Say, you have an Excel table and want to copy all column underneath each other so that you only have one column. For example, you have a table 2 rows by 4 columns like in the screenshot on the right-hand side. You want to copy and paste this table to one column. You often need… Continue reading Convert Table to One Column in Excel: 4 Easy Methods to Copy All Columns underneath Each Other
Category: Formulas and Functions
This category is basically about everything you type in an Excel cell, no matter if it is a function or a formula. Examples are XLOOKUP, lookup functions, etc.
SUMPRODUCT in Excel: Everything You Should Know (+Download)
The SUMPRODUCT formula in Excel is quite powerful. The disadvantage: SUMPRODUCT is often not self-explanatory. Before Excel version 2007 it was used as the SUMIFS formula. Fortunately, with Excel 2007 the SUMIFS formula replaced SUMPRODUCT in many cases. But there are still some cases, in which you have to use SUMPRODUCT. Here is everything you… Continue reading SUMPRODUCT in Excel: Everything You Should Know (+Download)
Case-Sensitive Lookups in Excel: 4 Methods (+XLSX-Download)
By definition, the VLOOKUP formula is not case-sensitive. Case-sensitive means, that it matters if you use capital letters or small letters. For instance, a VLOOKUP search for “AAA” will return the same value as for “aaa” or “Aaa”. But in some cases, you want to differentiate between capital and small letters. So how do you… Continue reading Case-Sensitive Lookups in Excel: 4 Methods (+XLSX-Download)
VLOOKUP to the Left in Excel. Yes, It’s Possible!
The VLOOKUP formula in its base version only works from left to right. The search column must be located on the left-hand side of the return column. What if your data doesn’t have such structure? There is a way for using the VLOOKUP to the left but it requires an array form of the formula.… Continue reading VLOOKUP to the Left in Excel. Yes, It’s Possible!
Multi-Condition VLOOKUP and INDEX/MATCH (+Excel-Download)
There are many cases in which you want to conduct a lookup with several search criteria. As of now only the SUMIFS formula allows a multi-condition lookup. Unfortunately, SUMIFS only works for numeric values (including dates) as the return value. If you want to return text, there is no direct method. The good news: Both… Continue reading Multi-Condition VLOOKUP and INDEX/MATCH (+Excel-Download)
CHOOSE Formula in Excel: Everything You Should Know
The CHOOSE formula in Excel is one of those, which is quite unknown. It can be very helpful though: For example it can easily convert the weekday-number into the weekday name. Often, it is used within other formulas. In this article you learn everything you need to know about the formula.
FIND & SEARCH in Excel: How and When to Use These Formulas
The two formulas FIND and SEARCH in Excel are very similar. They search through a cell or some text for a keyword or character. Once found, they return the number of characters, at which the keyword starts. Let’s learn how to use them and explore the differences of the two formulas.
How to Replace Text with the SUBSTITUTE Formula in Excel
Sometimes, you need to change text in a systematic way: If you want to replace some text with new text, there are two options. If you only want to do it once, using the Find-and-Replace dialogue is probably the fastest choice. If you want to do it repeatedly or don’t want to mess with your input data, you should try the SUBSTITUTE formula.
LOOKUP Function in Excel: How to use & why to avoid it!
Besides the well-known VLOOKUP function, there is a similar function in Excel: LOOKUP. The function is very unknown and hardly used. But there are good reasons why you should not use the function. Let’s start with the basics about the function and then talk about why you should avoid it.
Functions Dictionary: Easily Translate Excel Formulas to Your Language
Microsoft Excel supports many languages. In total, you can use Excel in 107 different languages. Many of these languages also use local formula names. For example, VLOOKUP is called SVERWEIS in German, BUSCARV in Spanish or DÜŞEYARA in Turkish. Fortunately, you don’t need to remember different formula names because Excel automatically translates the formulas to… Continue reading Functions Dictionary: Easily Translate Excel Formulas to Your Language