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. At the time this video was made, PerfectXL checked for 29 different types of risks, now we check for more than 70, making it possible to secure and validate your spreadsheet without having to check every cell manually.
Case: preclude abnormalities before passing the spreadsheet to a client
A colleague hands you a spreadsheet that you are to pass on to a client. Before you do, you want to do a quick scan and validate your spreadsheet to make sure there are no abnormalities in it.
A spreadsheet can be easily validated in PerfectXL. We upload the spreadsheet, and, after analysis take a quick glance at the visualization. The visualization shows how the worksheets in our spreadsheet are interconnected. Orange indicates an external source: a worksheet in another spreadsheet. A light-coloured sheet means that it is hidden in Excel. In this instance, it might strike us as odd that mostly conclusive sheets, with no outbound arrows, are the ones that tend to be hidden. Were these temporarily hidden? Shouldn’t they be made visible before sending the spreadsheet to the client?
Risks Detected During the Spreadsheet Analysis
Another quick validation check that we can do is to have a look on the Risks & Suggestions page. Presented is a list of all risks as detected during PerfectXL’s analysis of the spreadsheet. This one catches our eye in particular: an unexpected range.
Digging a little deeper on the Risks & Suggestions page, we look for details on the unexpected range. The pop up tells us more. An unexpected range E55:E58 was found in the formula at cell E60. Consider adding E59 to the specified range.
Using the worksheet and cell information, we could find it by hand in our Excel file, but there’s an easier way.
On the Reports section, we can download a copy of our original spreadsheet.
We’ll choose to add a worksheet containing a list of all risks.
Once opened in Excel, on the added sheet we can filter at the top to find our unexpected range. By clicking the cell reference in the location column, we can jump straight to the problem.
Here it is. What was the message again? An unexpected range E55:E58 was found in the formula at cell E60. Consider adding E59. That seems to be correct: the range clearly extends down to E59. Good that we caught this before we sent it to the client.
Sometimes, even spreadsheets that seem to work flawlessly on the surface, have hidden issues, potentially detrimental to the spreadsheet’s purpose. An unexpected range reference is only one example. PerfectXL can help you quickly validate your spreadsheet, without having to check every cell manually.
More Video Tutorials
Tracing Problems in 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.Learn how to trace problems in spreadsheet calculations
How to Check Your Input Sources in Excel
In this video we’ll show you how to run a quick basic check of input sources 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