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.

Vulnerable Functions – Risks

Vulnerable functions are 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.

Vulnerable Functions :: Risk Detection :: PerfectXL

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 :: Vulnerable Functions :: Risk Detection :: PerfectXL

Deprecated Function

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 will suggest this equivalent.

Deprecated Functions :: Vulnerable Functions :: Risk Detection :: PerfectXL

Recent Function

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 transferrable. Don’t use recent functions when you are sharing the workbook with users that have older Excel versions (and if you are not sure, just don’t take the risk).

Recent Functions :: Vulnerable Functions :: Risk Detection :: PerfectXL

Volatile Function

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 :: Vulnerable Functions :: Risk Detection :: PerfectXL

Try PerfectXL FREE

 

Other Types of Risk

Calculation Doubts Structure Issues Range Issues Hidden Info Complex Formulas VBA Issues

 

Relevant Guidelines

Every other week we publish a new Excel Guideline for Professionals, to help you get the best out of your spreadsheets.

Why we prefer INDEX and MATCH over VLOOKUP