Do you know how to check formulas in Excel?
Microsoft Excel has many functions and tools that can be used to create efficiency, carry out swift analysis, and manage complex data sets.
One way that accountants and auditors can utilise Excel functionality to create efficiencies is to review formulas entered into Excel by other people. Sometimes, a formula can throw up an error that doesn’t make sense at first glance, and this can become confusing if the formula has lots of inputs or lots of conditions.
Excel has some helpful functions to help find such errors, found in the Formulas menu.
(1) Trace precedents – this function shows which cells are inputs into the formula.
For the cell you wish to see the inputs for (E3 below), click on Trace Precedents and Excel puts a dot on all the cells that contribute to the cell selected (C3 and D3 in the example below).
(2) Trace dependents – this function shows which cells are affected by the formula.
To see all the cells that are affected by a particular cell (C3 below), click on Trace Dependents and Excel puts a dot on all the cells affected.
(3) Remove arrows – this function simply removes arrows created by trace precedents and trace dependents.
(4) Show formulas – this function switches cells to show the formula rather than the output.
(5) Error checking – this function shows cells which contain errors to review.
For example in the scenario below, when dealing with a large data set, the total row is not adding up due to an error somewhere in the data set.
An easy way to locate the errors is to click on Error Checking to bring up a dialog box with all the errors that can be worked through one-by-one.
(6) Evaluate formula – this function works though the formula to show the individual steps of the argument.
Formula evaluation allows users to break down and understand different parts of a formula (step-by-step) to identify the problem more systematically.
For further hints and tips on using Microsoft Excel more efficiently for audit and accounting needs, see the Excel for accountants and auditors — Part 1 Navigate learning Insight course.
Answer provided by Anand Singh ACA .
Document downloaded on 11-04-2025 from Croner-i Navigate, the UK’s leading online research service for tax, audit and accounting professionals. Find out more at www.croneri.co.uk or call 0800 231 5199.
This article was correct at the date of publication. It is intended as an aid and cannot be expected to replace specific professional advice and judgment. No liability for errors or omissions will be accepted. It is the responsibility of those using the information to ensure it complies with the law at the time of use and that it is used in line with relevant rules and regulations governing the subject matter in question.
Except where otherwise indicated, all content is copyright of Croner-i Ltd.
© Croner-i Ltd, 2025
All rights reserved. No part of this publication may be reproduced without prior permission
