Excel is a great tool for auditors, but oftentimes we do not utilize the shortcuts and other tools available to increase our productivity. This post will focus on some shortcuts I use to be more efficient.
My favorite and most used shortcut is auto filling formulas. Make sure you read the post on relative and absolute referencing or else you might make a critical error when auto-filling formulas.
Data Entry Tips
CTRL+D, =SUM formula
To auto-fill a formula down, you can use the command CRTL + D. First, enter the formula you want to apply in the topmost cell of the column you are working in. Highlight the cell with the formula and all the other cells you want filled in. Now hit CTRL + D and the formula will now be applied to all of those cells. If you are working left to right in a spreadsheet, the same approach can be used with CTRL + R.
A helpful trick when working with big datasets is to use CTRL + Arrowkey to jump to the end of a row or column. To highlight those cells use CTRL + SHIFT + Arrowkey. If you keep holding SHIFT, but drop CTRL, you can modify the selection using arrow keys. For example, you can deselect the row or column label.
Excel has a handy shortcut if you work with dates and times. To enter today’s date simply hit CTRL + semicolon. For the current time hit CTRL + Shift + colon.
For a long time I could never get Excel to work nicely with adding another row of text to a cell. I would turn on text wrapping and add in spaces until the text I wanted dropped down a line. Quite an inefficient process. Save yourself time and use ALT + ENTER to put a return space.
Switch cell formats to currency by hitting CTRL + SHIFT + $. To change it to text/General use CTRL + SHIFT + ~. For Percentages use CTRL + SHIFT + %. For numbers, CTRL + SHIFT + !. For Dates, CTRL + SHIFT + #. Want to switch to accounting format? Send Microsoft an email requesting that a shortcut feature be added, because right now there is no shortcut and you’ll need to use the manual process. The quickest way is to pull up a cell format dialog box using CTRL + 1.
To auto-sum fields you have entered use ALT + =. Select either a cell below a column or row needing summation.
Inserting rows and columns can be done by highlighting either an entire row or column and then hitting CTRL + SHIFT + =.
Editing Cell Formulas
If you select a cell and hit F2, Excel switches you to editing the cell formula. This is a great shortcut rather than having to go to the ribbon each time. One of the most important changes you can make is switching between absolute and relative references using F4.
F3 is handy if you use named ranges in complex formulas. This command will pull up the named range dialog box.
One of the best ways to review worksheets is to inspect the formulas used to calculate various fields. You can access this option using the ribbon, but a quick CTRL + ` (the one above tab not the ‘ next to the Enter key). Just enter the command again to turn formulas off.
Use CTRL+TAB instead
You can switch between worksheets by using CTRL + TAB to go to the next worksheet or CTRL + SHIFT + TAB to go back a worksheet.
Other useful shortcuts
We probably all use CTRL + V to paste, but did you know CTRL + ALT + V allows you to quickly paste special? Paste special is great for removing formatting copied from other documents or transposing data.
Turn auto-filter on and off with CTRL + SHIFT + L.
You can tie charts directly to data ranges. If you compile an annual report, creating charts that auto-update can save you a lot of time. Read more about this process in Stephanie Evergreen’s most recent blog post.
By using one or more of these tips above you will become more efficient in conducting your audit work and before no time you will be the next Excel super user in your office.
Ian Green, CGAP and OAD Senior Auditor
Enjoy reading our ‘how to’ posts? Subscribe to the Oregon Audits blog through the ‘Follow’ link in the right hand sidebar and keep tabs on the latest data wonk tips and tricks, audit releases, interviews, and more!