Formula

This guide will explain how to find the 2nd match or the nth occurrence in an array using the XLOOKUP function. There is a common problem with all lookup functions in Excel: how to skip the exact match and get the 2nd, 3rd, or nth match. We will use the XLOOKUP function in the example … Read more

Learn how to lookup the first negative value in a range using the XLOOKUP function or INDEX and MATCH functions. Generic Formula Here is the generic formula to lookup the first negative value in a range: In the formula, “rng” is the range where we find the first negative value; enter the formula in cell … Read more

To lookup and get the first text value in a range, use the XLOOKUP function with a wildcard character match mode. From time to time, you want to find and extract the first text value from a range that contains numbers and text values. Use the XLOOKUP function and apply the wildcard character (*) as … Read more

This guide will explain how to count cells that contain numbers in a range using the Excel COUNT function. In the example, we want to count all cells in a range where the cells are numbers. Two common methods are using the COUNT function or applying the SUMPRODUCT function. If you want to learn all … Read more

This tutorial will explain how to highlight every other row in Excel. Then, to improve your spreadsheet’s readability, alternate shade rows! Working with large data tables and organizing ranges in Excel is sometimes not too easy. In this Excel guide, I will cover the following topics: At first, you’ll learn how to highlight every other … Read more

To count unique values in a list or range, use the UNIQUE function and configure the function’s third argument to TRUE. In Microsoft 365 for Excel, you can create powerful Excel Formulas. Today’s guide will show you how to count unique and distinct values in a range using COUNT and UNIQUE functions. Differences between unique … Read more

To count cells that contain values less than a given number in a range, use the generic formula based on the COUNTIF function.

This tutorial will explain how to sum cells that contain formulas in Excel using the SUMPRODUCT and ISFORMULA functions. Generic formula to sum results where the cell contains formulas only: How to SUM cells that contain formulas In the example, we have a sales table that contains orders and sales for a given location. Some … Read more

Learn how to convert boolean TRUE or FALSE to numbers (0 or 1) using the Excel N function or the double negative method. When working with complex Excel formulas, you must know how to convert TRUE and FALSE values to the numeric equivalents 1 and 0. If you are unfamiliar with boolean logic, read this … Read more

To count cells that contain more than n characters, use a formula based on three Excel functions: SUMPRODUCT, LEN, and N. The general formula to count cells over n characters: In the example, n=70 in cell F2, we find the cells which contain more than 70 characters. The result is 3 since three cells in … Read more

The binary search algorithm is designed for fast searching. To use this search mode, you need to have a sorted array. If you are working with Excel, you can use the binary search option to set the 6th (search mode) argument of the XLOOKUP function to 2 or -2. This guide will show you how … Read more

This tutorial shows how to convert text to numbers in Excel using text to column, Formulas, Paste Special, error checking, or VBA. What if you want to work with numbers, but the formula returns errors? First, you should check the given cell: is the value in a number format? Or is it a text? The … Read more