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!
Basic navigationLet’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.
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.
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.
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’.
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.
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.
|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.
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.
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.
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.
- Excel Tips & Tricks #468 – Refreshing dealing with Objects
- Excel Tips & Tricks #467 - Excel functions that do not return arrays or ranges
- Excel Tips & Tricks #466 - Setting range permissions in Excel Online
- Excel Tips & Tricks #465 - Using #N/A in charts redux
- Excel Tips & Tricks #464 - Refreshing Keyboard shortcuts
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.