Top 10 Excel Risk Tools

Overview

Over 90% of Excel files have significant errors.  It’s a stat I hear frequently in the software space.  But it doesn’t have to be that way for your files.  Excel comes packed with so many features that people don’t take advantage of, and many of them are great at mitigating risk.

In this article, we’ll look at the top 10 tools for managing risk in Excel.  We’ll attack risk from a variety of angles, starting with the beginning of the process, and running through how you can review results.  I’ll also share a few custom tools that have helped me apply these risk tools as quickly as possible.  Make sure to use the box below to download the example workbook showing all these approaches in action!

The 10 Tools

#1 – Data Validation

One of the best ways to eliminate errors is to attack them at the beginning of the process – when you capture data.  Try to minimize inputs where you can – after all, the data probably already exists somewhere.  For inputs you do need to capture – for dates, names, identifiers, etc. – use data validation.

Excel offers a variety of data validation types, including text/number/date ranges, drop-down lists, and custom validations using formulas.  You can also set a custom input message (as you can see in the screenshot below) and/or a custom error message when the validation requirements aren’t met.

To apply data validation, go to the Data ribbon tab and click Data Validation.  Pick the type and configure the settings.  Optionally, go to the Input Message and/or Error Message tabs to configure those to guide your users.  Learn more about data validation here and some custom data validation tricks here.

Here’s a screenshot of data validation in action, along with the dialog box.

#2 – Named Ranges

Most errors happen within formulas, and a big reason is because they can quickly get complex, especially with a lot of cell references.  Named Ranges allow you to give descriptive names to cells, constant values, and even formula results.

To set named ranges, go to the Formulas ribbon tab and click the Name Manager.  From there, you can add a new named range by giving it a name (must start with a letter and only contain letters, numbers, or an underscore character), a reference (a constant value, a cell range, or a formula), and optionally, a comment describing the named range.  You can also select a range and enter a name into the name box (to the left of the formula bar).

Key tip: use consistent and specific names for your named ranges so you and others can easily understand what they mean!

Learn more about named ranges here and some extra named range tricks here.

Here’s an example of a named range definition in the Name Manager dialog box in the screenshot below.

#3 – Conditional Formatting

So far we’ve focused on preventing errors.  Conditional formatting helps you quickly identify them.  Outliers, exceptions, or specific values/ranges of values are common items you might want to highlight, and conditional formatting provides several different approaches to do just that.

To apply conditional formatting, select the cells to apply to and from the Home ribbon tab, go to Conditional Formatting.  Choose one of the built-in options on the quick selection list, or go to Manage Rules for more options.  Then select the conditional formatting type (i.e. color scales, data bars, icons, top/bottom values, custom, etc.), and configure the settings – both the formatting you want to apply, and any values or formulas to leverage for them.

Learn more about conditional formatting here or grab a comprehensive template here.

Here’s an example of applying conditional formatting (specifically the color scale type) with the Conditional Formatting Rules Manager in the screenshot below.

#4 – Dynamic Array Functions

One of the newer items on this list is Dynamic Array functions (aka DAFs).  They let you build formulas that spill down and/or across from just one formula.  That allows you to plan for growth (or contraction) in the number of rows you’re referencing or displaying, mitigating one of the biggest risks in Excel files: formula/reference completeness.

Before DAFs, you’d need to manually fill formulas down to the last row or sometimes more effort.  Maybe a macro could automate some of it.  But now DAFs can handle data size/structure changes automatically!

There are 30 or so dedicated DAF functions in Excel at the time this article was written.  Most legacy functions can act like DAFs if you supply an array/range to the parameters instead of a single value/cell.  The best way to learn them is to try them out!

Learn more about DAFs here or grab a template with examples of all of them in a comprehensive guide here.

Below is a screenshot example of using the SEQUENCE function to spill an array of counting numbers.

#5 – The LAMBDA and LET Functions

Released at the same time as DAFs were two specific functions: LAMBDA and LET.  By themselves, they don’t really do much.  But combined with other functions, they offer power and control.

