Keyboard

No mouse needed.

Anywhere

←↑↓→
Move the selection around the page
Enter
Open whatever is selected
1…9
Go to a section along the top (or, in a workbook, to one of its sheets)
CtrlQ
Leave an open workbook for its list. Works while in the sheet; asks first if you have typed anything
Alt→
Next section (Alt ← for previous), or next sheet in a workbook. Also Alt PgDn and Alt PgUp
CtrlPgDn
Next section or sheet, as in Excel. Needs focus mode
CtrlHome
Top of the page (Ctrl End for the bottom)
S
Start, or continue after your last solved challenge
X
A random function drill (on the Drills page)
G
Jump into the spreadsheet
F
Focus mode: full screen, browser shortcuts handed to the page
?F1
Show or hide this list

On a challenge

CtrlEnter
Check your answer; once it is right, go to the next challenge. In an exam, the next question. Works while in the sheet
Ctrl/
Show a hint. Works while in the sheet
C
Check your answer
H
Show a hint
AltR
Reset the sheet. Works while in the sheet
AltP
Previous question in an exam (Alt N for the next). Works while in the sheet
B
Begin a practice exam

Moving and selecting in a spreadsheet

←↑↓→
Move one cell
Ctrl↓
Jump to the edge of the data
Home
Start of the row (Ctrl Home for A1, Ctrl End for the last cell)
CtrlG
Go to a cell or range: type it in the name box. Also F5
AltPgDn
Next sheet of a workbook with several (Alt PgUp for previous). Its sheets take over the tabs along the bottom. Mid-formula, select cells there to use them
Shift↓
Select a range (add Ctrl to reach the edge of the data)
ShiftSpace
Select the row (Ctrl Space for the column)
CtrlA
Select everything in use
EnterTab
Move down or right. With a range selected, travel through it
Esc
Cancel a copy, or leave the sheet for the page

Writing a formula

=
Start a formula (or just type)
Tab
Complete the suggested function name
←↑↓→
Pick a cell; hold Shift for a range
F4
Lock the reference: A1, $A$1, A$1, $A1
F2
Edit a cell, or switch the arrows between picking cells and moving the text cursor
Alt=
AutoSum the numbers above
Ctrl'
Copy the formula from the cell above
Enter
Confirm. Esc cancels
CtrlEnter
With a range selected, fill every cell with what you typed

Editing a spreadsheet

CtrlC
Copy (Ctrl X to cut)
CtrlV
Paste. Add Shift to paste values only
CtrlD
Fill down: from the cell above, or the top row through a range
CtrlR
Fill right
Delete
Clear the selected cells
CtrlZ
Undo (Ctrl Y to redo)
Ctrl[
Go to the cells a formula uses (Ctrl ] for cells that use this one)
Ctrl`
Show formulas instead of results

Formatting

CtrlB
Bold (Ctrl I italic, Ctrl U underline, Ctrl 5 strikethrough)
CtrlShift1
Number with two decimals and thousands commas
CtrlShift4
Currency
CtrlShift5
Percentage
CtrlShift3
Date (Ctrl Shift 2 for time, Ctrl Shift 6 for scientific)
CtrlShift~
General: remove the number format
CtrlShift7
Border round the selection (Ctrl Shift − removes borders)

On a Mac, use ⌘ wherever Ctrl is shown and Option wherever Alt is shown (Ctrl Space is taken by macOS). For number formats on a Mac use Control+Shift, as Excel does: ⌘+Shift+3, 4 and 5 are macOS screenshots. Ctrl+PgUp/PgDn, Ctrl+W, Ctrl+T and Ctrl+N belong to the browser; focus mode hands them to the page in Chrome and Edge.

Blog

VLOOKUP vs XLOOKUP: which should you use?

What each one does, where VLOOKUP trips people up, and when the old one is still the right call.

The short answer

Use XLOOKUP if everyone who opens the file has Excel 365 or Excel 2021 or later. It is easier to read, safer by default and does everything VLOOKUP does. Stick with VLOOKUP when the file goes to people on older versions of Excel, because XLOOKUP shows up there as #NAME?.

How VLOOKUP works

VLOOKUP looks for a value in the first column of a table and returns something from the same row, a set number of columns to the right.

=VLOOKUP(E2, A2:C50, 3, FALSE)

This reads: find the code in E2 in the first column of A2:C50, then return the value from the third column of that table. The final FALSE asks for an exact match.

That last argument is where most VLOOKUP mistakes start. Leave it out and Excel assumes TRUE, an approximate match. On a list that is not sorted, an approximate match can quietly return the wrong row with no error at all. Always type FALSE unless you really want the nearest value below.

Two more catches:

  • It only looks right. The value you search for must be in the first column. If the code sits to the right of the name you want back, VLOOKUP cannot do it.
  • The column number is typed in. Insert a column inside the table and 3 now points at the wrong one. The formula does not update itself.

What XLOOKUP fixes

XLOOKUP takes the column to search and the column to return as two separate ranges.

=XLOOKUP(E2, A2:A50, C2:C50)

Find E2 in A2:A50, return the matching entry from C2:C50. Because the two ranges are separate:

  • Exact match is the default. No trailing FALSE to forget.
  • It can look left. The return column can be anywhere, before or after the search column.
  • Inserting columns is safe. The ranges move with the sheet, so there is no column number to go stale.
  • You choose what "not found" shows. A fourth argument replaces #N/A: =XLOOKUP(E2, A2:A50, C2:C50, "Not found").
  • It can search from the bottom. Set the sixth argument to -1 to get the last match instead of the first, handy for the latest price or most recent order.

When to stick with VLOOKUP

  • Older Excel. Anyone on Excel 2019 or earlier will see #NAME? instead of your answer.
  • A workbook full of them. If a model already uses VLOOKUP everywhere and works, rewriting it buys little. Use XLOOKUP for new formulas.
  • Interviews and tests. Many Excel tests still ask for VLOOKUP by name, so it is worth knowing both.

If you need to look left on an older version, INDEX with MATCH does the job and works in every version of Excel.

Common questions

Does Google Sheets have XLOOKUP?

Yes. Google Sheets has had XLOOKUP since 2022, with the same arguments as Excel, so the formulas in this article work there too.

Can XLOOKUP return more than one column?

Yes. Give it a return range several columns wide, such as C2:E50, and the results spill across the cells to the right. VLOOKUP needs one formula per column.

How do I do an approximate match with XLOOKUP?

Set the fifth argument, the match mode, to -1 for an exact match or the next smaller value. That is what VLOOKUP's TRUE does for things like tax bands, except XLOOKUP does not need the list sorted.

Try it yourself

Practise in the course

Not affiliated with Microsoft. Excel is a trademark of Microsoft Corporation.