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:
CTRL-C - Copy from a cell or a range of cells
CTRL -X - Cut from a cell or a range of cells
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.
CTRL-P - Displays the Print menu
ALT-M - Go to the formula tab on the ribbon
CTRL-O - Open a workbook
CTRL-W - Save a workbook and close
CTRL-Z - Undo
ALT-H, D, C - Delete the contents of a column. As for Delete, no warnings given
ALT-H, D, R - Delete the contents of a row. As for Delete, no warnings given
ALT-H, H - Got to the fill colour menu
CTRL-B - Make a cell or range of cells bold
ALT-H, A, C - Centre align selected cells
ALT-H, D, C - Delete a column
CTRL-9 - Hide selected rows
CTRL-0 - Hide selected columns
CTRL-Prt Sc - Creates a screen shot as an image which can then be pasted into another applications e.g Word using CTRL-V
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.
F2 - Puts the selected cell into Edit mode.
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.
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.
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.
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.