Found Blog

excel-shortcuts

10 Great Excel Shortcuts (You might not know about)

Posted on in the categories

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?

  • http://twitter.com/freeg131 Philip Gamble

    Another great Excel efficiency post!

    Is there a keyboard short-cut that performs the same action as double clicking on the bottom right hand corner of a cell?

  • http://www.facebook.com/profile.php?id=565170313 Gar Eth Oly Ott

    These really are useful. Thanks for posting this.

  • http://twitter.com/Found_Luke Luke Townsend

    Very useful – something in here for excel users of every level, and thanks for finding me a use for my ’tilde’ key, on a 3 year old keyboard i think that’s the 1st time it’s been pressed not in error!

  • http://keithhorwood.co.uk/ Keith Horwood

    Good stuff

    For tip 3 – you can then drag those coloured boxes to automatically change the cells referenced in the formulas, which is very useful.

  • http://www.fernandobiz.com/ FERNANDO

    Great Tips from a blog aweosme blog. Really helpful informative points. thanks for the share Jamie. The best thing was I found out your blog when i was searching for SEO companies in London to see which companies comes up on the first page. Good to see true colors from the top rank SEO firm.

    Thanks for the share Jamie. Will be around reading some more posts to see what best you guys have.

  • XLCalibre

    A good selection!

    On number 6 I think it should be Alt + Enter?

    Another shortcut that goes well with number 7 is Alt+AC which resets all autofilter criteria – a real time-saver.

    I’ve got a few more shortcuts on http://xlcalibre.com

X