Covering a few of Excel keyboard shortcuts and a few other little tricks that helps work faster.
F9 – Partial formula evaluation
F9 is really helpful for debugging complex formulas, because it allows you to select smaller pieces of your formula and evaluating just that highlighted part. To try it out, just select the portion of the formula that you want to evaluate:
Be careful because any changes that are made in the formula bar will be committed to the cell if you press ENTER. It’s a good idea to press ESC to preserve the original formula.
CTRL + ` – Show formulas
If I want to get a quick understanding / review of all the formulas in a sheet, instead of selecting each cell individually and examining the formula bar, I can use Show Formulas. Here is what my workbook normally looks like:
And after pressing CTRL + ~ I see the formulas in the cells, not the values:
ALT + = for AutoSum
If your active cell is at the bottom of or to the right of a range of data, typing ALT + = will automatically put the SUM function into that cell, along with the range reference for the data above, or to the right (respectively).
Before ALT + =
After ALT + =
Incidentally, if Excel doesn’t know what range to put it, you’ll just get =SUM(), which is better than typing it all out, right?
CTRL + [ and CTRL + ] to select direct precedent & dependent cells
In a complex spreadsheet with formulas that build on and reference each other, you may want to know which cells are precedents of or dependent on a particular cell. For example, consider this spreadsheet (in show formulas mode CTRL + ` ):
Cells A1, G3, and G5 contain a fixed value of “1”, and C1 depends on A1, E1 on C1, G1 on E1, and i1 on G1, G3, and G5.
Selecting cell i1 then typing CTRL + [ shows me:
To select all cells that are precedents of i1 (several levels of formula reference), type CTRL + SHIFT + {
The converse works as well, for formula cells that are dependent on the active cell. Use CTRL + ] and CTRL + SHIFT + } for that functionality.
Shift + F3 – Insert Function
Use Shift + F3 to insert a function from the dialog box .
F4 – Repeat
Use F4 to repeat the last thing you did, a great shortcut especially if the action just took several clicks. For example:
· Inserting cells to the right
· Applying complex formatting to a cell (could also use the format painter)
· Inserting more rows or columns (after the first insert, see Part 1 of this series for an example)
F12 – Save As
The Save As dialog will come up when typing CTRL + S as long as the book you’re working on has never been saved. But if you’re working on a workbook that’s already been saved, and now want to save it with a new name it’s useful to be able to quickly bring up the Save As dialog. F12 or ALT + F, then A (if you like the file menu) will automatically bring up the Save As dialog.
ALT + F1 – Insert a Chart
Just highlight some data in the grid and type ALT + F1 to get a chart inserted onto the same sheet you’re working with. If you don’t like the default column chart that gets inserted, notice that on the Insert Chart dialog there is a button named “Set as Default Chart”, so all you need to do is pick the chart type you like and click that button:
If you wanted a chart sheet instead of a chart object on a worksheet, type F11 instead.
CTRL+H – Find & Replace dialog
CTRL + SHIFT + F brings up the same dialog, but with the Font tab active.
CTRL + SHIFT + L for AutoFilter
As long as the active cell is in a range of data, typing this keyboard shortcut will turn on AutoFilter for that range, putting filter dropdown arrows at the first row of data.
Select a cell in the range you want to filter:
CTRL + ALT + F5 for Refresh All External Data
Just open a workbook and type this keyboard shortcut to trigger a “Refresh All”, which refreshes all external data connections.
CTRL + P for Print
This shortcut is simple enough, it brings up the Print dialog / user interface.
CTRL + F1 for Collapse / Expand Ribbon
Use this to get more of a “full screen” effect in Excel (and when reading Word documents).
Expanded ribbon:
Before CTRL + F1
After CTRL + F1
CTRL + Page Up/Down for Sheet Navigation
Use these shortcuts to navigate through sheet tabs in the current workbook.
Before using Ctrl + Page Down
After using Ctrl + Page Down