If you don’t have an Excel Auditing tool that will list all the formulas in a spreadsheet, you can use the FIND tool to find all the Excel functions prone to spreadsheet errors and debug them.
In this case you would decide which functions you are worried about e.g. XLOOKUP, VLOOKUP etc and FIND all of them in the spreadsheet. You can then prioritise which ones to look at based on our listing of risky actions in Excel functions (see below).
Table of contents
Which Excel functions to look for
In the free Auditing an Excel Spreadsheet course we have listed all the functions we look at first and what we look for that indicates that it may have a spreadsheet error.
Find all the risky Excel functions
The process in Excel is simple enough. Let us say we want to look for all the XLOOKUP’s in a spreadsheet.
You need to access the FIND tool. We access it via CTRL + F.
As shown below we do the following to find all the XLOOKUP’s:
- As show below, we search for XLOOKUP( . The ( is so that it find functions and not words. Less of an issue with XLOOKUP, but useful when the functions is a ‘normal’ word like LEFT (1).
- We click on the options button (2) which opens up the options with regards to where Excel must look and whether it must look within Formula
- We change the ‘within’ option (3) to look at the whole workbook. You can do it one sheet at a time if you want.
- Note that the ‘Look In’ already says Formulas so we don’t need to change that.
- We click on the ‘Find All’ button
After you click Find All, Excel will search the entire spreadsheet for the word XLOOKUP(.
Work through the risky Excel functions
The below will appear although it will be much smaller. Note that in (1) we have widened the dialogue box so we can see the results. Now in (2) we can look at the found XLOOKUP’s and decide which ones are risky and need to be investigated.
As per our formula risks, XLOOKUP’s should have $ signs on their ranges. The first one does not so we should investigate. To investigate you can just click on the formula and you will jump to that sheet.
So, as per below, when you click on the formula (1), you are taken to the sheet and the cursor jumps to the relevant cell (2).
To investigate a potential error of this sort ($ signs should be on some part of the formula), DON’T look at the first cell in the range. The error is hard to spot if you look at the first cell. Look at the second or third cell down and/or across and use you Formula Auditing tools (blue lines).
You can just click on the next cells and run the Trace Precedents BUT KEEP THE FIND WINDOW OPEN otherwise you will need to rerun it.
So below, note that
- (1) we kept the FIND window open
- (2) clicked in a cell below the one that is worrying us and ran the Trace Precedents tool.
- (3) shows that while the one range seems to be looking at the correct cells (column K), because there was no $ signs, the other range seems to be looking at an incorrect reference and is probably an error.
You can continue to work through each risky function and identify aspects that increase the risk of a spreadsheet error.
Making the spreadsheet review process more efficient
This process works well and you will go through all the risky cells in a spreadsheet. The problem is whether you have enough time to do this.
The alternate is to use an Excel Auditing tool to do the same thing (find all the risky functions) but show them in a unique listing and tell you which are copies which you don’t have to relook at. If spreadsheet errors are a concern for you, you should complete our free Auditing an Excel spreadsheet and consider an addin to find the spreadsheet errors.