• Got it Pass Team

Excel Shortcut Keys List: Useful Tips for Excel 2019

Updated: Sep 30, 2020

Shortcut keys are very useful for performing various operations within a workbook. They can save a great deal of time, when you would normally go through the ribbon menu and then click on the appropriate option. The important thing is remembering the key combinations and what the shortcut does!

20 Shortcut Keys of MS Excel

Here are some examples which are particularly useful to accountants:

  1. CTRL-C - Copy from a cell or a range of cells

  2. CTRL -X - Cut from a cell or a range of cells

  3. CTRL-V - Paste into a cell. A big advantage here is that if you paste into another application such as Word or an email, your range of cells will be pasted in as a tabular format, so that it will look exactly like the spreadsheet.

  4. CTRL-P - Displays the Print menu

  5. ALT-M - Go to the formula tab on the ribbon

  6. CTRL-O - Open a workbook

  7. CTRL-W - Save a workbook and close

  8. CTRL-Z - Undo

  9. ALT-H, D, C - Delete the contents of a column. As for Delete, no warnings given

  10. ALT-H, D, R - Delete the contents of a row. As for Delete, no warnings given

  11. ALT-H, H - Got to the fill colour menu

  12. CTRL-B - Make a cell or range of cells bold

  13. ALT-H, A, C - Centre align selected cells

  14. ALT-H, D, C - Delete a column

  15. CTRL-9 - Hide selected rows

  16. CTRL-0 - Hide selected columns

  17. CTRL-Prt Sc - Creates a screen shot as an image which can then be pasted into another applications e.g Word using CTRL-V

  18. F9 - This recalculates the entire spreadsheet. Normally, recalculation would be set to automatic, so that whenever a change is made, the entire workbook is recalculated. However, if the workbook is large or has complex formulae, it can take several seconds to recalculate, and it may be necessary to set recalculation to manual. You can then make several changes and the press F9 to recalculate without wasting time.

  19. F2 - Puts the selected cell into Edit mode.

  20. Delete or Del - Delete cell contents or selected range contents – note that there is no ‘Are you sure?’ warning. However, you can always use ‘Undo’

All the commands within the Excel ribbon have a shortcut. You can see these if you press the Alt key.

For example, if you pressed ALT-A you would move straight to the data tab on the ribbon

If you pressed ALT-A, T, this would take you to the Filter menu

One extremely useful shortcut on Windows 10 which is not specific to Excel and can be used on any application is a Windows Snipping Tool. The shortcut for it is WINDOWS-SHIFT-S. This allows you to select a portion of the screen using the cursor and copy it to the clipboard. This can then be pasted into another application e.g. Word.

This is very useful if you want to show a colleague a specific part of a spreadsheet as an image, but you do not want to send the entire spreadsheet file.

Workbook Structure

The Excel workbook is the highest-level object. It is a collection of worksheets up to a maximum of 256.

There must be at least one worksheet. When a new workbook is created, there is a default value for the number of worksheets in it, usually three.

This can be changed through File > Options > General which will allow you to select the number of worksheets when the new file is created.

Change the default value for the number of worksheets

You may require worksheets to be hidden. If you have sensitive data that is used in calculations, but you do not want other users to have access to it, then you can use a hidden sheet.

To make the sheet hidden, you right click on the tab name as shown at the bottom of the sheet, and a pop-up menu will appear.

Select ‘Hide Sheet’ and the sheet will not be shown

To show the hidden sheet, right click on any tab and click on ‘Unhide’.

This will show a list of all hidden sheets. Select the required one and click OK

If you have a workbook that has a large number of sheets, it can become difficult to navigate through them, as some spill off the scrolling tab bar along the bottom.

The easy way is to right-click on the small arrows in the bottom left of the workbook, and a list of available sheets will be displayed.

Click on the one you want to go to and click OK

You can edit the sheet name and change the colour of the tab by right clicking on the tab name and selecting the required menu option.

You can also change the order of tabs by dragging and dropping them into their new position.

You may want to share your workbook with colleagues, but you may not want them to make any changes.

You can password protect the workbook. This is done on the Review tab of the ribbon and clicking on ‘Protect Workbook’. This will request a password (which is optional).

Make sure that you remember the password as there is no way of recovering it if it is forgotten!

Once the workbook structure is protected, no user (including yourself) can insert, delete, rename, move, copy, hide, or unhide worksheets unless they have the password to unprotect. If a password is not given, then all anyone has to do is to click on ‘Protect Workbook’ and the workbook is unprotected.

Password is optional in protecting a workbook.

Worksheet Structure

Each individual worksheet is made up of rows and columns which define each cell which holds your information.

Cells can contain numbers, text, or formulae.

There are an enormous number of options available in terms of how each cell is formatted and how it is viewed by the user. This covers items such as borders, colours (background and font type/size), and alignment

The user can insert and delete rows and columns, hide rows and columns, insert pivot tables and charts, and add graphics e.g. a company logo.

As with the workbook itself, each individual worksheet can be password protected to prevent other users making changes which may destroy the integrity of the worksheet.

