Looking for more? Try chatting with the Exceljet Chatbot.
In this example, the goal is to create an "locked" reference that won't change when columns or rows are added or deleted in a worksheet, or during a copy / paste / cut operation.
The INDIRECT function accepts text, and evaluates that text as a reference. As a result, the text is not...Read more
The Excel DROP function returns a subset of a given array by "dropping" rows and columns. The number of rows and columns to remove is provided by separate rows and columns arguments. Rows and columns can be dropped from the start or end of the given array....Read more
Cut 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 + X also worksRead more
Paste will paste everything on the clipboard, both content and formatting. Use Paste Special to selectively paste only parts of what was copied. On the Mac, Ctrl + V also works.
You can also use Enter in Windows, and fn + Return on a Mac to paste from the clipboard.Read more
Use this shortcut to display the Paste Special dialog box. Paste Special is the gateway into many powerful operations, including paste Values, which you probably use every day. Note that this shortcut only works when data has been copied to the clipboard.
On Windows, once you have the...Read more
This shortcut selects one or more table columns in a table, when the cursor is in an Excel table. Behavior changes as the shortcut is used more than once. First time: column data. Second time: column data + column header. Third time: entire worksheet column.Read more
When multiple cells are selected, this shortcut will leave only the active cell selected.Read more
Normally, when you press the Enter key, Excel moves the cursor to the next cell. To insert a line break (i.e. a new line) inside a cell, you need to use a shortcut. Here at the steps:
(1) Move the cursor where you want to break the line
...Read more
This shortcut copied data from the cells above to cells below, without having to copy and paste. To fill down into more than one cell at once, make a selection that includes multiple rows below. Cell references in formulas will update normally.
Source and target cells do not need to be...Read more
This shortcut applies the Date format "dd-mmm-yy" to the selection. You can also use the shortcut Control + 1 to open the Format cells dialog box, which allows you to choose other date formats and to apply a...Read more
While editing a formula, this shortcut toggles cell references from relative to absolute, to partially absolute, back to relative again:
A1 --> $A$1 --> A$1-- > $A1-- > A1
This is much faster and easier than typing the $ character manually.
To convert an existing...Read more
The default behavior for drag and drop is to cut data in cells, but you can also copy cells during drag and drop. To copy cells, use this shortcut before or after you start to drag the selection. Excel will display a small plus (+) symbol to indicate the copy operation.Read more
The goal is to calculate a tax amount with both fixed and variable components according to the following logic:
The goal is to calculate a tax of 6% on amounts up to 20,000 and a tax of 10% on amounts of 20,000 or greater. This problem illustrates how to use the IF function to return different calculations. At the core, this formula uses a single IF function. The...Read more
The Excel EXACT function compares two text strings, taking into account upper and lower case characters, and returns TRUE if they are the same, and FALSE if not. EXACT is case-sensitive.Read more
The Excel XLOOKUP function is a powerful tool designed to look up a value in one range and return a corresponding value in another range — it supports approximate and exact matching, wildcards, regular expressions (regex), reverse searches, and lookups in vertical or horizontal ranges....Read more
The LOOKUP function does an approximate match lookup in one range, and returns the corresponding value in another.
Although the table in this example includes both maximum and minimum values, we only need to use the minimum values. This is because when LOOKUP can't find a match, it will...Read more
In this example, the goal is to count codes in a case-sensitive way. The COUNTIF function and the COUNTIFS function are both good options for counting text values, but neither is case-sensitive, so they can't be...Read more
Note: in the current version of Excel, the FILTER function is a better way to solve this problem. The INDEX and MATCH formula explained here is meant for legacy versions of Excel that do not provide the FILTER...Read more
The Excel HYPERLINK function returns a hyperlink to a given destination. You can use HYPERLINK to create a clickable hyperlink with a formula. The HYPERLINK function can build links to workbook locations, pages on the internet, or files on network servers....Read more
The Excel CHOOSECOLS function returns specific columns from an array or range. The columns to return are provided as numbers in separate arguments. Each number corresponds to the numeric index of a column in the given array.Read more
For many years, INDEX and MATCH have been the go-to solution for difficult lookup problems in Excel. While more complicated to configure, the two-function combination of INDEX + MATCH is flexible and powerful. But now that...Read more
In this example, the goal is to look up a price using XLOOKUP with multiple criteria. To be more specific, we want to look up a price based on Item, Size, and Color. At a glance, this seems like a difficult problem because XLOOKUP only has one value for lookup_value and lookup_array...Read more
In this example, the goal is to sum hours per project across three different worksheets: Sheet1, Sheet2, and Sheet3. The data on each of the three sheets has the same structure as Sheet1, as seen below:
...Read more
The goal is to assign a grade to each score in column C according to the rules in the table in the range F4:G9. One way to do this in Excel is to use a series of nested IF functions. Generally, nested IFs formulas are used to test more than one condition and return a different result for...Read more
In this example, the goal is to use XLOOKUP to find the first "red" or "pink" record in the data as shown. All data is in an Excel Table named data in the range B5:E14. This means the formulas below use structured...Read more
In this example, the goal is to count the number of names in the range B5:B16 (Invited) that are missing from the range D5:D12 (Attended). This problem can be solved with the COUNTIF function or the MATCH function, as explained below. Both approaches work well. The advantage of the MATCH...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 extract a set of records that match a partial text string. To keep things simple, we are only matching one field in the data, the last name ("Last"). The core operation of this formula comes from the...Read more
