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 21 types of risk, 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 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 & Improve 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 All Risks & Improve 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 a spreadsheet, without having to check every cell manually.