Despite its age and the host of other options now available, the venerable Microsoft Excel spreadsheet remains the first port of call for many accounting professionals. Sadly, qualifying as a chartered accountant doesn’t mean that you are automatically an Excel super-user. Use these tips to enhance your productivity and streamline your spreadsheets.
Work Smarter not harder | Be a productivity ninja | 20 & 21 September | REGISTER TODAY
Use Sparklines
A built-in Excel feature, Sparklines allows the user to display small line, bar or win/loss charts inside individual cells to simply and effectively display data trends in a compact format. To create a Sparkline, select the range of numbers you'd like to include, access the "Insert" menu and choose one of the chart options. Select a location range (along a single row or column in the same worksheet as your data range).
Hide zero values
Large data sets can be cumbersome to peruse, so try hiding zero values to see them more clearly. All you need to do is click on the "File" drop-down menu, and choose "Options." Then choose "Advanced" from the left-hand menu and uncheck the box for "Show a zero in cells that have zero value."
Use Excel templates?
No matter what version of Excel you are using, templates can you're your life easier. Under the "Spreadsheet Solutions" tab, you will find various templates, including an amortization schedule, which can then be inserted into the spreadsheet. You can also create your own templates by saving worksheets in the template format.
Use the F4 shortcut
Shortcuts are indispensable in a program like Excel, when repetitive actions can become mind-numbing. The F4 key is particularly useful, because it allows you to copy any formatting you have applied to a cell to another cell. To copy formatting from one cell to another cell or column, just apply the formatting, click where you want the formatting to appear and press F4.
Tally data appearances
The COUNTIF function counts the number of cells that meet your specified criteria, such as supplier payments, dates and particular debits. In the cell you want your total to appear, enter the formula =COUNTIF(range,"criteria").
Under range, specify the cells/columns it should search, and under criteria, specify the data it should search for. In your criteria, you can search for text, a number, cell reference, or a comparison.
