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

How to fix #N/A in Excel

Why a lookup returns #N/A, the usual causes, and how to find which one you have.

What #N/A means

#N/A stands for "not available". Excel shows it when a lookup (VLOOKUP, XLOOKUP, MATCH, XMATCH) could not find the value it was asked for.

Most of the time the formula is fine. The value really is missing, or it is there but does not look the same to Excel as the thing you searched for. Your job is to find out which.

The usual causes

1. The value is not in the list. A new product code, a typo, a customer added last week. This is the honest #N/A: the answer really is "not there".

2. Extra spaces. "A103 " with a space at the end is not the same as "A103". Data copied from other systems often brings spaces with it. Clean it with TRIM:

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

3. A number stored as text. The list holds the number 1001 and you search for the text "1001", or the other way round. They look identical in the cell but do not match. Text numbers usually sit on the left of the cell and may show a small green triangle. Convert the search value to match the list: VALUE(E2) turns text into a number, TEXT(E2, "0") turns a number into text.

4. The range moved when you copied the formula. Fill =VLOOKUP(E2, A2:C50, 3, FALSE) down one row and the table becomes A3:C51, so the first row drops out. Lock the table with dollar signs: $A$2:$C$50. Press F4 while the cursor is on the reference to add them.

5. An approximate match on an unsorted list. With VLOOKUP's last argument left out, Excel assumes the list is sorted. On an unsorted list it can stop early and give #N/A for a value that is there. Add FALSE at the end, or use XLOOKUP, which matches exactly by default.

Finding which one you have

Pick one row that fails and test it directly. If the lookup value is in E2 and you think the match is in A7:

  • =E2=A7 returns FALSE when the two are different in any way, even if they look the same.
  • =LEN(E2) and =LEN(A7) give different lengths when one of them has hidden spaces.
  • =ISNUMBER(E2) and =ISNUMBER(A7) disagree when one is a number and the other is text.

Once you know the cause, fix the data or the formula, not just the error.

Hiding it versus fixing it

It is tempting to wrap the lookup in IFERROR and move on:

=IFERROR(VLOOKUP(E2, $A$2:$C$50, 3, FALSE), "")

That hides every error, including the ones that mean your formula is broken. A blank cell then looks like a real answer and the mistake travels into your totals.

Hide #N/A only when "not found" is a genuine, expected result, and say so. With XLOOKUP, use its own argument:

=XLOOKUP(E2, $A$2:$A$50, $C$2:$C$50, "Not found")

This replaces only the not-found case. Any other error still shows, so you still hear about real problems.

Common questions

What is the difference between IFNA and IFERROR?

IFNA replaces only #N/A. IFERROR replaces every error, including #REF! and #VALUE!, which usually mean the formula itself is broken. When a lookup may not find a value, IFNA is the safer choice.

Why does my lookup return #N/A when I can see the value?

Usually one of three things: a hidden space at the start or end of one value, a number stored as text in one list and as a number in the other, or VLOOKUP doing an approximate match because its last argument is missing. The checks in "Finding which one you have" tell them apart.

Why does one #N/A break my total?

Any error inside a range passes through SUM, so the total shows #N/A too. Fix the lookups first. If some rows are expected to be missing, =AGGREGATE(9, 6, D2:D50) adds up the range and skips errors.

Try it yourself

Practise in the course

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