Tracing Problems in Spreadsheet Calculations
When spreadsheets contain inconsistencies or suspected miscalculations, it takes great effort to find the problem manually. In this video we’ll show you how to use PerfectXL’s Formula Breakdown feature to locate and fix the problems in spreadsheet calculations within a matter of minutes.
Case: discrepancy between two calculated figures
As an accountant, you receive a spreadsheet with the financial administration of employees. Employees are compensated for transport expenses.
In the spreadsheet in question, down at the bottom, we can see a bunch of sheets that calculate all sorts of staff expenses, from basic salaries to health insurance. On the ‘Summary Employee Expenses’ sheet, there are summaries of those expenses. We’re interested in one particular section, which shows two calculated figures: the expected travel compensation, and the actual payout. It turns out, there’s a discrepancy between the two. We want to find out why they don’t match up.
Traditionally, we’d need to rigorously check all relevant formulas to find the problem. Before you know it, you’ll be bouncing between sheets and meticulously scanning for inconsistencies within hundreds of rows. Luckily, PerfectXL offers a much easier solution to find problems in spreadsheet calculations.
Upload your Spreadsheet for Analysis
After analysis of our spreadsheet, we’ll click it and go over to the Formula Breakdown section. The Formula Breakdown allows us to dig down to the sources of a formula, listing potential problems along the way. Exactly what we need. To check the expected travel compensation calculation, we make a note of the worksheet and the cell of the formula and enter that here.
A List of all Cells and Ranges used in this Formula
On top of the page we see the formula in question, listed below are all the cells and ranges used in this formula, including their cell content. When a reference in your formula refers to different kinds of cell content, such as different formulas, you’ll see that the reference gets expanded.
Any cell reference containing a formula we can inspect by clicking it. Clicked formulas always remain visible on top of the page, so that we can see exactly how we’re traversing the spreadsheet.
A Formula Range is Interrupted by a Different Formula
When we see that the current formula is in some way risky, we can click it to see the details. And here is our problem: a formula that is supposed to be consistent throughout its range, is interrupted by a different formula.
List of Indirect Risks
You don’t need to stumble upon a risk like this by chance. If we start again at the original formula, we can also visit the list of indirect risks; which lists all risks detected in underlying formulas. There’s our interrupted formula range. To reveal that same series of linking formulas as before, leading up to the risk: just click it.
Resolve the Problem
Now that we know what and where the problem is, let’s resolve it. If we go over to the Reports section, we can download a copy of our spreadsheet with a custom mapping of the problematic sheet in question.
On the sheet with the mapping, we can easily spot the interrupted range. In our chosen mapping type, similar formulas are surrounded by purple borders, so this exception here is obviously suspicious. Switching to the same sheet in our original spreadsheet allows us to fix the problem. Now, when switching over to the summary sheet the calculations match.
We started off finding an inconsistency in the financial administration. Normally, it would have taken great effort to find the problems in your spreadsheet calculations. All we had to do now was upload the spreadsheet to PerfectXL, and use the Formula Breakdown to tell us what the problem was. Using a simple export, we could locate and fix the problem. What would’ve usually been an exhaustive struggle to find out, could now be accomplished in a matter of minutes.
More Video Tutorials
How to Check Your Input Sources in Excel
In this video we’ll show you how to run a quick basic check of the input sources of your spreadsheet and how to determine if there is any unknown input. We’ll also show you a powerful way to visualize your spreadsheet’s information flow to help determine the function and dependability of each worksheet.Learn how to check your input sources in Excel
How to Quickly Validate your Spreadsheet
88% of spreadsheets contain hidden issues. These issues can be a serious risk to the outcome of your spreadsheet. This video shows you how PerfectXL helps you to quickly validate your spreadsheet on 38 types of risk, without having to check every cell manually.Learn how to validate your spreadsheet