Excel has – in it’s newest version – a quite useful new formula type. It’s called “linked data” and offers the functionality to automatically insert data from the internet to your table. This can be done with the FIELDVALUE formula and works in a first test quite well. Unfortunately, the available data types and options… Continue reading FIELDVALUE Formula in Excel: Insert Data of Companies and Countries (+Download)
Array formulas are an advanced topic in Excel. Usually Excel users discover them when reaching the limits of – let’s call them – normal formulas, e.g. SUM, VLOOKUP, COUNT and so on. This article provides an introduction of array formulas in Excel.
A common task in Excel is to find out the number of different entries in a list. For example, you have a list of names and want to know, how many different people are listed as some people might be multiple times on the list. This article introduces 5 different methods of counting the number… Continue reading Count Number of Unique Records in Excel: 5 Methods (+Download)
FREQUENCY can classify data into bins. The formula in Excel is an array formula and just returns an array of values. In practice, it appears rather seldom. It is usually used within other formulas and not as a stand-alone formula. That said, let’s explore the purpose and usage of FREQUENCY.
If the return cell in an Excel formula is empty, Excel by default returns 0 instead. For example cell A1 is blank and linked to by another cell. But what if you want to show the exact return value – for empty cells as well as 0 as return values? This article introduces three different… Continue reading Return Blank Cells Instead of Zeroes in Excel Formulas: Easy!
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
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)
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)
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!
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.