Remove unnecessary calculations
How do you determine if you can safely remove irrelevant calculations from your model without consequences? While you can manually investigate dependencies for each formula, PerfectXL simplifies this process. It reveals all dependencies at a glance, allowing you to evaluate the relevance of output and to decide whether formulas can be removed, or whether you need to make other changes first.
- Download the sample file to follow the steps.
- Already working with PerfectXL? Then jump to the solution with PerfectXL.
Step-by-step without PerfectXL
Which calculations are “unnecessary”?
It may be a bit of a vague term, but we classify any calculation that is not used for insight, reporting, or influencing other parts of the model as “unnecessary.” An example might be a sum on a hidden sheet that is not being referenced in the summary because the summary has a sum of its own. Many models are full of calculations that are never referenced or looked at and that only slow down and complicate the model. This makes it harder to work with the spreadsheet (practically because it takes longer to make changes or run calculations) and it also makes the model more challenging to understand for other users.
Step 1: Trace all calculations
In order to clean up your model and remove unnecessary calculations, you need to localize the calculations in your file first. You can do this using the “Show Formulas” option under “Formulas” in Excel’s main navigation bar. The formulas on your worksheet will become visible.
Step 2: Check if the calculation is referenced anywhere
Next you need to determine per calculation whether the cell the calculation is found in is being referenced anywhere in this, or some other model. The “Trace Dependents” feature under “Formulas” in the Excel ribbon will display arrows indicating which cells are affected by the value of the currently selected cell and you can use the hotkey CTRL+] to navigate to the dependents of that cell.
N.B. You won’t see an overview of unused cells, and cells used in things like data validation lists, conditional format rules, or charts will not show any dependents.
Step 3: Determine if the calculation is used anywhere else
As for whether or not the calculation is used in another file or in VBA: hopefully the model contains documentation if a reference to the sheet exists from any alternative source.
Is the result of this calculation being used in a report, being communicated with a client or other party, or is a user of this model utilizing this value in any other way? Unfortunately, this is not a question any technology can answer for you, but this is an important thing to check to make sure that the number isn’t being used in some kind of email correspondence or in some way outside of the model.
Conclusion
Ultimately, if you can determine that a cell is not being used in- or outside of the model, that cell should be removed. Unneeded things in a spreadsheet grow and spread – a bit like a virus. Mess invites more mess, and messy spreadsheets will get worse with every user. Try to keep only relevant/necessary calculations in the file and even consider using a separate spreadsheet when gathering quick insights, that way “helper formulas” don’t start to become incredibly unhelpful!
Step-by-step with PerfectXL
How can I tell whether a calculation is unnecessary?
PerfectXL has two tools that actively help in detecting and removing unnecessary calculations. PerfectXL Explore looks at a model as a whole (sheets full of unnecessary calculations can be removed in one go) and PerfectXL Highlighter looks per cell; how cells are used, and how they connect to the rest of the file.
Step 1: Scan the whole file for unused worksheets
Open PerfectXL Explore and select the file you want to scan. This way you can evaluate the whole file at once and remove any unused worksheets (see “Simplify Models” for more information).
Step 2: Determine if there are unused worksheets
After analyzing your file PerfectXL Explore will display a visualization of your model. It shows you all the worksheets and external sources used in your file. The arrows indicate how these sheets and sources are connected. The visualization of our test-file shows that all sheets and sources are connected, which means that they are all used in one way or another.
Step 3: Check individual formulas
The next thing we’d like to determine is if there are any individual formulas or formula clusters that are unused. Open the file in Excel and select the first worksheet you want to scan for unused formulas. Navigate to the PerfectXL tab in the main navigation bar and select “Input Output cells” under “Highlight Cells.”
Step 4: Determine if there are unnecessary calculations
All “Unused Data” and “Formula Output” that are not directly used as an indicator or part of a report have no impact on the rest of the model (no dependents). This gives you a clear overview of Unnecessary Calculations per worksheet.
Step 5: List of highlighted cells
You can also select “Show list of highlighted cells” to see all cells that have been highlighted, not just those in view (very practical in case of a larger file).
Repeat these steps for all input and calculation sheets. Be extra careful with dashboards and output sheets, as those are often used for reports or are the purpose of the model.
Conclusion
PerfectXL Explore and PerfectXL Highlighter make it possible to clean up your file with the certainty that you are not removing any vital information. This way you can safely remove unnecessary calculations and make sure that your model is not overly complicated or slowed down.