Search

Six Excel hacks to become a spreadsheet pro

By Anastasia Stefanidou, Associate Manager - Branded Content and Channels, Association of International Certified Professional Accountants

Many people are comfortable with Excel. After all, it’s one of the most established parts of an accountant’s toolbox. But few master every intricate shortcut and feature. 

Using the software accurately and quickly can greatly improve your productivity and show your usefulness to your employer. 

Excel has a huge number of features. Here are some of the most advantageous hacks we hope will help even those of you who can set up a pivot table in your sleep from the 100 quick technology tips article by Kelly L. Williams, CPA, Ph.D., and Byron Patrick, CPA/CITP, CGMA (some features are available only in the latest Microsoft Office and Windows versions).

  1. Hide zero values. 
    Including many zero values in your data can be distracting. To easily hide them, go to the File tab, Options, Advanced and uncheck Show a zero in cells that have zero value. 

  2. Sort data based on colour. 
    Sorting data is not limited to sorting based on cell values. Data can also be sorted based on cell colour and font colour. To do this, select the data to be sorted. On the Home ribbon tab, select Sort & Filter from the Editing group, then select Custom Sort. Ensure that My data has headers is checked if headers were included in your selection. In the Sort by drop-down list, choose the column you want to sort. In the Sort On drop-down list, choose Cell Color or Font Color. In the Order drop-down list, choose the colour you want to be shown first. Next, click on Add Level, located at the top left of the Sort window. Complete the same steps as above for the second colour that should be shown, and so on until you have instructed Excel on the order to sort all cell colours or font colours.

  3. Copy visible cells only. 
    You may have a spreadsheet with hidden rows and/or columns but want to copy only the cells that are visible. To do this, press F5, Special, Visible cells only, OK. Then press Ctrl+C to copy.

  4. Visually represent your data using Sparklines. 
    Sparklines are small charts that fit in a single cell and are used to visually represent your data (see cells G3:G5 below). Select the cell or cells where you would like the Sparkline(s). On the Insert tab within the Sparklines group, choose Line, Column or Win/Loss. Select the data to be included in the Sparkline(s). 



  5. Quickly insert today’s date/time. 
    To insert today’s date in your Excel worksheet, press Ctrl+; (semicolon). To insert the current time in your Excel worksheet, press Ctrl+Shift+; (semicolon).

  6. Recover an unsaved workbook. 
    Excel will autosave your Excel workbooks (by default, every 10 minutes). If you have not previously saved your workbook and then close the workbook by mistake — or if your computer closes without the workbook being saved — you can recover the document. Go to File, Open, Recent Workbooks, Recover Unsaved Workbooks and choose the file that was closed before it was saved.

To celebrate Spreadsheet Day 2019 we have a 20% discount on all Excel products until the 24 October 2019 (use code BOSS).