Lesson 10: All the Tricks

Shortcuts, Flash Fill, quick tricks, and how to fix common errors

Listen to this lesson (6.4 MB, about 7 minutes)

Download the audio · Short pieces: 1 2 3 4 5

The errors you will meet most. The table below shows how to fix each one.

The shortcut keys worth learning

Hold the first key, then tap the second. On a Mac, use Cmd where it says Ctrl, unless the table says something else.

Keys	What it does	Mac
`Ctrl+C`	Copy	`Cmd+C`
`Ctrl+X`	Cut	`Cmd+X`
`Ctrl+V`	Paste	`Cmd+V`
`Ctrl+Z`	Undo	`Cmd+Z`
`Ctrl+Y`	Redo	`Cmd+Y`
`Ctrl+S`	Save	`Cmd+S`
`Ctrl+P`	Print	`Cmd+P`
`Ctrl+B`	Bold	`Cmd+B`
`Ctrl+F`	Find	`Cmd+F`
`Ctrl+H`	Find and Replace	`Ctrl+H`
`Ctrl+A`	Select everything	`Cmd+A`
`Ctrl+Arrow key`	Jump to the end of the list in that direction	`Cmd+Arrow`
`Ctrl+Shift+Arrow key`	Select to the end of the list	`Cmd+Shift+Arrow`
`Ctrl+Home`	Jump back to cell A1	`Fn+Ctrl+Left`-Arrow
`Ctrl+Shift+L`	Filter on or off	`Cmd+Shift+F`
`Ctrl+T`	Make a table	`Cmd+T`
`Ctrl+1`	Format Cells window	`Cmd+1`
`Ctrl+;`	Type today's date	`Ctrl+;`
`Ctrl+Shift+;`	Type the time now	`Cmd+;`
`Alt+=`	AutoSum	`Cmd+Shift+T`
`F2`	Edit the cell	`Ctrl+U`
`F4`	Add $ signs (lock an address)	`Cmd+T`
`Ctrl+E`	Flash Fill	Data, then Flash Fill
`Alt+Enter`	New line inside a cell	`Option+Return`
`Ctrl+D`	Copy the cell above down	`Cmd+D`
`Ctrl+Space`	Select the whole column	`Ctrl+Space`
`Shift+Space`	Select the whole row	`Shift+Space`
`Ctrl+Page Down` / Page Up	Go to the next or previous sheet	`Option+Right` / Left Arrow

Flash Fill: Excel copies your pattern

Flash Fill is like magic. You show Excel one or two examples, and it does the rest.

Say column A has full names like Sara Klein, and you want only the first names.

In B2, type the first name yourself: Sara.
Click B3 and press Ctrl+E.

Excel fills in all the other first names. It works for last names, phone numbers, capital letters, and joining two columns too.

Tip: If Flash Fill guesses wrong, type one more example in the next row and press Ctrl+E again.

Tip: Flash Fill is also on the Data tab, in the Data Tools group.

Quick tricks

Double-click the fill handle to copy a formula all the way down your list, without dragging.
Select numbers and look at the bottom bar for Sum, Average and Count, with no formula.
Double-click the line between two column letters to fit the width.
Hold Ctrl and click to select cells that are not next to each other.
Right-click a sheet tab, then Move or Copy, check Create a copy, to copy a whole sheet.
Paste Values: right-click, then the clipboard with 123. It pastes answers, not formulas.
Paste Transpose: copy a row, right-click, Paste Special, check Transpose. The row becomes a column.
Press Ctrl+` (the key above Tab) to see all formulas instead of answers. Press again to go back.
Drop-down list: select cells, click Data, then Data Validation. Under Allow pick List. In Source type Paid,Owes,Waiting and click OK. Now each cell has a little menu.
Click a cell, then Review, New Note, to add a sticky note to it.

Common errors and how to fix them

You see	What it means	How to fix it
#####	The column is too narrow to show the number or date	Double-click the line between the column letters to make it wider
#DIV/0!	You divided by zero or by an empty cell	Put a number in that cell, or use `=IFERROR(A2/B2,0)`
#N/A	XLOOKUP or VLOOKUP could not find it	Check the spelling and extra spaces (use TRIM). Add "Not found" at the end of XLOOKUP
#VALUE!	You did math with words, like `="ten"+5`	Make sure the cells hold numbers, not words or spaces
#NAME?	Excel does not know that word	Check the spelling of the function, and put words in quote marks
#REF!	The formula points to a cell that was deleted	Press `Ctrl+Z` to undo the delete, or write the formula again

Tip: IFERROR hides any error and shows something nicer: =IFERROR(your formula,"") shows an empty cell instead.

Tip: Click a cell with an error. A small yellow diamond with an exclamation mark appears. Click it to see help for that error.

Try it

Go to the sheet 10 Tricks. In column B, type the first name from A2, then press Ctrl+E for the rest. Do the same in C for last names. Then fix the four red error cells in rows 14 to 17 using the error table. Last, add a drop-down list in F2:F10 with Paid,Owes,Waiting. Practice five shortcuts from the table today: Ctrl+Z, Ctrl+S, Alt+=, Ctrl+T and Ctrl+;.

Download the practice workbook

Quiz

1. What does Ctrl+E do?

Show the answer

Flash Fill. It copies the pattern you started.

2. What does ##### in a cell mean?

Show the answer

The column is too narrow. Make it wider.

3. What does #DIV/0! mean?

Show the answer

You divided by zero or by an empty cell.

4. How do you see all the formulas at once?

Show the answer

Press Ctrl+` (the key above Tab).

5. What shortcut types today's date?

Show the answer

Ctrl and ; (semicolon) together.

Excel Made Easy. Free course. Written for easy reading.