Auditors and other accountants were among the earliest and most enthusiastic adopters of the first electronic spreadsheets – when VisiCalc was introduced in 1979, then joined by Microsoft Excel in 1985 – despite some concerns that the profession would find itself replaced or diminished by such technological wonders.

Fast forward to 2022 and both the profession and the spreadsheet are thriving. There have never been more spreadsheet products to choose from – Google Sheets and Zoho Sheet to name just two – and the flexibility of the ‘general purpose’ spreadsheet makes it the go-to option for many professional tasks, despite a plethora of more specialised software applications.

For many in the profession, that go-to spreadsheet option is Excel, not least because of its familiarity and accessibility as part of Microsoft 365 (which some may know better as Office 365). Given the ready availability of Microsoft productivity tools including Excel, some audit firms are keen to get more from Excel and the rich functionality it can offer – particularly when it comes to data analysis. So, this article will offer some tips and insights on functions that can assist auditors with this.

There is, of course, a multitude of functionalities in Excel that can be used to aid efficiency in an audit – whether that is by understanding client data, performing basic data analytics or even figuring out prior year workpapers.

Formula auditing

One of the tricks I have found useful when trying to interpret client or my colleague’s workpapers is to use the formula auditing toolbar. This little-known toolbar can very easily be used to show formulas on a sheet (without having to click in each cell), to work through formulas using Evaluate Formula or to find links between values using the tracing functions. This can save time trying to work through others’ formulas – the Evaluate Formula function, in particular, is useful if a number is not what you expect and you want to understand where you have gone wrong in a calculation.

This article will not provide step-by-step guidance on how, as there are many online resources available that do this, specialist training providers that can assist and numerous useful resources available through ICAEW’s Excel Community. It provides access to tips on using Excel, including many of the functions mentioned here. It also offers useful guides such as How to review a spreadsheet and Twenty spreadsheet principles to reduce errors and wasted time, as highlighted in a previous edition of Audit & Beyond. You can explore what’s on offer at the Excel Communuity.

Data Analysis ToolPak

One of the really helpful add-ins you can put on Excel is the Data Analysis ToolPak. There are very many incredibly useful tools built into it, including (but not limited to):

• Descriptive Statistics;
• Sampling;
• Regression Analysis; and
• Correlation Calculations.

Descriptive Statistics: I am sure we are all familiar with using functions in Excel to calculate the mean of a set of data, how many values there are, the range of data and variance in a sample. However, these can all be calculated using one button within the ToolPak – Descriptive Statistics

Other useful figures in Descriptive Statistics include the kurtosis and skewness of the values, which indicate the shape of the values if you were to plot them on a chart.

Sampling: It is difficult to manage truly random sampling within a population without the use of technology. Humans will always have a bias towards one part of the data set or one type of transaction. The Data Analysis ToolPak has a sampling tool where you can select either a random number of points within a data set, that Excel will then select for you, or for periodic sampling.

Regression and correlation: Regression is useful when looking towards the future – perhaps as part of the going concern assessment or when projecting for the full year at half-year. The Data Analysis ToolPak allows you to use relationships between variables to perform some simple linear regression analysis. Similarly, correlations can be identified – either to prove links between figures or to help identify links between them.
Users must be aware, however, that correlation does not always mean causation. For example, the revenues may have increased from both customers A and B over the year, so they are correlated, but customer A did not cause customer B’s revenue to rise, meaning they have no causation.

Get & Transform

A simple yet powerful tool that can be used to import data sets and perform some introductory data cleansing and analytics is the Get & Transform tool. This uses Power Query Editor, which is now the default option for linking Excel to all external sources of data.

The Get & Transform tool allows you to get data from multiple sources (text, CSV, PDF, Excel, online, Microsoft Azure and various other database systems) and import that into Excel, where data sets can then be merged or appended together. From here, you can cleanse and analyse the data, including adding calculations, extracting parts of the data and getting some initial statistical analysis on a data set. This can then be used in Excel or in Power BI (another Microsoft 365 tool).

Useful functions

One of the functions most commonly used by auditors is a lookup. There are multiple lookup functions – LOOKUP, XLOOKUP, HLOOKUP and VLOOKUP. Put simply, these functions look for a value (whether that be text or a number) and return a corresponding value from elsewhere in an array. This could be the invoice amount based on an invoice number or, if you have a sample list, finding the corresponding details in a client spreadsheet. Be warned, however, that HLOOKUP and VLOOKUP are known to generate incorrect results when not used properly.

Other tools that you may find useful include CHOOSE and SWITCH, which are among the many functions you can learn more about through ICAEW’s ‘Excel Tip of the Week’ index.

Validation circles

One of the lesser-known tools in Excel is data validation. You can create rules within Excel that only allow certain values to be input – which is useful as a simple internal control for data input. Similarly, if you receive client data and you want to check whether the client has followed its own internal controls, you can set it to circle any data that does not meet that rule, for example, an invoice approved by someone above their threshold, or where a calculated amount does not sit within expected boundaries

As only the first 255 transgressions are circled, let’s not forget ‘conditional formatting’. It may be more suitable where more items are concerned, can easily highlight high-value transactions and conditional colour scales can show a spread of risk within a set of transactions

My main consideration when using Excel is to make it as visual, clear and usable as possible. Humans are visual creatures; we are not designed to read huge data sets and interpret them easily. The best Excel tools are those that will aid your auditing, improve efficiencies and do so in a manner that is repeatable every year. There is no point spending hours making an Excel workbook or dashboard that is not then usable on other clients or in other years

All of the tools mentioned so far are useful and can easily make your auditing using Excel more efficient. This is before we consider the recent but significant ‘dynamic array’ feature or the always-useful PivotTable, each of which warrant their own article

Excel is an extremely useful tool and one that is often overlooked in favour of more ‘sophisticated’ and modern tools, but for an auditor, a good set of Excel skills will get you far.