Excel Formula and Shortcut Finder

XLOOKUP finds a value in one range and returns the matching value from another: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]). For example, =XLOOKUP(E2, A2:A100, C2:C100, "Not found") looks up E2 in column A and returns the value from column C. It is available in Excel 365, Excel 2021 and later, and Google Sheets.

Use =VLOOKUP(lookup_value, table_array, col_index_num, FALSE), where FALSE asks for an exact match. For example, =VLOOKUP(E2, A2:C100, 3, FALSE) finds E2 in the first column of A2:C100 and returns the value from its third column. VLOOKUP only looks to the right; XLOOKUP can look both ways.

Use =(New-Old)/Old and format the cell as a percentage. For example, =(B2-A2)/A2 returns 25% when a value rises from 80 in A2 to 100 in B2; a negative result means a decrease.

Type =SUM(A2:A100) to add every number in A2 to A100, or select the cell under the column and press Alt + = on Windows (Cmd + Shift + T on a Mac) for AutoSum. SUM ignores text and empty cells.

Use the asterisk: =A2*B2 multiplies two cells, and =A2*10 multiplies by a number. To multiply a whole range together use =PRODUCT(A2:A10), and to multiply two columns and add the results use =SUMPRODUCT(A2:A10, B2:B10).

Press Ctrl + 5 on Windows, or Cmd + Shift + X on a Mac. Press it again to remove the strikethrough.

Select the row by pressing Shift + Space, then press Ctrl + Shift + + (plus) on Windows or Cmd + Shift + + on a Mac. A new row is inserted above the selected one.

Ctrl + C, Ctrl + V and Ctrl + Z to copy, paste and undo; Ctrl + Arrow to jump to the edge of your data; Ctrl + Shift + L to switch filters on; F2 to edit a cell; F4 to repeat the last action or lock a reference; and Ctrl + ; to insert today’s date. This page lists all 343 for Windows and Mac.

Use =PMT(rate, number_of_payments, loan_amount) with the rate per period. For a 20,000 loan at 5% a year over 5 years paid monthly, =PMT(5%/12, 60, -20000) returns about 377.42 a month; the loan is entered as a negative so the payment shows as positive.

Use =TRIM(A2), which removes spaces at the start and end and turns repeated spaces between words into one. Spaces copied from web pages are often non-breaking; remove those first with =SUBSTITUTE(A2, CHAR(160), " ").

Usually calculation is set to Manual (press F9, or set Formulas > Calculation Options to Automatic), the cell is formatted as Text, Show Formulas is switched on (Ctrl + `), or the formula starts with a space or apostrophe.

Most Windows shortcuts use Cmd instead of Ctrl on a Mac, but not all: strikethrough is Cmd + Shift + X, a new line in a cell is Option + Return, and locking a reference is Cmd + T. Switch this page to Mac to see every shortcut in its Mac form.

It makes part of a reference absolute, so it does not change when the formula is copied: $A$1 fixes the column and row, A$1 only the row and $A1 only the column. Press F4 on Windows, or Cmd + T on a Mac, to cycle through them.

#SPILL! means a formula that returns several results, in Excel 365 or 2021, cannot fill the cells next to it because one of them is not empty. Clear those cells and the results appear.

Excel 365 has over 500 worksheet functions. This reference lists 496 of them with syntax, worked examples and where each works, plus 343 shortcuts, 114 tricks and 55 VBA snippets, free and without signup.