Ten Excel Tools Every Auditor Should Use

It seems like half of what auditors do is cross-reference workpapers back and forth.  For references within a sheet or between sheets, it’s really helpful to provide a link between them.  It helps you navigate, as well as your reviewers.  Check out the video screenshot below to see how you can use formatted, hyperlinked textboxes between two references.

Maybe you don’t need links between cross-references, but you still want to format them so they stick out.  This next tool helps you do that in one step using a shortcut.  Check out the video screenshot below for a peek.

Tickmarks are a crucial part of the audit process.  They efficiently indicate the documentation and review work done by the audit team.  The accounting firm I worked for had some custom buttons in the Excel toolbar, which were nice, but it took several clicks to insert them.  In the video screenshot, notice how you can define your own tickmarks (text, symbols, images, and formatting) and insert them without using the mouse!

When I was an auditor, the files we received usually had a LOT of sheets (like 20+).  Much of it was unnecessary years of history, but whatever the reason – it took a lot of time and effort to navigate.  I use a tool that instantly adds a hyperlinked Table of Contents, which made navigation so much easier.  Over the years, I’ve built several other sheet-related tools that help with navigation and sheet management, all without having to use the mouse!  Take a look at the TOC output in the screenshot below.

You’d be amazed how many things clients would put in hidden sheets, columns, and rows. Most of the time they were hidden to reduce the clutter, but sometimes it was stuff you probably wouldn’t want the auditors seeing . And if you leave that stuff hidden and a reviewer finds it? That’s a great way to get your hand slapped!

To instantly find all the hidden columns and rows in a file, I use a tool that will list them all out to quickly review them. You can then use that list to easily toggle them between hidden and visible!

The trick with hidden cells and sheets is that once you unhide them, it’s difficult (if not impossible) to hide them again, unless you’re keeping track of them. But this tool does that for you, making it a breeze to toggle them! Check it out in the screenshot video below.

I try to minimize hard-coded values as much as I can, but clients didn’t always take that approach. It was common to find hard-coded numbers in the middle of a sea of formulas, and sometimes within them (i.e. SUM(A2:A50)+1). That presents a lot of risk – undocumented amounts, plug figures, and flat-out errors. Reviewing every single cell/formula in a workbook doesn’t really make sense, but you can easily highlight the ones that might present issues. I use a couple of formula-based conditional formats that can highlight hard-coded values and formulas that contain hard-coded parts. It can easily be toggled on and off and reviewed in a pinch. Check out the output and the conditional format formula in the screenshot below.

Tracking review comments – especially when they are spread all throughout a file – can be quite tricky. While the commenting system in Excel has evolved to work as true threaded comments, it’s still helpful to be able to track them in one central place. I use several handy comment shortcuts, but the one that helps the most adds a central comment tracking sheet. It’s quite helpful for tracking the status, who is assigned, responses, and includes hyperlinks to the cells triggering the comments. Check out the output in the screenshot below.

Formulas can be tricky to trace and audit. The more cell references, the more time it takes to navigate and review them. If you haven’t used it yet, the Ctrl+[ shortcut is super-helpful for jumping to a formula cell reference, but it only works with the first reference in the formula.

To help understand which cell ranges are referenced in a formula and jump back and forth between them, I use a helpful search box that shows them all – the cell range, the sheet, and the file. Check out the search box in action in the screenshot below.

We all work with a lot of Excel files, and we spend an surprisingly large amount of time searching for them. It’s easy to forget where we save files and even what we name them.

To minimize that time, I use a file search box that shows results as you type (much like Google). I can usually find and open a file within just a couple of seconds this way, and that time adds up really fast! Before I started taking this approach, I remember searching for so long that I ended up just recreating the file a couple times, and nobody needs that! Check out the search box in action in the screenshot below.

The more formulas you work with, the more you probably run into formula errors. Even if the formulas are built correctly, often changes in the source data or references later on can create errors. To locate them quickly, I use a tool that lists out every formula error within the workbook, and a hyperlink to navigate to them for troubleshooting. Check out the output in the screenshot below.