Looking for more? Try chatting with the Exceljet Chatbot.

Video

Dynamic arrays are native

In this video we'll look at how dynamic array behavior is native and deeply integrated in Excel. Even older functions can use this new feature...Read more
Formula

Formula with locked absolute reference

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

Keyboard shortcut

Cut selected cells

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

Keyboard shortcut

Paste content from clipboard

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

Keyboard shortcut

Display the Paste Special dialog box

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

Keyboard shortcut

Select table column

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

Keyboard shortcut

Insert line break in cell

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

Keyboard shortcut

Fill down from cell above

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

Keyboard shortcut

Apply date format

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

Keyboard shortcut

Toggle absolute and relative references

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

Keyboard shortcut

Drag and copy

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

Formula

Tax rate calculation with fixed base

The goal is to calculate a tax amount with both fixed and variable components according to the following logic:

  1. If the amount is less than $1000, only the base tax applies.
  2. If the amount is $1000 or greater, the result is the base tax + 15% * the amount over $
    3. ...Read more
Formula

Tax rate calculation with two rates

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

Function

EXACT Function

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

Formula

Score quiz answers with key

This formula uses the named range key (C4:G4) for convenience only. Without the named range, you'll want to use an absolute reference so the formula can be copied.

In cell I7, we have this formula:

=SUM(--(C7:G7=key))
...Read more
Formula

Lookup value between two numbers

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

Function

HYPERLINK Function

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

Function

INDIRECT Function

The Excel INDIRECT function returns a valid cell reference from a given text string. INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.Read more

Formula

XLOOKUP with multiple criteria

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

Formula

Sum across multiple worksheets with criteria

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

Formula

Nested IF function example

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

Formula

XLOOKUP with Boolean OR logic

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

Formula

Count missing values

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

Function

INDEX Function

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

Formula

FILTER with partial match

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

Pages