Quick scan for risks and blunders

When there’s a rush to deliver or review a model, there’s not enough time to manually check all data and calculations. Still, you need some level of assurance. With PerfectXL, you can quickly check for major risks, such as broken formula ranges, incomplete references, and whether external sources are used properly. You can extend or simplify this scan as needed.

Quick scan for risks and blunders in Excel // Use cases // PerfectXL

Step-by-step without PerfectXL

Step 1a: Identify risks to check for

When you feel doubtful about calculations made in a model, or when you have multiple models to review, but limited time to asses them it is nice to have something to hold on to. Without PerfectXL, it’s extremely difficult to quickly gain sufficient control over an Excel model to identify core risks. Specifically, you will want to manually check for presence of the following risks:

  • Approximate lookup
  • Calculation Set to Manual
  • Circular Reference
  • Excel error
  • Data Validation Rule not Followed
  • Double Count
  • Double Operator
  • Excel Error
  • Formula Interrupted by Data
  • Formula Interrupted by Other Formula
  • Iterative Calculation Enabled
  • Longer Cell Reference Expected
  • Numeric Text Calculation
  • Reference to Empty Cell
  • Unnecessary Space

Step 1b: Show all formulas

Start with the “Show Formulas” button under “Formulas” in the Excel main menu.

Click button "Show Formulas" in Excel ribbon // PerfectXL

Stap 1c: Visible formulas

When you click this button, the cells that normally contain the result of a formula will now display the formula itself.

Show all formulas on a worksheet // PerfectXL

Step 2: Scan for irregularities

Once the formulas are visible, you can visually scan for irregularities. Repeat this process for each tab.

In the example model with five tabs, cells with formulas are highlighted in yellow for convenience.

Worksheet with visible formulas for manual check // PerfectXL

On the “Summary” tab, we see nine formulas, with four referencing cells from other tabs and the remaining five being simple subtractions. Nothing requiring immediate attention here.

Worksheet 2 with visible formulas show no irregularities // PerfectXL

On the second “Income” tab, we observe a reference to another Excel file in cell C4, four simple extractions in column D, and three additions in row 9. With a naked eye, we can see consistent formula ranges in both column D and row 9.

No irregularities found on worksheet with visible formulas // PerfectXL

Visible formulas on worksheet 4 // PerfectXL

Similarly, on the next two tabs, no irregularities are observed.

On the last tab – “Operating” – however, we notice something that needs our attention. Column D contains a formula that appears to be intended to be extended, but it’s interrupted, as the subtraction =C[ ]-B[ ] appears in all cells except for cell D8, which contains a ‘0’. It seems that we have interrupted the extended formula by entering a hardcoded number. This is likely an error!

Furthermore, in cell D23, there’s another type of interruption. While all other cells in the column follow the pattern “=C[ ]-B[ ]”, cell D23 contains the formula “=C23-B23+30”. Again, a hardcoded number is present, but this time it’s added as a value to the formula.

Extended formula interrupted by hardcoded number // PerfectXL

Step 3: Evaluate and improve findings

You, as the user, need to decide whether this ‘+30’ belongs there or not. In our case, the ‘+30’ represents unforeseen costs, but presenting these costs in this way is unclear. It’s better to add a new entry for unforeseen costs and then extend the formula accordingly.

Now that a row has been added to the table, it’s crucial to check in the last row, “Total operating expenses,” whether the cell range for the sums is still correct (i.e., whether the new row is included in the total). Fortunately, in our example, this has been done correctly.

Improved table: hardcoded numbers are removed from extended formula // PerfectXL

Conclusion

Our example is only a small and very manageable model, but you can imagine that such an investigation in more extensive models is extremely time-consuming. It takes days to go through an Excel file sheet by sheet and cell by cell, and often, you don’t have that time. Additionally, there are various problems that you can’t easily make visible, such as the location of a circular reference, the location of broken external links, hidden elements, or unintended white space.

Step-by-step with PerfectXL

Step 1: Open the model in PerfectXL Explore

Check for:

  • Connections between sheets that you believe are necessary;
  • The correct external sources being called;
  • Whether the charts contain relevant information.

The visualization of our concise exemplary model shows us that both the “Personnel” and “Operating” sheets refer to “Expenses” and that both “Expenses” and “Income” refer to “Summary.”

Furthermore, we see that external source “Invoices.xlsx” is called by the “Income” sheet. This all looks perfectly fine.

Find risks in Excel in a spreadsheet visualization in PerfectXL Explore // PerfectXL

But there is another tab that we didn’t spot using the manual method: “Content”. The grey color reveals that this concerns a hidden worksheet and the fact that there are no arrows that lead to or from this sheet indicates that it doesn’t contain any relevant information for our model. This assumption makes it useful to manually inspect the worksheet.

Click the external link icon in the top right corner of the grey box to jump to the relevant location in your model right away.

A pop-up will appear asking you if you would like to unhide the worksheet first.

Unhide and go to hidden worksheet // PerfectXL

As soon as you click ok, the software will take you to the worksheet, which is then no longer hidden.

The worksheet turns out to be a table of contents of the model, so it is not problematic as long as it stays unhidden (so don’t rehide it).

Reveal a (formerly) hidden worksheet in Excel // PerfectXL

Step 2: Open the model in PerfectXL Risk Finder

Filter at the bottom left on Risk level: “Risk” and go through the risks one by one.

Most of the risks you encounter can directly impact the outcome of the model. However, in some cases, something is a risk but not immediately wrong.

Scan for risks in your Excel model // PerfectXL Risk FinderYou probably want to check findings that have a potential to be a direct error. “Excel errors,” “Longer Cell Reference,” and “Formula interrupted by data” are very relevant in this case.

As you can see only one instance of “Formula interrupted by data” was found in our model: in cell D8 on worksheet “Operating”. If you would like to know more about this instance you can click the case for more details:

Example of a formula interrupted by data found in Excel model // PerfectXL

A fragment of the relevant worksheet with cell D8 highlighted will now appear in the frame. Underneath it you’ll find the description of the issue: “The formula range D4:D22 is interrupted by a value at D8.”

Due to the visual fragment and the description, it is easier to determine that a serious risk was discovered; this formula should have been extended throughout the entire range D4:D22!

Details of a risk found in Excel model and quick fix option // PerfectXL

When you click the visual fragment PerfectXL Risk Finder will take you straight to the exact location of the issue in Excel: cell D8 in tab Operating, so that you can make the necessary adjustment.

Items like “Hard Coded Number in Formula” or “Many operations” are also important, but if you’re really short on time, you can ignore them. They influence the quality of a model but not the concrete outcome of a specific formula.

In the PerfectXL Risk Finder, you’ll find explanations on how to deal with each identified risk. There’s also a manual available (link) with a detailed description of each element.