Balance or Scale // PerfectXL Spreadsheet Validation

Excel Risk Inspections

Vulnerable Functions

Currently Excel offers about 475 standard functions and new ones are added regularly. Most of these functions are rock-solid, but a couple of them we consider vulnerable functions. Some of which are frequently used by many Excel professionals. We suggest you try to avoid using them. Excel offers a range of fine alternatives.

Risks of vulnerable functions

Vulnerable functions are Excel functions that are unreliable in one way or another. They slow down your spreadsheet, aren’t updated automatically or may corrupt your spreadsheet as time passes or when it is transferred to other users with different versions of Excel.

Risks of vulnerable functions in Excel // PerfectXL Risk Eliminator

Numeric Index Reference

Functions that accept a number as (part of a) cell reference are risky, because Excel doesn’t update them when columns/rows are inserted or deleted. For example in a VLOOKUP function: when columns are inserted or removed, failing to update the column reference argument will typically yield incorrect results. This is why PerfectXL marks them as vulnerable functions.

Numeric Index Reference // Risks of Vulnerable Functions in Excel // PerfectXL Risk Eliminator

Deprecated Functions

Deprecated may indicate that the feature will be removed in the future. Certain functions in Excel are supported for backwards-compatibility, but should no longer be used. Try to replace the function with the new equivalent function. PerfectXL Risk Eliminator will suggest this equivalent.

Deprecated Functions // The Risks of Vulnerable Functions in Excel // PerfectXL Risk Eliminator

Recent Functions

Recent function indicates that the function will replace a previous function. Using functions that are only supported in the latest few versions of Excel make the spreadsheet less transferable. Don’t use recent functions when you are sharing the workbook with users that have older versions of Excel (and if you are not sure, just don’t take the risk).

Recent Functions // The Risks of Vulnerable Functions in Excel // PerfectXL Risk Eliminator

Volatile Functions

A volatile function is recalculating the formula every time a change in the spreadsheet is made. This results in reduced performance and makes the spreadsheet slow. Check if the volatile function exists intentional or by accident.

Volatile Functions // The Risks of Vulnerable Functions in Excel // PerfectXL Risk Eliminator

Risk inspection categories

Below you’ll find an overview of the risk categories that PerfectXL Risk Eliminator detects. Click a category to read more.

Hiding “unimportant information” from other users may seem like a good idea to make complex spreadsheets more user friendly. But in reality that’s not the case. Hidden information can be altered by a user, without him even realizing what he’s doing, resulting in potential negative results.

Excel does what you ask for, but are you asking the right question? We see many formulas that give us reason to doubt that. For example, when hard coded numbers or double operators are used. This category of risks we call ‘calculation doubts’.

Larger spreadsheets are usually stuffed with formulas based on cell ranges. When spreadsheets are edited or expanded over time, different types of range issues may occur. These issues are usually hard to detect, but they can be very risky! We’ve seen big numbers and large sums of money ‘disappear’ because of range issues.

Currently Excel offers about 475 standard functions and new ones are added regularly. Most of these functions are rock-solid, but a couple of them we consider vulnerable functions. Some of which are frequently used by many Excel professionals. We suggest you try to avoid using them. Excel offers a range of fine alternatives.

The creation of a good structure is one of the most important, yet most underestimated aspects in the development of a spreadsheet. Usually a spreadsheet starts small, but then it grows and nobody gets around to reorganizing the structure anymore. PerfectXL Risk Eliminator groups the risks resulting from ill structure under ‘structure issues’.

The art of programming is the art of keeping things simple. Complex formulas are hardly ever wise to use. Calculations can usually be simplified. You just have to figure out how. PerfectXL points out different types of complex formulas in your spreadsheet and it offers suggestions to improve the complexity.

Excel is a powerful tool. And when it can’t do what you need, VBA can fill the gap. Unfortunately, using VBA can lead to unpredictable behaviour, slow performance, and corrupt Excel files. PerfectXL checks your macro-riddled spreadsheets for potential problems in the VBA code.