Excel tips: how to save time with smart shortcuts

SAVE AND CLOSE: To save and close the active file you can hold the Ctrl key down and press S and then W. This leaves Excel open.

COPY THE WHOLE TABLE: With a cell selected in a table, you can copy the whole table by holding down the Ctrl key and pressing * and then C. I use the * on the numeric keypad. On a laptop you may need to use Ctrl + Shift + 8.

COPY A SHEET TAB: To copy a sheet tab, you click and hold on the sheet tab name at the bottom of the screen, then hold the Ctrl key down and drag the sheet tab to the left or right, then release the mouse, then release the Ctrl key. This technique works for most things in Excel. If you usually drag something in Excel to move it, then try holding the Ctrl key down while you drag to copy the item instead of moving it.

COPY OR INCREMENT NUMBERS OR DATES: Try holding the Ctrl key down as you drag the Fill Handle. The Fill Handle is the small black cross at the bottom right-hand corner of a cell or range. It either copies or increments the cell. 

Dragging with the Fill Handle will copy numbers and increment dates. With the Ctrl key held down, dragging the Fill Handle will increment numbers and copy dates.

Quick Access Toolbar

The Quick Access Toolbar (toolbar) is installed above the Ribbon at the top of the screen. Most people don’t use it, which is unfortunate. 

It only has a few icons on it when you first install Excel. To add an icon from the Ribbon, right-click the icon and select Add to Quick Access Toolbar. 

I display the toolbar below the Ribbon as it’s easier to access. To move the toolbar, right-click it and select Show Quick Access Toolbar Below the Ribbon. 

By adding your most commonly used icons to the toolbar, you can hide the Ribbon using Ctrl + F1 and maximise the screen area for the spreadsheet. Ctrl + F1 also unhides the Ribbon.

Validations

Keeping your files accurate is achieved by including validation checks throughout your file. I also recommend including a centralised validation sheet that has links to all the validations, so you have a central validation listing. This allows you to see all the validations in a single sheet, which can speed up the review/correction process. 

When you have linked to the other validations, it is a simple process of using the keyboard shortcut Ctrl + [ to follow the link directly to the validation cell itself. Press F5, then press Enter, to return to the validation sheet. This is an efficient way to monitor validations. A centralised listing also makes it easier to create a single overall validation check for the whole file.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia. Questions can be sent to [email protected]