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.
For the slightly more advanced users, there’s also our latest Excel Cheat Sheet. It’s packed with formulas and macros aimed at keyword marketers but useful for anyone who wants to sharpen up their skills and knowledge.
As of January 2021, we’ve refreshed this list with an additional 10 shortcuts that will knock your socks off with their sheer usefulness!
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!
11. Ctrl + Alt + V + V (paste as values)
Pasting as values strips formatting away from what is being pasted. So if the sheet you are copying from has green text, pasting as values means you don’t have to change it back to black manually – it will inherit the black text format of the destination sheet. This is how I need to paste 90% of the time. The shortcut gives you a way to paste just the text without formatting, using only the keyboard rather than going through the right click menu. It runs through the steps in the menu, then you hit enter at the end. A true efficiency booster if you paste as values a lot.
Bonus: Ctrl + Alt + V works across Word and Outlook too, making it easy to strip formatting and just keep pure text for the applications. Especially useful for pasting text from websites into emails. It brings up a menu of pasting options, just surf up and down with the arrow keys and hit enter.
12. Ctrl + Space (select the whole column)
This grabs a whole column in one go. Once it is selected, it is yours to do with as you deem fit. Ctrl right or left to extend the selection to other columns.
13. Shift + Space (select the whole row)
Same as the above, but for rows. No more moving the mouse to the little row starting box and carefully clicking it – just grab them with and arrow keys and this shortcut.
14. Ctrl + Shift + Plus (insert a new row / column)
Once you bave used Ctrl + space or shift + space to grab a column or row, you can press ctrl + shift + plus and a new row or column will be inserted before the current one. If you have copied or cut a row of column prior to this, pressing ctrl + shift + plus will insert that copied or cut row. This way, you can swap around whole rows or columns with the keyboard. I regularly use this to rearrange columns on things like keyword research, without reaching for that oh so inconvenient mouse.
Bonus! If you press F4 after doing this, it will repeat the action.
15. Ctrl + Minus (delete row / column)
The Yin to Ctrl + shift + plus’s Yang, this will eliminate any rows or columns you have selected in their entirety. Employing this simple shortcut means you can delete whole clusters of rows or columns using the keyboard alone. Also repeat with F4.
16. F12 (go directly to the Save As dialog for the current folder)
The champion. While not strictly just an excel shortcut, it’s the one I use the most. When I use Excel I am always needing to Save As so I want it quick and simple. Excel has its own saving and opening interface which is snazzy looking but paradoxically seems built to increase the number of clicks required to reach the final saving folder window. No more of this with F12, which takes me straight into the saving folder in which the file currently exists, which is where I want to be.
Bonus: Works with Word too!
17. Alt + 5 (put file as attachment straight into an outlook email)
For this one to work you have to click ‘customize quick access toolbar’ and select ‘Email’, just once. After this, when you press Alt + 5, your current Excel file will be catapulted into a new Outlook message, with itself as the attachment ready to go!
18. Alt + A + C (clear all filters)
If you do a lot of filtering, for example in keyword research and grouping, you’ll know that for every filter turned on, a filter must be turned off, right? Wrong! Turn them all off at once with Alt + A + C. Good for when I realise I need to delete a column but can’t because filtering is on, and I need it off immediately. This is one of the more beautiful shortcuts to use, applied with an elegant inward motion of the thumb, followed seamlessly with the ring finger and index finger. It’s magic.
19. Alt + A + M (remove duplicates)
Get straight into it – if you do this a lot you’ll appreciate this shortcut. It brings the remove duplicates box up. Follow it with a few tabs and enter and you can have your duplicates removed in seconds without using the mouse.
20. Alt + H + A + C (center text)
Centered text looks great in tables. To make it happen without reaching for that oh so cumbersome mouse, this one proides an alternative that’s easier on the wrist. It also sounds like ‘hack’ so you can remember it.
Warning: Don’t try to use this in google sheets – instead of centering your text, it will bring up the web page for reporting your sheet as abusive content!If you’re interested in how Found could supercharge your digital performance across SEO, PPC, Social and Digital PR, then get in touch today to speak to a member of the team.
