11 Excel secrets for accountants

Everyone in business will cross paths with a spreadsheet at some point. But how do you get the most out of the ubiquitous Excel program? Read on to find out.

It’s no secret that CPAs make good use of the almighty Excel spreadsheet program; it’s an invaluable tool that helps us process complicated data sets and communicate large amounts of information to clients and colleagues. But working with data and multiple sheets can also represent a significant time suck, with most of us only ever using 10 per cent of what Excel has to offer. Whether you stick to basic functions, or are a macro-writing, pivot-table-creating, multi-referential workbook workhorse—these 11 tips might yet turn you into an Excel superstar.

  1. Copy formatting using the format painter function (F4)
    Save yourself the irritation of repeating a cumbersome three- or four-step format process via the dropdown menus with the Format Painter button. It will copy the formatting of any cell you choose, and pressing it again while on the target cell will transfer the format. Double click on the format painter button to change multiple cells at one time. It’s like magic.
  2. Edit within a cell using F2 (PC) or Control + U (Mac)
    Double-clicking into a cell to make changes is frustrating, and often results in typing over the whole cell instead of making an edit. Try F2 or Control + U and save yourself the headache.
  3. Select a row with Shift + Spacebar / Select a column with Ctrl + Spacebar (PC & Mac)
    Manipulating rows and columns through the dropdown menus is a big waste of your precious time. These two shortcuts will allow you to quickly select the portion you need.
  4. Paste values with Ctrl + Alt + V (PC) or Control + Option + V (Mac)
    Dealing with changing formats in an Excel spreadsheet template that you use frequently (or that others have access to) can make you feel like you are on a hamster wheel of pasting, undo-ing, changing, and re-doing. No fun at all. Using the Paste Values function allows you to just plug in the numbers you want, and leaves out any formulas, calculations, and formatting that might be attached to the original. To find this function, use the Paste > Paste Special menu, or get there with Ctrl+Alt+V.
  5. Apply currency format with Ctrl + $ (Shift + 4) (PC & Mac)
    There are a bunch of formatting shortcuts you might find useful, but if you are dealing with money, Ctrl + $ is the way to go.
  6. Move from tab to tab with Ctrl + Tab (PC) or Option + Tab (Mac)
    Multi-tabbed worksheets can be powerful archives of information. The more data you have, the more mindful you need to be of how to quickly navigate through it. Ctrl + Tab is a quick way to cycle through all tabs in a workbook.
  7. Insert an AutoSum formula with Alt + = (PC) or Command + Shift + T (Mac)
    A fairly basic function, the AutoSum formula can save you a good chunk of time. Go pro with the keyboard shortcut of Alt + = (PC) or Command + Shift + T (Mac).
  8. Apply the COUNTIF formula (PC & Mac)
    Dig into your data with the very useful =COUNTIF formula. This versatile formula can help you determine how often something is or isn’t happening. Say you wanted to know how often your company’s daily sales total exceeds $5,000. If you have a list of days in column A and the totals in column B, this would do the trick: =COUNTIF(B1:B31,">5000").
  9. Insert a line break within a cell with Alt + Enter (PC) or Option + Enter (Mac)
    Using data to communicate requires that you make your presentation of the data clear. This isn’t necessarily Excel’s strength. Using a function like inserting a line break within a cell can give your data a bit of room to breathe, and help you make your case to clients or your colleagues.
  10. Toggle between formulas and values with Ctrl + ~ (PC & Mac)
    Formulas are a powerful part of the Excel arsenal, but it can be easy to lose track of where you are in a complex nested formula. Ctrl + ~ displays the formula in any selected cell so that you can suss out where you’ve gone wrong. Tap that combination again to go back to the values view.
  11. Save your sanity with Ctrl + S (PC) or Command + S (Mac)
    This is a very basic function, and one that most users will be familiar with, but it’s arguably the most important on this list. If you use Excel at all, you know that its propensity for freezing is astounding, and that the chances of a crash increase depending on how busy you are, how tight of a deadline you are under, and the amount of work you’ve done since your last save.

Do yourself a great favour and save your work as frequently as possible. Like right now.


Have any tips of your own for mastering the Excel beast? Post a comment below.


The views and opinions expressed in this article are those of the author and do not necessarily reflect that of CPA Canada.

You may also be interested in:

Learn how professional development is changing at CPA Canada to address the changing needs of its members.