Sometimes it’s the little things that make a big difference so today I’m going to share with you a few of my favourite Excel shortcuts. They're not necessarily all keyboard shortcuts either.
These are the type of skills that you pick up over the years watching your colleagues wield their Excel swords.
So, without further ado and in no particular order:
1. Copy and Paste as a value from a cell containing a formula – F2, F9, ENTER. For example you have a formula in a cell like this:
F2 to edit the cell:
F9 to calculate the formula:
Press ENTER to keep it as a value:
Note: you can only do this with one cell at a time.
2. F3 – Paste a named range into a formula. This is handy if you can’t remember the name you’re looking for. Pressing F3 while writing the formula brings up a list of your names and you simply choose the one you want:
Of course if you know the name of your range you can start typing it out and then select it from the list using the arrow keys or mouse, and then tab or double click to select it:
3. F11 – insert a chart. Select your chart source data, press F11. This will insert a chart on a new worksheet in the file. If you want your chart on the same sheet as your data you can use ALT + F1.
4. F12 – Save As. Remember ‘Save As’ is only necessary the first time you save the file, after that you can just use CTRL+S to save updates. This works in Word and PowerPoint too :-). Actually it probably works in other Microsoft Programs but I stopped testing at PowerPoint.
5. F4 – Repeat/re-do. This works in other Microsoft Programs too.
6. F4 also inserts the dollar signs for absolute references when editing a formula.
Tip: you don’t have to select the whole cell or range reference to set it, just select part of what you want to absolute. For example; in the image below you can see I have only selected from 2:D:
Then press F4 and it will absolute both D2 and D13:
Pressing F4 again and again will scroll through the levels of absolute reference from column to row, or all.
7. CTRL+Z – undo
8. CTRL+* or CTRL+A to select the current range while at least one cell is the range is selected.
9. CTRL+# to apply Date format and CTRL+SHIFT+! For two decimal format.
10. CTRL+; Insert today’s date.
11. CTRL+O – open file.
12. Select all objects (charts, shapes, pictures etc.) – left-click on one and then press CTRL+A to select them all.
13. CTRL+Pg Up/Pg Down to scroll through worksheets.
Or, if you’ve got a lot of worksheets you can right-click on the scroll buttons for the sheets to reveal a list of sheets – click one to (Activate) jump to it or select ‘More Sheets’ and double click the one you want to go to.
14. Double click the edge of the selected cell to jump to the end of the data. For example; double click the bottom of the selected cell to go to the end of the range of data. Or do the same using the keyboard - press CTRL + down/up/left/right arrow.
15. CTRL+SHIFT+ down arrow to select the column of data. Likewise with the other arrow keys.
16. CTRL+TAB to toggle between workbooks.
17. CTRL+1 opens Format Cells dialog box….I could go on all day with the CTRL shortcuts as there are tons of them.
18. ALT+= for AutoSum.
19. Auto insert totals: select table of cells including a blank row/column.
Press ALT+= to insert totals:
20. If you enter a lot of numbers and sums using the numeric keypad you might find it quicker to start your formulas with the plus sign instead of the equals sign.
For example: you can type +20+10 then press ENTER and Excel will complete the equals sign for you like this: =+20+10 Perfect if you don’t want to reach all the way over to the equals sign!
Likewise you can do this +VLOOKUP(A8,A2:D13,2,FALSE) and Excel will complete it like this =+VLOOKUP(A8,A2:D13,2,FALSE).
That’s just a few (well, 20 actually) that come to mind. You can find more in my 100 Excel Tips & Tricks eBook.
I’d like to thank my friend, Shannon Hommel, as she sent in quite a few of these handy tips.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.
What are your favourite shortcuts or tips? Please take a few minutes to share them in the comments below.