Looking for more? Try chatting with the Exceljet Chatbot.
The Excel LET function lets you define named variables in a formula. There are two primary reasons you might want to do this: (1) to improve performance by eliminating redundant calculations and (2) to make more complex formulas easier to read and write....Read more
If VLOOKUP finds more than one match, will you get the first match or the last match? It's a trick question. It depends :) This article explains this confusing topic in detail, with lots of examples.Read more
The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column numbers....Read more
In this example the goal is to check if values in column B are within a tolerance of .005. If a value is within tolerance, the formula should return "OK". If the value is out of tolerance, the formula should return "Fail". The expected value is listed in column C, and the allowed tolerance is...Read more
In this example, the goal is to list the next n upcoming birthdays from a larger set of 25 birthdays based on the current date. The set of birthdays are in an Excel Table named data in the range B5:C29. In the example shown, we are using 7 for...Read more
In this example, the goal is to extract unique values from three separate ranges at the same time: range1 (C5:C16), range2 (D5:D15), and range3 (F5:F13). At one time, this was a difficult problem, since UNIQUE is programmed to...Read more
The MODE function has no built-in way to apply criteria. Given a range, it will return the most frequently occurring number in that range.
To apply criteria, we use the IF function inside MODE to filter values in a range. In this example, the IF function filters values by group with an...Read more
To create a pivot table that shows the last 7 days of data (or, more generally, the last n days) you can add a helper column to the source data with a formula to flag records in the last 7 days, then use the helper column to filter the...Read more
The Excel YEAR function returns the year of a date as a 4-digit number. You can use the YEAR function to extract the year from a date. You can also combine YEAR with other functions to manipulate dates in specific ways, and even to count and sum by year.Read more
In this example, the goal is to strip (i.e., remove) non-numeric characters from a text string with a formula. Until 2024, this was a tricky problem in Excel, partly because Excel did not support regex (Regular Expressions), and partly because there...Read more
The Excel HLOOKUP function finds and retrieve a value from data in a horizontal table. The "H" in HLOOKUP stands for "horizontal", and lookup values must appear in the first row of the table, moving horizontally to the right. HLOOKUP supports approximate and exact matching, and...Read more
In this example, the goal is to count numbers that fall within specific ranges. The lower value comes from the "Start" column, and the upper value comes from the "End" column. For each range, we want to include both the lower value and the upper value. For convenience, the numbers being...Read more
In this example, the goal is to sum values in rows that are visible and ignore values in rows that are hidden. The range F7:F19 contains 13 values total, 4 of which are hidden by the filter applied to column C. This is a good job for the ...Read more
Above: Using the FILTER function to return multiple matches on "red".
In fall 2018, Microsoft announced a new feature in Excel called "dynamic array formulas". In a nutshell, this feature allows formulas to return multiple results to a range...Read more
The goal is to do something if a cell contains a given substring. For example, in the worksheet above, a formula returns "x" when a cell contains "abc". If you are familiar with Excel, you will probably think first of the IF function. However, one limitation of IF is that it does...Read more
In this example, the goal is to perform a case-sensitive lookup on the color in the "Color" column. In other words, a lookup value of "RED" must return a different result from a lookup value of "Red". By default, Excel is not case-sensitive and this applies to standard lookup...Read more
Copy puts everything on the clipboard: text, formulas, formatting, borders, fills, etc. Use Paste Special to selectively paste only parts of what was copied.
On the Mac, Ctrl + C also worksRead more
Moves current selection one cell to the right of the active selection.Read more
Moves current selection one above the active selection.Read more
The delete key on a Mac deletes to the left.Read more
This isn't really a shortcut, but it's important to understand that pressing enter or return will complete a cell entry and move the cursor down by one cell by default. Using other keys (like the tab and arrow keys) you have the option to move the cursor in other directions after...Read more
With a single cell selected, this shortcut will enter a value and leave the same cell selected.Read more
Apply the General number format. The General number format is the default format in Excel. It will display numbers as numbers, and text as text. Excel offers many types of number formatting.Read more
Use this keyboard shortcut to evaluate parts of a formula. This is not really a keyboard shortcut per se, but rather just a trick you can use to find out how Excel evaluates parts of a formula as it calculated. This is a great way to check a formula to find out how it works, or to debug a...Read more
The MEDIAN function has no built-in way to apply criteria. Given a range, it will return the MEDIAN (middle) number in that range.
To apply criteria, we use the IF function inside MEDIAN to "filter" values. In this example, the IF function filters by group like this:
...Read more
