Professor Excel
Count Number of Unique Records in Excel

Count Number of Unique Records in Excel: 5 Methods (+Download)

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 of unique records in a list, regarding two major differences.

  1. You simply want to know the number of unique records. There is no other condition to be considered.
  2. You want to know the number of different entries under one or more conditions.

[…]

FREQUENCY function in Excel

FREQUENCY Formula in Excel: Everything You Should Know (+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.

[…]

Return Blank Cells Instead of Zeroes in Excel Formulas

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 options for dealing with empty return values.

[…]

Convert Table to Column in Excel

Convert Table to One Column in Excel: 4 Easy Methods to Copy All Columns underneath Each Other

Copy table to one column in Excel

Copy table to one column

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 such transformation for inserting PivotTables or to create database formats. This article provides 4 simple methods to transform a 2-dimensional table into one column in Excel.

[…]

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 should know about the formula in Excel.

[…]

Case-Sensitive Lookups in Excel

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 proceed? In this article, you learn how to make VLOOKUP, HLOOKUP, INDEX/MATCH and SUMIFS case-sensitive.

[…]

VLOOKUP to the Left

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. It’s often worth considering alternative formulas though. Here is everything you should know.

[…]

Multi-Condition Lookups in Excel

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.

[…]

choose, formula, excel

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.

[…]

Combine Text in Excel

Combine Text in Excel: 4 Best Ways (+Download)

Excel offers three distinct formulas as well as a fourth way to combine multiple text cells into one cell. There are countless examples in which you might need this: Combine given– and family names or preparing primary keys for multi-conditional lookups. For example, in a VLOOKUP or INDEX/MATCH formula combination. In this article you learn 4 methods and in the end, you learn how to deal with a large range of cells.

[…]