Hello and welcome to another Excel blog post – third in this series, where I attempt to explain and demonstrate some great Excel shortcuts for you to use in your everyday working lives (or just for fun). If you’re not already familiar with them, you may want to check out my previous posts: 5 Great Time-Saving Excel Tips and 5 Great Uses of the IF Formula.
My previous post turned out to be a bit of a beast, so I thought I’d strip this one way back for some quick, punchy tips that you can pick up and put to work right away. So without further ado, here are the shortcuts..
1. ALT + = (autosum a column)
I first read this tip elsewhere about 3 months ago and scoffed at it openly. “How much time can this possibly save me?” scoffed I. Well, apparently quite a lot, as I’ve used this shortcut all the time since, every day. Simply use this shortcut at the bottom of a big list of numbers and it’ll do an immediate sum of them for you. If you find yourself doing this kind of thing regularly then it’s definitely worth remembering.
2. Ctrl + Tilde Key (shows formulas in cells)
If you’re not sure what the tilde key is, it’s the one at the top left of your keyboard next to the number 1. That is, the tilde key was traditionally always there (~) but now probably has something like the following (¬) instead. Regardless, that is the key. The one next to the number 1 look (If you’re a mac owner then you’re probably beyond all help, sorry).
Anyway this shortcut can be used to toggle showing the formulas on the page. It can be especially good if you’re analysing and comparing formulas next to each other.
3. F2, or double click (show formulas and references)
This tip was added after watching several colleagues struggle to keep tabs on what cells were being referenced in some of their formulas. Stupidly easy – rather than running your eyes/finger up and down the page to find the cell reference, just double click on the cell (or press F2 on it) and it will go back into edit mode and show all the referenced cells in nice colourful boxes.
4. F3 (brings up list of named ranges used in sheet)
If you don’t make use of named ranges in your worksheets, then this tip might not make sense – but that should really give you more impetus to start using them, so you can come right back here and learn how to find them again! As it turns out, it’s incredibly easy – simply press F3!
5. F4, whilst writing formula (locks cells with $ symbols)
In my previous post I talk about the importance of using $ symbols to lock formulas in place. This can be a pain if you’re working on particularly lengthy formulas. However if you press F4 after the cell reference it’ll add these in for you. Have a go at playing with this now!
6. Alt + E (start a new line in the same cell/wrap Text)
More of a presentation one, this. You may be aware that the ‘Wrap Text’ button allows text within a single cell to be displayed on separate lines (as opposed to having a long line of text spill into other adjacent cells). Well with this shortcut you can move the cursor manually down a line while typing, which gives you that extra little bit of control.
7. Ctrl + shift + L (turn on/off filters)
If you’re a regular user of Excel, then chances are you use filters on quite a regular basis to quickly drill down to relevant info. I can’t really dress this one up too much – simply put, if you want to toggle filters on and off much more quickly then use this shortcut on any set of data!
8. Ctrl + H (find and replace)
You can refer to the 5th tip on my first Excel post, which demonstrates some powerful uses of find and replace. This is a very commonly used process for a lot of the analysts here at Found, as they use it to quickly chop and change data, as well as using it for quick edits on keywords and search terms. Note: This includes the standard ‘find’ function in the same popup box, which you can easily flick to if needed. Alternatively you can just use Ctrl + F (Hey, two shortcuts in one tip!)
Here I’ve demonstrated the shortcut with one of my grammatical pet hates. “Your” is the possessive word, and “you’re” is an abbreviation of “you are”. Honestly people, let’s get this back on track! (/end rant)
9. Ctrl + Home (go to cell A1)
And now onto the final two shortcuts, which are both based around simple keyboard navigation. I find myself using a mouse less and less with Excel these days, as it’s a lot quicker to zoom around with the keys. These final two tips are really extensions of the 4th point from my previous post on time saving tips. Ctrl + Home – shoot the cursor back up to cell A1, aaaand…
10. Ctrl + Page Up/Page Down (flick between tabs)
This is used to flick between tabs on an Excel worksheet. When used in conjunction with other keyboard quick navigation keys, these will enable you to fly around the page(s) with ease. There’s no stopping you now, so get stuck in and start using these shortcuts now!
Thanks for reading, and if you have any suggestions or requests for further Excel topics, why not message me?