Search Not Just Numbers

Tuesday, 26 February 2019

Excel Tip: Some useful keyboard shortcuts to save you time

Hello stranger!

I must start by apologising for the huge expanse of time that has passed since my last post. My consultancy workload has increased over the last year or so and the blog has been the main casualty!

I'm still pretty busy and may not be able to post as regularly as I have at some times in the past, but I will endeavour to post at least a post a month.

OK, on with this month's post...

Personally, I am not a huge user of keyboard shortcuts - as I struggle to remember too many - but there are a few that I find particularly useful, and I thought I'd share those with you. I'm sure many of you have your own favourites, so please share them in the comments. Mine are all for the Windows version. Any Mac users please feel free to post the Apple equivalents.

Here are my favourites...

F4 - add dollar signs

I use this one all of the time. To add the dollar signs that fix rows and columns in formulae, you can simply press F4 while your cursor is on the cell reference in the formula, and each time you press it it will toggle to the next combination of $ signs in the following sequence - none, fix column and row, fix row, fix column and back to none.  If you don't understand how the dollar signs work, take a look at one of my most popular posts - The dollar sign ($) in a formula - Fixing cell references.


Ctrl+; - Enter today's date

This one is really useful when entering data into a spreadsheet, you can enter today's date into the current cell by simply pressing the Ctrl key and the semicolon together.

Ctrl+Shift+; - Enter the current time

Similar to the shortcut above, you can enter the current time into the current cell by pressing the Ctrl  and Shift keys and the semicolon together.

Ctrl+C - Copy
Ctrl+V - Paste
Ctrl+X - Cut

These are standard windows shortcuts, but are as useful in Excel as elsewhere. Be wary though about using cut/copy and paste generally, see these earlier posts if you aren't aware of them:


Alt+F11 - Open Visual Basic Editor

This is a one for the coders out there and provides a quick method to open the Visual Basic Editor. This shortcut holds a special place in my heart as I managed to completely forget what it was on a live Webinar I was doing for the ICAEW!

F9 - Calculate

This forces a recalculation of your Excel Workbook, which is useful if you have manual calculation switched on. A lesser known use is that you can highlight a section of a formula and press F9 and it will swap the highlighted text for its value - this is great for trying to find where the problem is in a long formula! See this post for more details - How to evaluate individual parts of a large formula.

As I said, I'm not a big shortcut user, so I'm sure many will consider some of the ones I haven't listed a travesty, so please restore justice in the comments!


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

8 comments:

  1. Got to be CTRL+Shift+Down Arrow to get to the bottom of a list of figures and select them all, thus giving you the sum, average and whatever else you have turned on in your bottom toolbar area

    ReplyDelete
  2. two I use regularly are CTRL+D and CTRL+R to copy the contents of the cell above and to the left respectively ... i.e. Down and Right

    ReplyDelete
  3. I'd never used that one. I will now though!

    ReplyDelete
  4. Replies
    1. Reports of my death have been greatly exaggerated :)

      Delete
  5. I find Ctrl+[ very useful to jump to the source of a link (I use it to track numbers back to their source). It also opens the relevant file if the link is to an external workbook.

    ReplyDelete
    Replies
    1. Again, another useful one. I should use more of these :)

      Delete