# Excel Cheat Sheet: Formulas, Dynamic Arrays and Shortcuts

Source: https://srvscripts.com/guides/excel-cheat-sheet/
Updated: 2026-10-09
Publisher: srvScripts (https://srvscripts.com/)

**Short answer:** In current Excel, use `XLOOKUP` instead of VLOOKUP, and dynamic array functions such as `FILTER`, `SORT` and `UNIQUE`, which spill their results into neighbouring cells. `LET` names parts of a long formula, and `LAMBDA` turns a formula into a reusable function. Most of these need Microsoft 365 or Excel 2021 or later. The tables below cover the syntax, PivotTable basics and the main keyboard shortcuts for Windows and Mac.

Excel was not available for testing, so the formulas and shortcuts in this Excel cheat sheet were checked against Microsoft’s documentation on 9 October 2026 rather than tested. Version notes refer to Excel for Microsoft 365, Excel 2021 and Excel 2024.

## Formula basics and references

| Syntax | What it does |
| --- | --- |
| =A2*B2 | Every formula starts with =. Operators: + - * / ^ and & to join text |
| $A$2, A$2, $A2 | Absolute, mixed row, mixed column. Press F4 on Windows (Cmd+T or F4 on Mac) while editing a reference to cycle through them |
| =SUM(Sales[Amount]) | Structured reference to a column of an Excel table called Sales. It grows when the table grows |
| =IF(B2>=100, "Big", "Small") | Two-way condition |
| =IFS(B2>=1000, "A", B2>=100, "B", TRUE, "C") | Several conditions; TRUE acts as the default (Excel 2019 and later) |
| =IFERROR(A2/B2, 0) | Replace any error with a value |
| =SUMIFS(C:C, A:A, "East", B:B, ">100") | Sum with several criteria. COUNTIFS and AVERAGEIFS work the same way |
| =TEXT(A2, "yyyy-mm-dd") | Format a number or date as text |

## Lookups with XLOOKUP and XMATCH

XLOOKUP and XMATCH are in Excel for Microsoft 365, Excel 2021 and Excel 2024, but not in Excel 2016 or 2019. The syntax is `=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])`.

| Formula | What it does |
| --- | --- |
| =XLOOKUP(E2, A2:A100, C2:C100) | Exact match by default; can look left, unlike VLOOKUP |
| =XLOOKUP(E2, A2:A100, C2:C100, "Not found") | Custom result instead of #N/A |
| =XLOOKUP(E2, A2:A100, B2:D100) | Return several columns at once; the result spills to the right |
| =XLOOKUP(E2, A2:A100, C2:C100, , , -1) | Search from the last row up, so you get the latest match |
| =XLOOKUP(E2, A2:A100, C2:C100, , -1) | Exact match or the next smaller item (tiers and price bands) |
| =XLOOKUP("srv*", A2:A100, C2:C100, , 2) | Wildcard match: *, ?, and ~ to escape them |
| =XMATCH(E2, A2:A100) | Position of a value; same match and search modes as XLOOKUP |
| =INDEX(C2:C100, MATCH(E2, A2:A100, 0)) | The older equivalent, for workbooks that must open in Excel 2019 |

Match modes: `0` exact (default), `-1` exact or next smaller, `1` exact or next larger, `2` wildcard. Search modes: `1` first to last (default), `-1` last to first, `2` and `-2` binary search on sorted data.

## Dynamic arrays and spilling

A dynamic array formula is entered in one cell and its results spill into the cells below and to the right. These functions are in Microsoft 365, Excel 2021 and Excel 2024.

| Formula | What it does |
| --- | --- |
| =FILTER(A2:D100, C2:C100="East", "None") | Rows that match; the third argument avoids a #CALC! error when nothing matches |
| =FILTER(A2:D100, (C2:C100="East")*(D2:D100>100)) | AND: multiply conditions |
| =FILTER(A2:D100, (C2:C100="East")+(C2:C100="West")) | OR: add conditions |
| =SORT(A2:D100, 4, -1) | Sort by the 4th column, descending |
| =SORTBY(A2:A100, C2:C100, -1) | Sort one range by another |
| =UNIQUE(A2:A100) | Distinct values. =UNIQUE(A2:A100, , TRUE) returns values that occur exactly once |
| =ROWS(UNIQUE(A2:A100)) | Count distinct values |
| =SEQUENCE(12) | Numbers 1 to 12 down a column. =SEQUENCE(rows, columns, start, step) |
| =SUM(F2#) | The # after the first cell refers to the whole spill range, whatever its size |

If cells in the way are not empty, the formula shows `#SPILL!` until you clear them. Spilled formulas cannot live inside an Excel table, so put them in the grid next to it. In older files you may see `@` before a function name: that is the implicit intersection operator, which returns a single value instead of spilling.

## Text and reshaping functions

| Formula | What it does | Available in |
| --- | --- | --- |
| =TEXTJOIN(", ", TRUE, A2:A20) | Join a range with a delimiter, skipping blanks | 2019 and later |
| =TEXTSPLIT(A2, ",") | Split text into columns (add a row delimiter as the third argument) | 365, 2024 |
| =VSTACK(Jan!A2:C50, Feb!A2:C50) | Stack ranges vertically; HSTACK side by side | 365, 2024 |
| =TAKE(SORT(A2:B100, 2, -1), 10) | Top 10 rows by the second column | 365, 2024 |
| =CHOOSECOLS(A2:F100, 1, 3) | Keep only columns 1 and 3 | 365, 2024 |
| =GROUPBY(A2:A100, C2:C100, SUM) | Summary by group in one formula | Microsoft 365 |
| =REGEXEXTRACT(A2, "\d{1,3}(\.\d{1,3}){3}") | First IPv4-looking string in a cell (PCRE2 syntax) | Microsoft 365 |
| =REGEXTEST(A2, "^ERROR") | TRUE or FALSE for a pattern | Microsoft 365 |
| =REGEXREPLACE(A2, "[0-9]+", "#") | Replace every match | Microsoft 365 |

For server admins: paste a CSV export of failed logins into column A, enter `=REGEXEXTRACT(A2, "\d{1,3}(\.\d{1,3}){3}")` in B2 and fill it down to pull out the IPs, then `=GROUPBY(B2:B5000, B2:B5000, COUNTA)` counts attempts per IP. GROUPBY and the REGEX functions need Microsoft 365; in Excel 2021 or 2024, run `UNIQUE` and `COUNTIF` on an IP column instead.

## LET and LAMBDA

| Formula | What it does |
| --- | --- |
| =LET(total, SUM(B2:B100), n, COUNT(B2:B100), total / n) | Name intermediate results; each is calculated once. 365, 2021, 2024 |
| =LET(rows, FILTER(A2:D100, D2:D100>0), SORT(rows, 4, -1)) | Keep long dynamic array formulas readable |
| =LAMBDA(x, x * 1.2)(A2) | Test a LAMBDA in a cell by calling it straight away. 365, 2024 |
| =MAP(A2:A10, LAMBDA(v, IF(v>4, v*v, v))) | Apply a LAMBDA to each value |
| =BYROW(A2:C10, LAMBDA(r, SUM(r))) | One result per row |

To turn a LAMBDA into your own function, open Formulas > Name Manager (Formulas > Define Name on Mac), add a name such as `AddVAT` and set Refers to `=LAMBDA(net, net * 1.2)`. Then `=AddVAT(B2)` works anywhere in the workbook. A LAMBDA entered in a cell without a call returns `#CALC!`, and a wrong number of arguments returns `#VALUE!`.

## PivotTable basics

| Step | What to do |
| --- | --- |
| Prepare the data | One header row, no blank rows or columns. Convert it to a table first (Ctrl+T) so new rows are picked up on refresh |
| Create | Select a cell in the data, then Insert > PivotTable and choose a new or existing sheet. Recommended PivotTables are a Microsoft 365 feature |
| Build | Drag fields to Rows, Columns, Values and Filters. Text fields default to Rows and numeric fields to Values |
| Change the calculation | Values show as SUM, or COUNT if Excel sees text. Use Value Field Settings to switch to Average, Max or Count |
| Refresh | Right-click the PivotTable and choose Refresh, or PivotTable Analyze > Refresh All. Pivots do not update by themselves |

## Keyboard shortcuts: navigation and selection

| Action | Windows | Mac |
| --- | --- | --- |
| Jump to the edge of the data | Ctrl+Arrow | Cmd+Arrow |
| Extend selection to the edge | Ctrl+Shift+Arrow | Shift+Cmd+Arrow |
| Go to cell A1 | Ctrl+Home | Ctrl+Home |
| Last used cell | Ctrl+End | Ctrl+End |
| Select the whole column / row | Ctrl+Space / Shift+Space | Ctrl+Space / Shift+Space |
| Select the current region | Ctrl+Shift+* | Cmd+A |
| Next / previous sheet | Ctrl+Page Down / Ctrl+Page Up | Option+Right Arrow / Option+Left Arrow |
| Go To dialog | F5 or Ctrl+G | Ctrl+G or F5 |
| Find / Replace | Ctrl+F / Ctrl+H | Ctrl+F / Ctrl+H or Cmd+Shift+H |

## Keyboard shortcuts: editing and formatting

| Action | Windows | Mac |
| --- | --- | --- |
| Edit the active cell | F2 | Ctrl+U |
| New line inside a cell | Alt+Enter | Option+Return |
| Fill the selection with the entry | Ctrl+Enter | Cmd+Return or Ctrl+Return |
| Fill down | Ctrl+D | Ctrl+D or Cmd+D |
| AutoSum | Alt+= | Shift+Cmd+T |
| Insert today’s date / current time | Ctrl+; / Ctrl+Shift+: | Ctrl+; / Cmd+; |
| Show formulas instead of values | Ctrl+` | Ctrl+` |
| Paste Special | Ctrl+Alt+V | Cmd+Ctrl+V |
| Format Cells dialog | Ctrl+1 | Cmd+1 |
| Create a table | Ctrl+T or Ctrl+L | Cmd+T or Ctrl+T |
| Turn filters on or off | Ctrl+Shift+L | Cmd+Shift+F or Ctrl+Shift+L |
| Flash Fill | Ctrl+E | — |
| Calculate all open workbooks | F9 | Cmd+= or F9 |

On a Mac, the function keys may control brightness or volume. Hold Fn (for example Fn+F4), or change the keyboard setting in System Settings.

**Official documentation (checked 9 October 2026):** [XLOOKUP function](https://support.microsoft.com/en-us/excel/functions/xlookup-function) · [FILTER function](https://support.microsoft.com/en-us/excel/functions/filter-function) · [Dynamic array formulas and spilled array behavior](https://support.microsoft.com/en-us/excel/dynamic-array-formulas-and-spilled-array-behavior) · [LET function](https://support.microsoft.com/en-us/excel/functions/let-function) · [LAMBDA function](https://support.microsoft.com/en-us/excel/functions/lambda-function) · [Create a PivotTable to analyze worksheet data](https://support.microsoft.com/en-us/excel/get-started/create-a-pivottable-to-analyze-worksheet-data) · [Keyboard shortcuts in Excel](https://support.microsoft.com/en-us/accessibility/excel/keyboard-shortcuts-in-excel)

## Excel Cheat Sheet at a glance

In short: Modern Excel formulas, dynamic arrays, PivotTable basics and the keyboard shortcuts that matter, with Windows and Mac keys side by side.

Excel Cheat Sheet: what this guide covers.

**Related:** [SQL Cheat Sheet: MySQL, MariaDB and PostgreSQL Syntax](https://srvscripts.com/guides/sql-cheat-sheet/) · [R Cheat Sheet: Vectors, Data Frames, Strings and Files](https://srvscripts.com/guides/r-cheat-sheet/) · [JSON to CSV Converter (and CSV to JSON)](https://srvscripts.com/tools/json-to-csv/).