ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tips & Tricks

Excel Tips & Tricks #464 - Refreshing Keyboard shortcuts

Author: Bani Lamba

Published: 20 Jan 2023

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello and welcome back to Excel Tips and Tricks! To start off the new year, we have a Basic User level post in which we’re taking a look at keyboard shortcuts that will improve your experience of using Excel. All of our posts are written with Excel for Windows in mind, but for this post especially so!

In the past we have looked at using function key shortcuts in Excel in Tip #270 and shortcuts you can use while writing formulas in Tip #352. In this tip, we will be covering some of the most useful shortcuts you can use to navigate the workbook, format cells and work with data.

Basic navigation

Let’s start with basic navigation! To navigate around the ribbon in excel using keyboard shortcuts you can use the Alt key. This will display the ribbon shortcuts which will appear as small letters called Key Tips as shown below.
Excel smart chip gif

You can then combine the letter in the Key Tip with the Alt key to navigate around the ribbon.

For example, to navigate to the Formulas tab you can use Alt + M which will take you to the Formulas ribbon. From there, you can see the Key Tips which can be used to move around the Formula ribbon.

Excel query gif

Some of the basic ways of moving the active cell around include using the following keys:

  • Arrow keys - move
  • Tab - Move right
  • Enter - Move down (if you tabbed beforehand, will return to the same column you started in)
  • Shift Tab - Move left
  • Shift Enter - Move up

You can also move in jumps. For example, using Ctrl and any of the arrows will allow you to move in "End Mode" - i.e. if you are in an empty cell, skip to the first filled cell; if you are in a filled cell, move to the last filled cell. Finally, holding Shift in combination with these navigation shortcuts will select the range.

More on how to navigate around active cells and between excel files has been covered in Tip #328.

Formatting cells

Some of the most commonly used keyboard shortcuts in Excel are the Ctrl + C and Ctrl + V shortcuts to copy and paste selected cells. These shortcuts will copy and then paste the values, formulas and all the formatting across to the new cells or worksheet.

However, there may be instances where you only need to copy and paste the values. You can do this in Excel Online easily by using Ctrl + Shift + V to paste the values of the cells only. While this shortcut won’t work in desktop versions, you can work around this by using Ctrl + Alt + V to open the Paste Special dialogue box. From here, you can select ‘Values’.

Excel tick box gif

Another handy shortcut to know is Ctrl + 1 which you can use to access the Format Cells dialog box in Excel. This will allow you to change the number, alignment, font, border, and fill formatting and add protection to cells easily.

Excel tick box gif

But there’s an even quicker way! You can apply different number formats to your cells directly using shortcuts. For example, if you want to apply a number format with two decimal places, thousands separator and a minus sign for negative values you can use Ctrl + Shift + ! to the selected cells.

There are many other number formatting shortcuts covered in the table below.

Format   Keyboard Shortcut 
General number format Ctrl + Shift + ~
Currency format with two decimal places (negative numbers in parentheses) Ctrl + Shift + $
Percentage format with no decimal places Ctrl + Shift + %
Scientific number format with two decimal places  Ctrl + Shift + ^
Time format with the hour and minute, and AM or PM Ctrl + Shift + @

Working with data

When you’re working with large data sets in Excel, it can be handy to know how to select data using shortcuts. You can select the entire worksheet using Ctrl + A or Ctrl + Shift + Spacebar. Note that if you use these shortcuts in a range of data then using Ctrl + A or Ctrl + Shift + Spacebar will select what Excel believes to be the entire range of data. To then select the whole worksheet, you will need to keep Ctrl or Ctrl + Shift held down and then press A or the Spacebar again. To select an entire column, you can use Ctrl + Spacebar.

If you want to create a table using the data, you can select the data and use Ctrl + T. The Create Table dialog box will appear which will allow you to create a table.

Excel tick box gif

Another handy shortcut to use when analysing data sets is Ctrl + Q. You can use the quick analysis pop-up to perform analysis on data using functionality such as conditional formatting, charts and sparklines.

Excel tick box gif

Google sheets

If you’re using Google Sheets, potentially the only keyboard shortcut you need to learn is Ctrl + ?. This will open up a searchable menu of all the keyboard shortcuts you can use in Google Sheets. Some of them are the same as Excel keyboard shortcuts, but it has its own special shortcuts too.

In the example below, we looked at some navigation shortcuts.

Excel tick box gif

Further guidance

If you’re looking for further guidance and information, we suggest taking a look at Microsoft’s support page on Keyboard shortcuts in Excel. This page also provides more information on using these shortcuts with different versions of Excel and operating systems.