16 Advanced Excel for Productivity
[PAGE UP] / [PAGE DOWN] lets you scroll up or down one “page” worth of rows… or about a screen
height. Useful for navigation when [CTRL] + [UP] or [CTRL] + [DOWN] don’t get you where you want to go,
such as the middle of a long contiguous range of data.
[CTRL] + [HOME] goes to the first cell, A1.
[CTRL] + [END] goes to the bottom-right-most cell you’ve ever edited. In other words, this is the right-
most column and lowest row that at some point you have made changes to. If all of your sheet’s contents
are bounded within B3 through G10, [CTRL] + [END] goes to G10. If you enter a value in N5, [CTRL] +
[END] now goes to N10. If you now erase the contents of N5, [CTRL] + [END] still goes to N10. Excel
doesn’t forget that you used column N at one point.
[CTRL] + [END] is useful for two reasons: you might find that there is additional data, or perhaps hidden or
invisible information, in rows or columns that are out of view. [CTRL] + [END] lets you know there’s
something there, or at least, there used to be something there. Secondly, there’s a neat trick for reducing the
file size of your spreadsheets. We’ll go over this in Chapter 9, but I’ll add a preview in this footnote.
4
Regular [HOME] and [END] (without [CTRL]) are much less useful. Home goes to the left-most cell in the
row, so column A of the same row. You could do the same with [CTRL] + [LEFT], so don’t worry about this
shortcut. [END] is even less useful. It turns on “End Mode”, which is the same as if you held down [CTRL]
while pressing the arrow keys. You’re better off just using [CTRL], but if you’re curious about End Mode then
go ahead and try it.
[CTRL] + [F] brings up the Find dialog box. Sometimes you know what text or formula you’re looking for,
but you’re not sure where on the sheet it is. Within the find box, start typing for what you need. Press
[ENTER] to find. I consider this part of “keyboard navigation” because you can use it to get to specific parts
of your sheet very quickly!
A quick note on Find: by default, Find searches within formulas and not the values of the cell. If you have a
cell whose output value is 5, but the formula is =2+3, searching for “5” will not go to this cell. You can change
to searching for values by clicking Options in the Find box, and choosing Look In: Values. Sorry, this part
does use the mouse. Read Chapter 9 for more Find/Replace Tips.
Entering and Editing Formulas
Besides navigating with the keyboard, you’ll also spend a lot of time entering and editing formulas. Let’s see
how that works without the mouse.
When entering formulas into a new cell, just start typing the formula with the [=] key. Learn the functions you
need (in Chapter 4), and enter them by typing the formula directly. For example, you can sum a range just
by typing =SUM(A1:A5).
This brings us to entering cell references in formulas. You can still use the keyboard, in two possible ways:
1. Directly type in the cell reference you want, with cell letters and numbers (“A1:A5” above).
2. Use the keyboard to select cells or ranges while you’re in the middle of entering a formula.
4
Sometimes you might notice you have a large file (at least 5 MB), but when you open it up there’s only visible
information in a small part of it. This should take a lot less than 5 MB – and you’re right, except sometimes Excel
thinks you’re using a huge area of the sheet and takes up file space. Use [CTRL] + [END] to see how much of the
sheet Excel thinks you’re using. To reduce the size of your file: use [CTRL] + [END] to find the last cell. Delete
any unnecessary rows and columns working up and left from the last cell. Then you must save and close the file
immediately. When you re-open it, the area within [CTRL] + [END] will be smaller, and your file size will be smaller
too.