LAMBDA lets you build your own custom functions – complete with required/optional parameters – without needing VBA or macros.  This helps simplify and share complex formulas while making them easy to maintain in one place.  To define your own custom function using LAMBDA, go to the Formulas ribbon tab and click the Name Manager.  Give it a name, a definition that starts with “=LAMBDA(” and then supply the parameters and calculations.

LET allows you to name certain parts of your formula so you can reuse them and avoid redundancy.  This makes formulas easier to read and maintain, and can also speed up calculations.  Note that LET is often used within LAMBDA to make powerful and efficient custom functions.

Key tip: Keep a sharable library of your LAMBDA custom functions that you can reuse across your workbooks (see the macros section below for how I do this super-quickly!).

Learn more about the LAMBDA function here, the LET function here, or grab a LAMBDA library template with 80+ custom functions you can use here.

Below is a screenshot example of using a custom LAMBDA function.

#6 – Tie-outs and Check Figures

The more sheets and data your Excel files have, the more you’ll benefit from tie-outs and check figures.  This is merely the process of ensuring your figures agree consistently throughout your files.  There might be a consolidated sheet and then a breakdown of what you’re consolidating.  It could be that you display cash on a cash flow statement then again on a balance sheet.

Whenever figures should be in agreement, add a comparison and apply conditional formatting such that any mismatches clearly stick out.  I then recommend having a central place where all of those check figures are referenced, so you can see them all in one place – a quick and easy review you can do as a last check.

Below is a screenshot example of using a central tie-out sheet with colored formulas for when all the figures tie-out correctly.

#7 – Formula Auditing Tools

Formulas are the backbone of Excel, and as we’ve seen from other risk tools, the source of many errors.  To understand how formulas are working, there are several useful formula tools.  Among the most helpful are trace precedents/dependents arrows and show formulas.

The arrows show cells that have references between them.  Click the appropriate button on the Formulas ribbon tab.  Show formulas will toggle displaying the resulting cell values and the underlying formulas so that it’s easy to compare them.  Click the Show Formulas button on the Formulas ribbon tab to toggle between the views.  If you just want to display one or a few formulas instead of all of them, the FORMULATEXT function works great for this.

Below is a screenshot example that shows some formula arrows and the show formulas option applied.

#8 – Protection

Protection cells (and other worksheet components) from changing is way underused.  It’s so easy to make unwanted changes in Excel, and that’s a big reason that so many software solutions exist today!  I find that it’s often helpful to lock all cells other than specific inputs to prevent those unwanted changes.

By default, all cells are actually in a locked state.  But that attribute is ignored unless the sheet is protected.  With the sheet unprotected (the default), right-click any cells you want to unlock, and go to format cells.  Go to the protection tab and uncheck the “locked” checkbox.  When you’re ready to lock the sheet, go to the Review ribbon tab and click the Protect Sheet button.  You can optionally enter a password when you protect it.  Click it again to unprotect the sheet.

Note that this is by no means a bulletproof solution – it’s easy for users to bypass with little effort.  If you have a file or data that really should be confidential, consider other security access methods.

Below is a screenshot example that shows sheet protection in action when you try to change a locked cell in a protected sheet.

#9 – Power Query, Macros, and Office Scripts

Generally speaking, when you use automation designed correctly, you’re adding control to the process because you’re removing error-prone manual steps.  That’s where tools like Power Query, macros, and Office Scripts come in.

Of all of these, Power Query generally provides the most bang for the buck because it’s a purpose built tool to get data from a variety of sources, enhance that data, and load it into Excel where you can use that data for formulas, PivotTables, charts, and other analysis.  It has a great audit trail because it shows you the raw data and what the data looks like after every enhancement.  It can generally replace about 70% of the manual, repetitive steps that Excel users tend to perform.

Power Query is found in the Data ribbon tab under the Get & Transform Data section (see the screenshot below).  When setting up the data sources and steps, it will launch a distinct editor and build the query for you behind the scenes!

Macros and scripts can then do just about any other automation you can think of – micro automations like combining two steps into one, or bulk automations that can apply several steps for thousands of looped items.

When you identify a risk point that is the result of error-prone manual steps, consider using one of these automation approaches to both save time and reduce risk.

I built the XLEV8 Excel Add-in to provide users with hundreds of these automations ready to use for a variety of tasks.  It generally saves Excel users 200+ hours a year with a combination of shortcuts and bulk automations.  Learn more or see it in action here!

Below is a screenshot example of a data table sourced from Power Query.

#10 – Copilot

Unless you’ve been living under a rock, you probably haven’t gone a day without hearing about generative AI tools like Copilot.  They are extremely powerful and they democratize all kinds of Excel tools normally regarded as expert-level.

From the risk management perspective, they can help you in several ways:

• Better understanding functions and tools
• Building items like custom functions and macros
• Building out the starting structure of your workbook
• Analyzing data for trends and insights
• Finding potential errors or other issues

The first three bullets are in the risk prevention area, while the last two are in the risk detection area.  That last bullet is the big one where Copilot and other tools can provide a ton of value.  If you use a high-quality prompt, you can quickly surface a variety of errors worth fixing.

Depending on your Excel/Office license, you may or may not have access to Copilot directly within Excel.  You can also use other AI tools like Claude, ChatGPT, Gemini, etc. by providing the Excel file as an attachment and asking for insights and analysis.

Below is a screenshot example of using Copilot directly within an Excel file and the prompt to apply that will provide an audit log of potential Excel errors.

Macros that help

I’ve built dozens of macros to help address the risk of Excel file errors quickly and easily.  It leaves you with no excuse not to leverage the powerful tools we’ve covered in this article.  Below are just some of the many tools available in the XLEV8 Excel Add-in.  Check these out in the support pages referenced below, or see them in action in the video below:

• Data Validation Picker – this allows you to quickly apply common data validation types and your favorite validation settings with just a couple of keystrokes.  You can also apply data validation in one bulk step!
• Named Range Picker – this allows you to quickly add, adjust, copy, etc. named ranges using a picklist menu.  You can also add, edit, and remove named ranges in one bulk step!
• Conditional Formatting Picker – this allows you to quickly apply native and custom conditional formatting types using a picklist menu.  You can also apply conditional formatting across multiple sheets/ranges in one bulk step!
• Search Formulas – this custom search box allows you to save and search through your favorite formulas using an auto-complete search box, saving a lot of time in reusing formulas.
• Search Lambda Formulas – this custom search box lets you search for and inject LAMBDA functions from your central library with just a couple of keystrokes.  You can also 
• Global Search – this custom search box also lets you search for and inject LAMBDA functions from your central library with just a couple of keystrokes.  It also surfaces other items – recent files, sheets, named ranges, macros, and keyboard shortcuts.
• Letify Formula – this helps you automatically apply the LET function to existing formulas by wrapping them within the LET function and naming repetitive formula parts.

Video

Summary

As you can see, Excel offers more risk mitigation tools than ever, and chances are that you haven’t heard of or used some of them.  And even if you have, you can probably use them more.  It takes effort and a proactive approach to manage risk, but speaking from the perspective of a former auditor (and I’ll probably always be a heavy Excel user!), that effort is worth it.

To leverage any or all of these Excel risk mitigation tools, make sure to download the example workbook using the box above.  There are plenty of items you can learn from or easily copy and paste to your key workbooks.

Are there other approaches or tools you take to minimize the risk of errors in your Excel files?  Let us know in the comments below!