To protect an individual worksheet, you select the required worksheet and then select the Review tab on the ribbon and click on ‘Protect Sheet’

A password is required – as for the workbook password do not forget it as it cannot be recovered!

A number of options are available on the pop-up menu. By ticking the appropriate boxes, you can allow users to insert columns or delete columns, do formatting on cells, etc.

The protection for altering contents or hiding contents will only work on the locked cells on the sheet. All cells are locked by default when a new sheet is created.

To unlock cells, select the required cells and right click on it. Click on ‘Format Cells’ in the pop-up menu and select the ‘Protection’ tab in the formatting menu. You can uncheck the Locked box if you want the user to have access to changing the contents of the cell. You can also hide the contents by checking the Hidden box. You may have a formula in the box that you do not want other users to see.

Protect worksheet

Workbook Design Principles and Tips

Design principle matter very much when designing your workbook. They make it easy for the user and prevent errors creeping in. They also help any one else who has to support it in your absence.

The fastest way for a user to lose confidence in a workbook is for errors to start appear.

1. Opening tab to describe the workbook

A good idea on a workbook is to have an opening tab which introduces the user to the workbook. Give some information about what the workbook is all about, what it is achieving, and a map of the visible sheets in the workbook and what they show. Sadly, this needs a small piece of VBA code to make it the default sheet when the workbook is opened – there is no easy option to set a default sheet on opening.

2. Hide sheets and areas which the user not need to see

The user does not need to see complex data areas and calculations being made. These can detract from the user experience, and in some cases a user may feel that something is incorrect and start losing confidence in the application

3. Keep input areas on one sheet and highlight them

Where you want users to enter data, keep the input area to one sheet so that they are not moving backwards and forwards through a whole load of sheets looking for where they need to put a number.

You can easily use formulae to take the input data to the appropriate places where the calculations take place.

Highlight the input cells with colours. This can be useful if you have written user documentation. You can state ‘input number into the red cell’

You can also add notes to a cell by right clicking on the cell and clicking on ‘Insert Comment’. You can add advice on input – this will show as a red triangle in the top right-hand corner of the cell. When the user hovers their cursor on the triangle, the note will appear.

Use cell validation to prevent incorrect values being entered which will cause errors elsewhere in the workbook

4. Lock and protect the workbook and worksheets

Do not allow users open access to do anything with your workbook or worksheets. They may change something unintentionally and then save the workbook. The integrity of the workbook will be destroyed by this.

5. Keep formulae simple

You can enter several steps of functions into one cell. The problem is that someone else may take charge of the maintenance of the workbook and have considerable trouble trying to understand what your formula is actually doing.

For example, if your formula goes onto several lines in the edit box it may be very difficult to pick it apart.

An easy way is to use a hidden sheet and to use the functions in steps in a row of cells. You put the first part of the calculation in one cell, and then the next step in the cell to the right of it. You can then ‘daisy chain’ the calculation across. This also gives a good audit trail for a calculation in that you can see the result of each step. This is very useful if a bug is found within the workbook.

6. Beware of linking formulae to other workbooks

Excel allows formula to refer to another workbook but this can be dangerous. If a user does not have access to the drive or folder where that workbook is kept then errors will occur, and no user wants to see errors happening.

Also, the referenced workbook could change in structure or location of data, which could also cause errors to happen

7. Thorough testing

Give the workbook to your colleagues and ask them to act as a user and see if they can break the system. The person who created the workbook and designed all the calculations and data validation is the worst person to test it because they know exactly how it works. Another pair of eyes new to the workbook will try all sorts of scenarios which the original designer had never thought of.

8. Making changes

Once the workbook is live and users are depending on the data coming out of it, changes are likely to be needed. Provide version numbers in the filename and on the worksheets when a change is made

Before a change is made, always take a backup copy of the workbook. If you do anything wrong with it in making the change, you need a simple way back to a stable state.

Also, on a hidden sheet, keep a change log that is updated every time a change is made. Keep the details of what the change was, which worksheets it affected, who asked for it, who did it, and who tested it.

9. Documentation

Keep documentation on a separate hidden sheet showing the mechanics of how it all works. You will not be working at your organisation for ever, and a new person may have to pick up your workbook and maintain it.

#excelshortcutkeys #msexcel2019 #excelworkbook #excelworksheet

If you find this article is helpful and you want to help others too, just share it in any social media (such as Facebook, LinkedIn).

Got it Pass Team is happy to work with Richard in presenting this article.

About Richard Shepherd-

Richard is an expert in MS Excel and Access with extensive experience of developing MI reporting systems, databases and dashboards using VBA or RAD for major companies such as Santander, Nationwide Building Society and Zurich Insurance. He is also the author of Access 2010 VBA Macro Programming and Excel VBA Macro Programming. He was graduated in University of the Thames Valley, United Kingdom.

Note: Some of the links in this page contain affiliate links. I will earn a small commission for any purchases you make, though there is no extra cost to you. 

The products or services I recommend only I use them myself or after my investigation and truly believe they are useful to you. Don’t forget there are many other options out there – these are simply my preferred choices.