Excel Errors
Excel errors can arise for various reasons. Sometimes they are expected; when a lookup function can’t find a match for instance, but all too often they indicate something’s gone horribly wrong. All the more reason to inspect them!
#NAME?
#NAME? errors indicate unrecognised Excel functions and/or names. Possible causes include:
- The formula uses a recently introduced Excel function, but the spreadsheet was opened in an older version of Excel that doesn’t support this function. In this case Excel prefixes the name with “xlfn”.
- A function name was misspelled, e.g. “CIELING” instead of “CEILING”.
- A User Defined Function is not recognised because the VBA was modified or the spreadsheet was resaved as a file type that doesn’t include VBA (commonly from .xlsm to .xlsx).
- A function is dependent on some plug-in, such as a corporate Excel add-in that is only available on organization workstations.
#DIV/0!
#DIV/0! errors result from divisions by zero. Possible causes include:
- The denominator is zero.
- The denominator is a reference to a cell whose value is zero.
- The denominator is a reference to an empty cell.
- The denominator is a calculation that resolves to zero.
#REF!
#REF! errors result from problems with cell references. Possible causes include:
- The formula refers to cells that were deleted, possibly because of a removed row or column those cells were part of.
- A numeric index reference was out of bounds. Take a VLOOKUP-function: the third argument specifies which column to take from the cell range specified in the second argument. If you try to point to column 4 in a cell range that is only 3 columns wide, you get a #REF! error.
- A reference may be incorrectly constructed in an INDIRECT-function.
- A formula in row 1 tries to access the row above it, or a formula in column A tries to access the column to its left. This can happen when a formula has been extended up or to the left, or when formulas were inserted by a VBA script.
For other, more exotic situations that yield #REF! errors, such as issues with DDE (Dynamic Data Exchange) or OLE (Object Linking and Embedding), please refer to Microsoft Support for in-depth help.
#N/A
#N/A errors indicate that a value couldn’t be found in a specified cell range. This results from lookup functions such as MATCH, XLOOKUP, VLOOKUP, HLOOKUP, and LOOKUP when no match was found. When such results are to be expected, it is common to wrap the formula in an IFNA- or IFERROR-function to provide a default value.
#NULL!
#NULL! errors result from problems with the range operator or its operands. When you enter two cell ranges separated by a space (the “intersect operator”), Excel takes the overlap between the provided cell ranges.
Common causes of #NULL! errors are:
- There is no overlap between the provided cell ranges; they don’t intersect.
- Use of the intersect operator was accidental; a comma (,) was intended to separate the cell references.
- Between single cell references, it’s possible a colon (:) was intended to make a range (“A1:A9” instead of “A1 A9”).
#NUM!
#NUM! errors are numeric errors. Possible causes include:
- A number was too large (less than -10^307 or greater than 10^307). This is also true for calculated number values.
- A number was entered in an argument section with a prefix or unit. For example, you cannot enter $1000 (one thousand dollars) in a SUM function because the dollar sign is reserved for absolute row references. Always enter plain numbers.
- A number was entered in an argument section with thousands separators. To enter a million, simply type `1000000`, not `1,000,000`.
- A workbook using iterative calculation couldn’t resolve to the desired precision. Either decrease “Maximum Change” or increase “Maximum Iterations” in Excel’s formula options.
#VALUE!
#VALUE! errors are the most generic errors in Excel. A lot of situations can give rise to #VALUE! errors, but here are some possible causes and solutions:
- Empty cells are commonly treated as 0, but cells with a space character (which look empty) will often result in a #VALUE! error. The same applies to leading and trailing whitespace. A quick way to find cells with spaces is to turn on AutoFilter; “(Blanks)” will appear separate from cells containing whitespace.
- Text cells in number calculations can also cause #VALUE! errors. To quickly find text cells among a list of numbers, look at the alignment: left-aligned = text, right-aligned = numeric. Dealing with formatted cells? Not to worry: you can also use the ISTEXT-function to determine if a cell contains text.
- If #VALUE! errors result from calculations with dates, it’s usually because the dates are stored as text. Make sure cells with date values are properly formatted, are entered without leading spaces and in accordance with Excel’s date settings. If Excel follows your computer’s date settings, make sure to check those as well. Pay special attention to the day/month order.
- If #VALUE! errors result from simple subtraction, change the List separator in Windows’ Region-settings from a minus sign to something else (a comma is most common).
- Make sure data connections are active and available. In some cases, unreachable external sources can end in #VALUE! errors too.
#SPILL!
#SPILL! errors occur when a formula returns multiple results but there is not enough space on the cell grid to display all values. The error first occurs where non-empty cells are encountered, which isn’t necessarily the cell where the formula was entered (it usually isn’t).
Make sure to leave enough room for formulas that are expected to return multiple results. In the case of formulas with variable-length outcomes it is wise to restrict worksheets to at most one table. This allows tables to grow without risking #SPILL! errors.
#CALC!
#CALC! errors occur when Excel’s calculation engine encounters a scenario it does not currently support. For example:
- An array (list of values) was empty. Empty arrays are not supported in Excel. So if your FILTER-function returns no results, it displays #CALC!. Expecting the result to be empty every now and then? Wrap your formula in IFERROR to provide a default value.
- A LAMBDA-function was entered into a cell. LAMBDA-functions wrap formulas that take arguments. They are typically meant to be entered into a Defined Name, since the result of a LAMBDA-function is itself a function, which means there’s no value to display in a cell. To use LAMBDA in a cell directly (for testing purposes, say) you must use an extra pair of parentheses to call it instantly.
- Asynchronous functions (usually functions that retrieve something from the web) that fail for any reason (e.g. when the connection was lost) end in a #CALC! error. Recalculating all formulas (F9) sometimes fixes this.
- Excel for the web only allows custom functions to refer to at most 10,000 cells. Referring to more cells results in a #CALC! error.
By default all errors are considered a risk. However, errors are seen as weakness when:
- the error occurs as a literal value in the cell
- the error is inside a conditional format rule
How to avoid?
No single method will fully protect you from future Excel errors, but here are a few tips to avoid common ones:
- Wrap lookup functions (e.g. XLOOKUP) in an IFNA-function. #N/A errors result most commonly when no match was found.
- Run a quick “Find & Select” for #NAME? errors whenever you have just deleted a defined name, table, or VBA script containing UDFs.
- Use spill functions on empty worksheets so that room is available for results to be displayed. #SPILL! errors are common in tight spaces.
- Implement checks to remove leading or trailing whitespace. This can be done in Power Query, using a FILTER-function, using AutoFilter or by using conditional formats. Whitespace is one of the most common causes of #VALUE! errors.
Errors in Excel trickle down to all places that reference them, so even a simple #NAME? or #REF! error can completely break your spreadsheet. Either solve the issue directly by making sure no error triggers, or, if the errors are expected, put mechanisms in place to allow for them.
When a formula points to a cell containing an Excel error, the error will be displayed in the referencing cell. It seems innocent, for surely the problem is in the original cell. However, this “error inheritance” masks any additional mistakes in referencing formulas.
Excel errors should always be dealt with. References to them are hard to verify, so they are always seen as a risk.
How to avoid?
References to Excel errors often result from errors popping up elsewhere. Build in mechanisms to catch errors early; this will help to prevent errors down the line.
Unreliable Calculations
Hardcoded numbers are numbers that have been entered into a formula directly. Such numbers appear without a name or label, which makes it hard to tell what they mean. It’s also easy to miss one or two formulas when changing hardcoded numbers.
A hardcoded number is any number that appears as a literal value in a formula.
Excluded numbers
The following numbers are excluded:
In general:
- 0, 1 (frequent numbers)
- 10%, 25%, 50%, 75%, 100% (frequent percentages)
In the context of multiplication/division:
- 0.5, 2, 10, 100, 1000 (common factors)
- 4 (quarters in a year)
- 7 (days in the week)
- 12 (months in a year)
- 24 (hours in a day)
- 30, 31 (days in a month)
- 52, 53 (weeks in a year)
- 60 (minutes in an hour)
- 365, 366, 365.25 (days in a year)
- 3600 (seconds in an hour)
Excluded function arguments
Sometimes a hardcoded number is not bad practice. For example, the first argument in a SUBTOTAL()-function decides what aggregation method to use. These accepted places for hardcoded numbers are excluded as well.
Specific arguments in these functions are excluded:
- AGGREGATE, SUBTOTAL
- HLOOKUP, MATCH, VLOOKUP, XLOOKUP, XMATCH
- ADDRESS, DROP, EXPAND, INDEX, OFFSET, SORT, TAKE
- DATE, EDATE, EOMONTH, TIME
- CEILING, CEILING.MATH, CEILING.PRECISE, FIXED, FLOOR, FLOOR.MATH, FLOOR.PRECISE, ISO.CEILING, ROUND, ROUNDDOWN, ROUNDUP, TRUNC
- CHAR, FIND, FINDB, LEFT, LEFTB, MID, MIDB, REPLACE, REPLACEB, REPT, RIGHT, RIGHTB, SEARCH, SEARCHB, SUBSTITUTE, UNICHAR
- LARGE, SMALL, PERCENTRANK, PERCENTRANK.INC, PERCENTRANK.EXC, PERCENTILE, PERCENTILE.INC, PERCENTILE.EXC, QUARTILE, QUARTILE.INC, QUARTILE.EXC
Common hardcoded numbers are marked as an observation. All other numbers are considered a risk.
Common numbers include all of the following:
- 0.25, 0.5, 0.75
- 2, 3, 4, 5, 7, 12
- 0.000001, 0.00001, 0.0001, 0.001, 0.01, 0.1
- 10, 100, 1000, 10000, 100000, 1000000
How to avoid?
Separate constants from your formulas and label them clearly. If a constant is used several times, consider turning the cell into a defined name.
Double Operators occur when two or more operators are next to each other in a formula. This is usually an error and can lead to unexpected results, for example “–” is actually treated as a “+” in Excel.
When multiple operators occur in sequence, this isn’t necessarily a problem. Multiply by a negative number and you’ll have an asterisk (\\*) followed by a minus sign (-). However, in some cases the sequence is questionable. Two percentage signs? Try it in Excel: it really works. It will divide the preceding number by 10,000. It works, but it’s probably a typo. This inspection triggers when it isn’t obvious if two or more operators in sequence are deliberate.
The following two adjoining operators will trigger a weakness: ++, +-, -+, %%.
Since a double minus (–) is not only hard to spot but also does the opposite of using a single one, this case is triggered as a risk.
How to avoid?
Pay close attention when entering formulas. Some might find it helpful to use spaces around operators. Although it may help with clarity, use of whitespace in formulas is an uncommon practice among Excel modellers.
Lookup functions in Excel don’t necessarily search for an exact match. For example, if you omit the last argument in a VLOOKUP function it actually performs an ‘Approximate match’, which only works when the data you search in is sorted.
Approximate lookups apply to VLOOKUP(), HLOOKUP() and MATCH(). In the first two, the forth argument must be `FALSE`. In MATCH(), it must be `0`. Other arguments (or unspecified arguments) will result in approximate lookups, which trigger this issue.
Any approximate lookup triggers a weakness.
How to avoid?
Use XLOOKUP or XMATCH to avoid issues with incorrect lookups. Both of these performs exact searches by default.
A double count occurs when a cell is calculated more than once in the same formula, either directly or indirectly. This could mean that the result of the formula is incorrect.
While selecting a data range for a SUM, it’s all too easy to include any subtotals that might be among them. And it doesn’t happen with SUMs alone: cells can find their way into a calculation more than once when this is often unintended.
The impact a double count can have on a model is quite severe, which is why it’s a risk. If a number is included three or more times it might be intentional and the level drops to a weakness.
How to avoid?
When including separate aggregations among a data set, use Excel’s SUBTOTAL-function. Use it both for subtotals and the grand total; the SUBTOTAL-function ignores any SUBTOTAL results it encounters, eliminating the risk of a double count.
A circular reference occurs when a formula refers back to itself, either directly or through a series of intermediate formulas. Calculations rarely need to be circular, so it’s always a good idea to verify them.
Formula results are recalculated automatically whenever something changes in one of the cells they reference. If the referenced cells contain formulas too, Excel will dutifully calculate those first. This way, even in a modest spreadsheet model, just a single cell edit can cause a whole chain of formulas to be recalculated. Very useful!
So what happens when, in this chain of formulas, a formula is found that has already been encountered? Take the simple case in which a formula in cell A1 references cell B1, and cell B1 contains a formula that makes a reference back to cell A1. What should Excel do?
What Excel should do very much depends on what was intended. Often, circular references have slipped in by accident. In such cases the formulas must simply be reworked. If a circular dependency is a ciritical element of the calculation, an alternative is to enable iterative calculation.
Circular references are considered a risk.
How to avoid?
During construction, pay careful attention not to introduce circular references. They slip in all too easily: through typos or by selecting the wrong cells. Never just accept Excel’s warning about a circular reference. However, do not rely on Excel’s warning alone. No warning does not mean there is no potential for circular references; some of them only pop up under certain conditions.
Reference Issues
The layout of a Pivot Table is often dynamic: rows and columns shift as the source data changes. When you move a formula, Excel automatically updates its references, but when a Pivot Table changes, references to this table are not updated accordingly. Also consider that Pivot Table cells contain derived values which may be out of date. As such, it is better to avoid directly referencing Pivot Tables.
References to pivot tables that make use of the GETPIVOTDATA-function, which is the officially recommended way to do it, are excluded from this inspection.
Referencing data inside pivot tables without using GETPIVOTDATA is always a risk.
How to avoid?
Pivot tables are powerful summarising tools for understanding & presenting data. It’s okay to use them. The key is not to use use them as an intermediary result. Use them as endpoints only: in a report, dashboard, or summary sheet.
Pay close attention to formulas that reference empty cells. Are these cells knowingly empty, such as when the spreadsheet serves as a template model? Or are there gaps in the source data? Perhaps some of it got accidentally deleted, or a data import failed somewhere. Note that the reference itself could also be the mistake: perhaps the formula is supposed to refer to a different, non-empty cell.
Some empty cell references are expected: when the spreadsheet serves as a template model for instance. Or at the bottom of a data range, where additional data may be inserted in the future. This inspection serves to flag those empty cell references that look suspicious or which must be verified manually.
Formulas that refer to multiple empty cells trigger an observation. When only one empty cell is referenced it’s considered a risk. Three conditions to keep in mind:
- References are grouped per formula range. So for a formula in A1:A100 with a reference to B1 in the first cell we consider the full range B1:B100.
- A formula that contains only a single cell reference (e.g. “=A1”) always triggers an observation.
- Empty cells at the bottom of a cell reference are ignored. E.g. if you calculate the sum of a table column in which only the top half is filled, the bottom half is simply ignored.
How to avoid?
Empty cell references can be the result of a mistake on either end of the issue. Either a correct reference points to missing data, or an incorrect reference points to empty cells as a result. In the first case, verify your input sources. In the second, check your formula.
A longer cell reference is expected when a formula refers to only part of a group of similar cells. In most cases adjacent cells should all be included.
This issue triggers for every cell reference in a formula under the following conditions:
- When a cell reference points to data cells, additional cells are considered missing from this reference if they are of the same data type and are styled the same way.
- When a cell reference points to formula cells, additional cells are only considered missing if they contain the same formula.
References to a mix of data and formula cells are not considered.
When a cell reference doesn’t include logical adjacent cells, it considered a risk. As more cells are left out from the reference, chances increase that the partial reference was intentional. When the size of the reference reaches less than half the data range, it is marked as a weakness.
How to avoid?
During construction, it is useful to keep in mind these tips regarding references:
- Use tables and table references whenever you can. Tables grow automatically as new data is added, and references to them don’t have to be updated.
- When not using tables, either reference entire rows and columns or make sure to include plenty of extra cells. Avoid referencing exactly the data you need unless that data never changes its dimensions.
- For aggregation purposes, it’s often sufficient to use a pivot table. Pivot tables can be refreshed with the press of a button without having to change any formulas.
The name of a worksheet is not required for cell references within the same worksheet. Specifying the current worksheet makes a formula unnecessarily long and harder to read.
References to cells in other worksheets require you specify the worksheet name (the part before the ‘!’). For cell references that point to places in the same sheet, the name is optional. It is best not to include a current sheet name if possible, because it makes the formulas less readable.
Current worksheet references work just fine, even though they’re harder to read. You might want to clean them up a little, so they’re marked as observation.
How to avoid?
The best tip is to pay attention when switching worksheets while you edit a formula. While you edit a formula, clicking a cell automatically inserts a reference to that cell. When you switch to another worksheet, Excel helps you out by prefixing the worksheet name even before you click a cell. However, if you go back to the worksheet you were editing, that prefix simply changes to the current worksheet.
Unused defined names are named ranges that are not referenced anywhere in formulas. They are often relics of the past or get introduced by copying worksheets between workbooks.
Any defined names that is not found in any formula is considered unused. Note that some of these defined names may be used in VBA or in other workbooks through external links.
Unused defined names are always marked as an observation.
How to avoid?
Pay close attention when copying worksheets from other workbooks. Also consider checking the Name Manager whenever you delete a worksheet. Oftentimes defined names get left behind and clutter the Name Manager.
Formula Interruptions
Formulas should generally be consistent throughout the cell range they cover. When interrupted by different formulas, it’s time to investigate; some formulas might have been misplaced.
Triggers when a formula is interrupted by a different formula, but only when the other formulas cover ≤20% of the original formula range. Interruptions are only detected in the direction of travel of the formula; a vertically extended formula will only trigger interruptions in rows, a horizontally extended formula in columns.
Formula interruptions by other formulas are always considered a risk.
How to avoid?
Keep formulas together and protect them to prevent accidental deletion. On dashboard sheets where different formulas may sit in close proximity, use clear styling to differentiate between them.
Formulas should generally be consistent throughout the cell range they cover. When interrupted by empty cells, some formulas might be missing. Were they accidentally deleted?
Triggers when a formula is interrupted by empty cells. Interruptions in the corner or fully along the bottom or top are ignored. Also note that interruptions are only detected in the direction of travel of the formula; a vertically extended formula will only trigger interruptions in rows, a horizontally extended formula in columns.
Formula interruptions by empty cells are always considered a weakness.
How to avoid?
Keep formulas together and protect them to prevent accidental deletion. Also try not to use extensive areas of empty rows and columns as margins between tables. Instead place them on separate worksheets.
Formulas should generally be consistent throughout the cell range they cover. It’s rarely good practice to mix data and formulas in the same series. Was a formula overridden by accident?
Triggers when data cells interrupt a formula, but only when the data cell coverage is ≤20% of the formula range. Interruptions are only detected in the direction of travel of the formula; a vertically extended formula will only trigger interruptions in rows, a horizontally extended formula in columns.
Formula interruptions by data are always considered a risk.
How to avoid?
Follow principles of good Excel modelling: have separate import workflows, calculation steps and output sheets. Mixing manual input alongside formulas is where things tend to go wrong. If input and formulas are necessarily close together, make sure to protect formula ranges to keep them consistent.
Complexity
You know the type: formulas that aren’t even visible in Excel unless you extend the formula bar down a few lines. Long formulas are hard to read and maintain, and often work better split into multiple steps.
This inspection will trigger if the formula is at least 200 characters long and doesn’t also trigger a case of “Many Nested Functions”, “Many Operations”, or “Many References”.
A long formula is considered a weakness.
How to avoid?
Break up long formulas into small, manageable chunks. A series of steps is easier to follow than one long calculation. Each cell should calculate a logical part of the formula and present a clear intermediate result.
Too many different functions and operators in a single formula are an indicator of complexity, which is often a sign of a formula trying to do too much.
This inspection will only trigger if there are at least 8 different functions and operators and if the formula doesn’t also trigger a case of “Many Nested Functions”.
A formula that has between 8 and 9 different functions and operators is considered a weakness. 10 or more operations raise this level to risk.
How to avoid?
Break up long formulas into small, manageable chunks. A series of steps is easier to follow than one long calculation. Each cell should calculate a logical part of the formula and present a clear intermediate result.
Nested functions are Excel functions used inside the arguments of other functions. Long chains of nested functions are hard to read and maintain, and often work better split into multiple steps.
This inspection triggers when a chain of nested functions includes at least 5 function calls.
A chain of nested functions with 5 or 6 function calls is considered a weakness. From 7 functions it is considered a risk.
How to avoid?
Break up long formulas into small, manageable chunks. A series of steps is easier to follow than one long calculation. Each cell should calculate a logical part of the formula and present a clear intermediate result.
In Excel, it is possible to refer to cells across multiple worksheets at once. Although seemingly powerful, its need often indicates that data has been unjustifiably split over multiple worksheets. Multiple sheet references are fragile constructs, for they impose a functional role to the order of worksheets. In addition, few people know of these types of references so they’re easily overlooked or broken during future updates to the spreadsheet model.
Multiple sheet references are always considered a risk.
How to avoid?
Avoid the temptation to use worksheets as a means to distinguish different categories of otherwise similar data. For example, don’t use one worksheet for every booking year where each year contains tabular data with the same headers. Rather, keep everything in one table and add a column specifying the year. This may seem less structured to the eye, but it will promote a far more organised calculation model without needing to resort to feeble solutions such as multiple sheet references.
Too many references in a formula are an indicator of complexity, which is often a sign of a formula trying to do too much.
This inspection will only trigger if there are at least 10 references in a single formula and the formula doesn’t also trigger a case of “Many Operations” or “Many Nested Functions”.
A formula that has between 10 and 19 references is considered a weakness. 20 or more references raises this level to risk.
How to avoid?
Break up long formulas into small, manageable chunks. A series of steps is easier to follow than one long calculation. Each cell should calculate a logical part of the formula and present a clear intermediate result.
Many conditional format rules make a spreadsheet significantly harder to maintain. They can also make Excel less responsive, so it’s good to keep an eye on them.
Spreadsheet responsiveness is never quite linear with the number of conditional format rules. It very much depends on the formatting applied (cell borders are a bit heavier on Excel than fills) and the interaction between formatting rules (Is there overlap? Are there “Stop If True” checkboxes?). It can also vary from machine to machine.
It is considered a weakness to have more than 25 conditional format rules in one worksheet.
How to avoid?
Conditional format rules are copied over when you paste from another spreadsheet or worksheet. Always take care to check the conditional format rules after such operations. Also be mindful of generated conditional format rules through VBA scripts.
Vulnerable Formulas
Some functions lead to errors more quickly than others; because of how they are designed, by the way they are used or as a result of the unexpected ways Excel handles them. Even a few commonly-used functions should often be reconsidered.
CELL()
Getting information about a cell using the CELL-function is language-specific. For example, CELL(“format”, A1) gives the number format in English, CEL(“notatie”, A1) gives the same information in Dutch. In this particular example, not even the outputs match between the two. If the cell format of A1 is “General” (the default), the output is “G” in English but “S” (for “Standaard”) in Dutch. Unlike function names, function arguments are not updated automatically when the same spreadsheet is opened in Excel set to a different language. That’s why it’s strongly advised not to rely on the CELL-function when setting up your model.
INFO()
Reading information about the environment using the INFO-function is language-specific. For example, INFO(“numfiles”) gives the number of selected worksheets in all currently open workbooks in Excel, but only when Excel is set to English. In Dutch, one would have to write INFO(“AANTALBLADEN”) to get the same information. Unlike function names, function arguments are not updated automatically when the same spreadsheet is opened in Excel set to a different language. That’s why it’s strongly advised not to rely on the INFO-function when setting up your model.
OFFSET()
When you move or insert cells, Excel automatically updates any references to shifted cells. This does not work for relative references using the OFFSET-function. OFFSET-functions also have the nasty side-effect of being recalculated after every edit.
LOOKUP()
The LOOKUP-function is deprecated and should no longer be used. Not only is this function deprecated, LOOKUP is actually untrustworthy for general use. That’s because LOOKUP assumes the search range is sorted, which isn’t always apparent and usually cannot be guaranteed for arbitrary data sets.
VLOOKUP() and HLOOKUP()
The VLOOKUP-function is vulnerable for two reasons:
- When you move or insert cells, Excel automatically updates any references to shifted cells. However, Excel does not update the third argument of VLOOKUP, which specifies the column from which to take the value in the matching row.
- The last argument defaults to TRUE (for ‘Approximate Lookup’), which means VLOOKUP assumes the search range is sorted. Even if this behaviour is known, it isn’t always apparent that data is sorted correctly and it usually can’t be guaranteed for arbitrary data sets.
The same problems apply to HLOOKUP in the horizontal direction.
TEXT()
The second argument in the TEXT-function is a format specifier, which is language-specific. It will break when the spreadsheet is opened in a version of Excel set to a different language.
INDIRECT()
INDIRECT is a very powerful function that can solve problems no other function can solve. However, many of these problems shouldn’t have been problems in the first place. In fact, INDIRECT poses problems of itself:
- Errors in INDIRECT are hard to debug; you get no fancy syntax colours and any unresolvable output yields a #REF!-error.
- Adding, removing, or just moving cells (let alone entire rows or columns) can break an INDIRECT. This as opposed to regular cell references, which are usually updated automatically.
- Within INDIRECT, references to external files only work when the file in question is open, else you’ll get a #REF!-error.
- INDIRECT is slow: it is volatile—meaning it recalculates after every edit—and it doesn’t run multi-threaded.
Use of any error-prone functions is considered a weakness.
How to avoid?
Alternatives to VLOOKUP(), HLOOKUP() and LOOKUP()
Use XLOOKUP instead. XLOOKUP separates the lookup range from the return range. In other words: no more counting rows and columns! Need to use the matching row or column in multiple places? Then a combination of INDEX and MATCH could also be an option. Look for what you need using MATCH, then target another cell using the MATCH’s result in INDEX.
Alternatives to CELL(), INFO() and TEXT()
Relying on language-specific formatting is generally not a good idea. Format your cells using Excel’s regular number formatting.
Alternatives to OFFSET()
Avoid OFFSET-functions if at all possible. Replace them with ordinary cell references, or use INDEX-functions to target specific cell indices. Only use OFFSET as a last resort.
Alternatives to INDIRECT()
Take a critical look at your model. What is the INDIRECT-function achieving, and is it absolutely necesssary? Here’s some common workarounds:
- Need to refer to a different worksheet based on some condition? Either remodel your data to fit on a single worksheet or use the CHOOSE-function to refer to the correct worksheet. (Provide all options as arguments to CHOOSE, then use the first argument to specify which to take, possibly using a MATCH function to enable a text-based selection).
- Need to reference cells based on a calculated distance from other cells? Use INDEX to achieve the same.
- Using INDIRECT to vary the link to some external source? Use Power Query to build a solid import workflow instead.
Some functions in Excel are volatile; these cause Excel to recalculate after every formula edit, so be wary not to overuse them. They can negatively influence the performance of your model.
Functions that cause Excel to recalculate on every edit (“volatile functions”) include: NOW, RAND, RANDARRAY, RANDBETWEEN and TODAY.
When Excel’s calculation option is set to “Manual” and a time-sensitive function (NOW, TODAY) is present, a weakness is triggered. Other volatile functions trigger an observation.
How to avoid?
Volatile functions aren’t the root of all evil, but if you’re experiencing slow performance when editing cells in Excel then reducing volatile functions is a good place to start. A quick way to use of volatile functions more sparingly is to use them temporarily.
Before dynamic array formulas were introduced in Excel, array-like operations could be performed by entering formulas with Ctrl + Shift + Enter (these “legacy array formulas” are also known under the acronym CSE-formula). Two curly braces around the formula indicate its ability to calculate with ranges where normally only cells are expected. However, these array formulas should no longer be used. They are easy to miss, easy to break, and no longer necessary.
Any formula entered with Ctrl + Shift + Enter will trigger a warning.
Legacy array formulas are always considered a weakness.
How to avoid?
Update to a recent version of Excel and use modern array formulas. Any formula whose output spans multiple cells will automatically spill over in adjacent cells.
Functions marked as depecated by Microsoft should no longer be used in Excel formulas. Many such functions have been replaced to fix known issues or expand capabilities. Suppport for deprecated functions may be dropped in the future.
When a deprecated function is used in more than one place, only a single finding will trigger. The following functions are marked as deprecated:
BETADIST, BETAINV, BINOMDIST, CEILING, CEILING.PRECISE, CHIDIST, CHIINV, CHITEST, CONCATENATE, CONFIDENCE, COVAR, CRITBINOM, DATEDIF, EXPONDIST, FALSE, FDIST, FINV, FLOOR, FORECAST, FTEST, GAMMADIST, GAMMAINV, HYPGEOMDIST, ISO.CEILING, JIS, LOGINV, LOGNORMDIST, N, MODE, NEGBINOMDIST, NORMDIST, NORMINV, NORMSDIST, NORMSINV, PERCENTILE, PERCENTRANK, POISSON, QUARTILE, RANK, SINGLE, STDEV, STDEVP, T, TDIST, TINV, TRUE, TTEST, VALUE, VAR, VARP, WEIBULL, ZTEST
Deprecated functions always trigger as observation.
How to avoid?
Deprecated functions should not be used in new models. When typing the name of a function, Excel will typically warn you about its deprecation status with a yellow warning symbol.
Worksheets in Excel can be protected to prevent changes to cells. Certain cells can be excluded from protection so they remain editable. This can be useful for, say, input fields, but formula cells should usually be locked so they are safe from accidental changes.
Any formula cell in which the ‘Locked’ checkbox is unticked in the ‘Protection’ tab of the ‘Format Cells…’ dialog is marked as an unprotected formula.
Unlocked formula cells are marked as a risk when they are on a protected worksheet. On an unprotected worksheet, the level is dropped to weakness.
How to avoid?
Be careful when unlocking cells. Sometimes it is easiest just to redo the protection of the whole sheet:
- Select all cells (hit Ctrl + A twice to make sure not just a single table is selected)
- Right-click anywhere and choose ‘Format Cells…’
- Go to the tab ‘Protection’
- Tick the checkbox that says ‘Locked’ and close the dialog
- Now select just those cells that need to be unlocked
- Repeat the process, this time unticking the ‘Locked’ checkbox
Data Issues
Data validation rules ask the contents of a cell to follow certain guidelines. Not following these rules is certainly not best practice.
When entering new data in cells on which data validation rules are present, Excel warns you when you try to enter invalid data. However, data validation rules that have been applied to existing data may not guarantee that all data adheres to the rules.
Data that is invalid according to data validation rules applied to their cell are considered a risk.
How to avoid?
Use Data > Data Validation > Circle Invalid Data to find any invalid cells after changing or adding data validation rules.
Excel is a wizard with numbers. However, sometimes what appear to be numbers are actually text values that look like numbers. When calculating with numbers stored as text, the correctness of the outcome depends on the functions used. Sometimes it’s accurate, at other times it’s flat out wrong. It’s always worth a look.
This issue triggers when a formula runs numeric operations on text values that either look like numbers, dates or times. These text values could either be entered into the formula directly as a string (in quotes), or be situated in a different cell.
Excluded functions
The following functions will work fine when numbers are stored as text, so these are excluded from the inspection:
- XLOOKUP, LOOKUP, HLOOKUP, INDEX, MATCH, VLOOKUP
- AVERAGEA, COUNTA, COUNTBLANK, STDEVA, STDEVPA, VARA, VARPA
- ASC, CHAR, CLEAN, CODE, CONCAT, CONCATENATE, DATEVALUE, DBCS, DOLLAR, FIND, FIXED, LEFT, LEFTB, LOWER, MID, NUMBERVALUE, PROPER, REPLACE, REPLACEB, REPT, RIGHT, RIGHTB, SUBSTITUTE, TEXT, TEXTJOIN, TIMEVALUE, TRIM, UNICHAR, UPPER, VALUE, &, =, <>
- ISBLANK, ISNUMBER, ISTEXT, ISNONTEXT, ISNA, ISERROR, ISFORMULA
- DAY, HOUR, MINUTE, MONTH, SECOND, YEAR
- IF, IFS, IFERROR, IFNA
Excluded arguments
In the following functions, arguments for criteria ranges are excluded in specific situations:
- SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS
- SUMIF, COUNTIF, AVERAGEIF
By default, this issue triggers a weakness. However, if a function is known not to handle numeric text values well the level is raised to risk.
Functions that trigger a risk
- SUM, SUMIF, SUMIFS, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2
- COUNT, COUNTIF, COUNTIFS
- AVERAGE, AVERAGEIF, AVERAGEIFS
- MEDIAN, MODE, STDEV, STDEV.S, STDEV.P, STDEVP, VAR, VAR.S, VAR.P
- PRODUCT, AGGREGATE, SUBTOTAL
- MAX, MIN, LARGE, SMALL
How to avoid?
Make sure that all numbers—whether entered directly into the formula or through referencing cells—are actually stored as such. Especially take note during imports. Use Power Query for a solid import qorkflow or use data validation rules to verify numeric values.
Extra spaces in cell values can be tricky to spot, especially at the end of a cell value. Often such spaces are unintentional, stemming from unclean data imports or typos. Although such spaces may not cause any visual disturbance, Excel functions can trip over them easily. For example, lookup functions may not find a match for a value that to the naked eye looks identical.
Leading spaces, trailing spaces, and extra spaces in the middle of cell values will be marked as an Unnecessary Space. It doesn’t matter if the value is the result of a formula calculation or simply a data cell. Note that cells with only whitespace will not be marked (there has to be at a least a non-space character).
Cells with unnecessary spaces are marked as an observation. When (some of) these cells are referenced in formulas, this level is raised to risk.
How to avoid?
If your data comes from an import procedure, use Power Query to add a clean-up step to remove any unwanted spaces. If unwanted spaces enter the data set through input fields inside the spreadsheet model, consider using data validation rules to restrict input.
Spreadsheets are easily shared, so it’s important to know what’s in them. Especially when spreadsheet models must conform to compliancy regulations such as GDPR, knowing whether or not sensitive data is present is critical.
Whether you are an auditor or an attentive spreadsheet modeller, it’s important to know about the presence of sensitive data in Excel files. No-one wants their sensitive data leaked or to infringe on GDPR compliance regulations. Sometimes the presence of sensitive data is obvious, but other things are easily missed (think contact details of authors who have since left the company, or information tucked away in hidden columns).
The risk of incorporating sensitive data into spreadsheets often depends less on the data itself and more on the context in which it exists. Was it left there accidentally or has it been included consciously? Is the file appropriately protected? Since such external factors are often unknown, all sensitive data is marked as observation.
How to avoid?
You cannot always avoid having sensitive data in a model, but it should be there either deliberately or not at all. It must also be clear to all with access to the file that this data is in there. Documentation and security measures are key.
External References
Defined names that link to external workbooks are prone to give trouble down the road. They’re easy to miss; before you know it the spreadsheet is shared without its dependencies.
Keeping external references in one place helps to keep a spreadsheet maintainable. Keeping them on worksheets is best, since there it will be most transparent what a spreadsheet’s dependencies are.
Defined names with links to external Excel files are always marked as an observation.
How to avoid?
Point a defined name to a cell (range) in your workbook where external data is imported. This way you can keep using a defined name while keeping track of external references in an easily accessible place.
Pivot tables with direct links to external workbooks are prone to give trouble down the road because such dependencies are easily missed. This does not apply to pivot tables that make use of proper Data Connections, as the latter can be managed separately in Excel.
Although pivot tables keep an internal cache, they are often still based on values in cells elsewhere in the workbook. It is technically possible to reference data from cells in other workbook files. This is when we speak of pivot tables with external references.
Pivot tables with direct references to external workbooks are not at all obvious and seen as a weakness. When a pivot table makes use of a proper Data Connection, which can be managed separately in Excel, its presence is marked simply as an observation.
How to avoid?
Separate the import of data and the generation of pivot tables. That way, it’s easier to find and manage all the dependencies of your Excel model.
Links to external workbooks are commonplace in Excel, but they have to be used with care. Transparency and maintainability are key when it comes to using external sources in formulas.
Since links to other Excel files often include the file path, formulas with external references can get very long. It is usually a good idea to separate the import of data and any further calculations.
Even a single external reference is good to know about, so every link to an Excel file is marked as an observation. When a formula contains two or more external references, it is seen as a weakness.
How to avoid?
External references aren’t necessarily something to be avoided, but here’s a few tips to help organise them:
- Use Power Query when the import of data is complicated or when many files have to be referenced.
- When using links to external workbooks in formulas, use single references. Keep this retrieval step separate from calculations.
- Dedicate a single place in your workbook for external references. Use consistent naming or colour codes if multiple sheets contain external references.
- Clearly document what external references are used and where they are used in your workbook.
Charts that point to data sources outside the workbook are prone to give trouble down the road. They’re easy to miss; before you know it the spreadsheet is shared without its dependencies.
Charts in Excel are not self-contained objects. The graphs and pies they reveal are based on values in cells elsewhere in the workbook. It is technically possible to reference data from cells in other workbook files. This is when we speak of charts with external references.
It is inadvisable to have external references outside Power Query connections or direct cell references in worksheets, because anywhere else they become hard to track. Charts with references to external sources are therefore seen as a weakness of the model.
How to avoid?
Pay close attention when copy/pasting charts from other workbooks. This is a common way to inadvertantly introduce external references.
Hidden Information
Formula cells that have been marked as “Hidden” in cell formatting aren’t visible when the worksheet is protected. Such hidden formulas are easy to overlook when updating the calculation model.
Transparency kindles trust, so there’s no shame in showing your formulas. Would you trust a result if you couldn’t see the calculation?
If a formula is marked as hidden but still visible on the worksheet, it’s considered an observation. When a sheet is protected, and a formula on it is actually hidden from view, a weakness is triggered.
How to avoid?
Nearly every spreadsheet has a few formulas that don’t need to be constantly available during a model’s day to day use. However, it’s better to put such formulas on a separate worksheet and hide that worksheet than to hide just the formulas.
Hiding data doesn’t make the information disappear. Refrain from using creative means to conceal cell values, such as through color and formatting tricks. Invisible cells are easy to miss when you make changes to a model; it’s all too easy to delete or overwrite them.
Cells are marked as invisible when at least one of the following conditions are met, regardless of whether the cells contain data:
- The text color matches the background color
- Conditional format rules apply that makes cell content invisible
- Number formatting is set such that cell content isn’t rendered
Invisible cells are always considered a risk.
How to avoid?
Keep cell content visible. If you don’t want data to get in the way, put it on a different worksheet.
In addition to hiding a worksheet in Excel, a worksheet can also be made “very hidden”. This not only hides it from view, but also removes it from the Unhide worksheet list. This gives a false sense of security and should therefore be avoided.
Very hidden worksheets are often introduced by people with a lot of Excel experience. Most of the time, worksheets might just as well be hidden.
The problem with very hidden worksheets is that they’re not only hidden from end users but also from auditors, management, and other people that are working on the model. They’re also no more secure than regular worksheets, even though they sometimes feel as such. A very hidden worksheet is always considered a risk.
How to avoid?
Never assume security through obscurity, especially in Excel. Hidden content is not necessarily secure. If you must, hide a worksheet using the regular hide-functionality, then use a workbook password to raise the barrier to access (but always take care to implement extra measures). Worksheet protection can help to protect against accidental changes.
Worksheets in Excel are commonly hidden to remove clutter for end users, such as options for data validation lists or calculations that aren’t important during use. However, hidden worksheets may also hinder audits and prove a nuisance during model updates.
Consider why a worksheet must be made hidden: is it bringing clarity to end users? Go for it. Are you hiding it because you’re afraid to delete it? Probably not a great idea.
It isn’t necessarily good or bad to hide worksheets, but it’s always worth a look. That’s why hidden worksheets are marked as observation.
How to avoid?
Delete what you don’t need, hide what you need but doesn’t need to be in sight.
Hiding formulas doesn’t stop them from calculating. Hiding data doesn’t make the information disappear. All hiding does is to disguise the way a spreadsheet works and conceal its source data.
Transparency is one of the hallmarks of Excel, but hidden rows go against that philosophy. They can hide critical data or calculations from view. Hidden rows are also easy to miss when copying & pasting cells.
Hidden rows are seen as a weakness.
How to avoid?
Keep rows visible. If you don’t want calculations or data to get in the way, put them on a different worksheet.
Hiding formulas doesn’t stop them from calculating. Hiding data doesn’t make the information disappear. All hiding does is to disguise the way a spreadsheet works and conceal its source data.
Transparency is one of the hallmarks of Excel, but hidden columns go against that philosophy. They can hide critical data or calculations from view. Hidden columns are also easy to miss when copying & pasting cells.
Hidden columns are seen as a weakness.
How to avoid?
Keep columns visible. If you don’t want calculations or data to get in the way, put them on a different worksheet.
In Excel, hidden defined names are named ranges that are stored in the workbook, but are not visible in the Name Manager or on the worksheet. These names are typically used for internal purposes, such as print settings, table ranges, or third-party add-ins. Hidden defined names can be created by Excel itself or by end users through VBA code.
Hidden defined names are those defined names that can be referenced from formulas and VBA code, yet are not visible in the Name Manager.
Hidden defined names pose no inherent threat to users of the model, but they can lead to confusing situations. In large numbers, hidden defined names can also cause a spreadsheet to become slow. That’s why it’s good to know about them when auditing a spreadsheet. The presence of hidden defined names is marked as an observation.
How to avoid?
It is not always possible to prevent hidden defined names from appearing, as Excel itself will create them under certain conditions. When using macros, especially those not written by yourself, it’s a good idea to verify the macros don’t generate hidden defined names or hide existing ones.
Files & Settings
In theory a well-maintained spreadsheet can last forever, but in reality most spreadsheets don’t stand the test of time. Models become outdated and weighed down by legacy features. Suspiciously old files commonly indicate that an old version was accidentally opened.
Technology changes fast, so be wary of particularly old spreadsheets. Has an older version been opened by accident? Is the spreadsheet still relevant? Is it in need of an upgrade?
Spreadsheets created 5 or more years ago are marked as a weakness.
How to avoid?
Perform regular evaluations on the use of old Excel models in your organisation. Sometimes a rebuild of an old model can be the best option.
The Excel file extensions .xls and .xlt are outdated. Files of this format don’t support modern functions. When you encounter an .xls-file, it’s usually time to take a critical look at the model.
In Excel 2007, the default spreadsheet file extension switched from .xls to .xlsx (.xlt to .xltx for template files). It is based on Open XML, and has improved support for data recovery. Even though Excel will still open older file types, it is highly recommended to use the standard .xslx format.
Spreadsheets with the outdated .xls file extension are considered a weakness.
How to avoid?
Make sure to choose a recent file type when saving your workbook. For older files, look into the possibility of converting them into the new format. For most spreadsheets this should be as trivial as doing a Save As in a recent version of Excel.
Excel isn’t always the right tool for the job. A particularly large spreadsheet size is one indication that Excel is being used beyond its intended scope.
Data storage is the bread and butter of databases; programming languages digest complex algorithms for breakfast. Different technologies lend themselves to different tasks. So what about Excel? Excel is the middle ground, the jack-of-all-trades. When a database is overkill or a programming language too advanced, a spreadsheet can usually be whipped up in its stead. However, when a spreadsheet file gets to a certain size, it may be trying to do too much.
Files that are between 10-25MB are marked as a weakness, anything larger than 25MB is flagged as a risk.
How to avoid?
You cannot always avoid big spreadsheets. It’s important to look at what causes it. If it results from the presence of lots of media objects, such pictures and videos, you can opt to leave them in if they aren’t causing trouble. If there’s simply an awful lot of data, reconsider if Excel is still the right tool for the job. Sometimes spreadsheets simply try to do too much: the model may be better off split into separate files.
Iterative calculation enables Excel to compute models with circular dependencies. Once enabled, this setting masks any unintentional circular references and may increase calculation time.
When iterative calculation is enabled, Excel continues to calculate formula chains a predefined number of times. This allows for circular references, where cells refer back to themselves.
Iterative calculation masks potential mistakes in calculation chains, and it isn’t necessarily obvious that this option is enabled. Therefore this option is always considered a risk.
How to avoid?
Most calculations don’t require circular references, and almost all can be reworked to not require a circular reference. In most cases it’s best not to enable iterative calculation. Is it truly necessary? Make sure to clearly document this and thoroughly check your file for unintended circular references.
Usually, when a spreadsheet contains links to external workbooks, Excel asks to update these references. However, there is a setting which skips this question and just goes ahead to updating external references. Users unaware of this setting may unknowingly change data in their spreadsheet.
This issue pops up when the setting under Data > Workbook Links > Refresh settings is set to “Always refresh”
Updating external links automatically without informing end users is always considered a risk.
How to avoid?
Leave the Refresh settings to “Ask to refresh”.
Usually, when a spreadsheet contains links to external workbooks, Excel asks to update these references. However, there is a setting which disables this question and prevents Excel from automatically updating external references. Users unaware of this setting may unknowingly work with outdated data.
This issue pops up when the setting under Data > Workbook Links > Refresh settings is set to “Don’t refresh”
Never updating external links without informing end users is always considered a risk.
How to avoid?
Leave the Refresh settings to “Ask to refresh”.
This spreadsheet uses an outdated interpretation for dates, called the Date 1904 System. This system is incompatible with Excel’s usual date interpretation. To make matters worse, it incorrectly counts leap years and allows ‘negative’ dates. Copying 1904-system dates into a newer workbook does not give an error, but it does alter the dates.
Excel stores every date as a number, counting the number of days starting with the number 1 for the 1st of January 1900. An older system, called the *Date 1904 System*, counts days from the 1st of January 1904 starting with the number 0. The different assumptions about where to start counting make these systems incompatible.
Use of the 1904 date system is never recommended, so it is always considered a weakness at a minimum. If the spreadsheet also contains dates, the level is raised to risk.
How to avoid?
Never enable the *Use 1904 date system* option in Excel, and always check for this setting in Excel files that have been created a long time ago.
By default, formula results are recalculated automatically when needed. This behaviour is disabled when Excel’s calculation option is set to “Manual.” Those who fail to notice might wrongly assume formulas show up-to-date values, when in truth a recalculation is required.
A common reason for setting the calculation option in Excel to “Manual” is to cope with spreadsheets that have become really slow. Usually this hints at a greater underlying issue, such as an inefficient use of functions. Changing the calculation option is at best a temporary solution.
The calculation option “Manual” is considered a risk; the option “Automatic Except for Data Tables” a weakness.
How to avoid?
Non-automatic calculation options are commonly chosen to deal with models that are getting slow. Instead of changing the calculation option, consider the following options:
- Split the spreadsheet up into multiple files
- Use a database for large amounts of data
- Delete any unnecessary data and formulas
Take note that changing the calculation option impacts all open Excel files, not just the one you are working in.
VBA Issues
Macros are pieces of generated VBA-code based on a recording of user interaction with Excel. Compared to manually written VBA code, macros tend to be overly contextual, relying on very specific cells and worksheet names. This can make them unpredictable and hard to maintain.
At this time, macros are detected by the the auto-generated comments leading the Sub with recorded code.
Recorded macros are always considered a risk.
How to avoid?
In a modern Excel environment, VBA can often be avoided by using dynamic array formulas and Power Query. If Excel does not suffice, consider whether Excel is the right solution for the job at hand. If VBA is unavoidable, produce well-written code without resorting to recorded macros.
VBA scripts should generally not run by themselves, because they give Excel users no indication of what is happening, let alone a way to stop it. For example, VBA allows you to run scripts when you open the spreadsheet or when you select a different cell. Users unfamiliar with the model should want to do a thorough inspection before allowing this code to make changes to the spreadsheet.
This inspection triggers whenever there is VBA code that runs in response to events on the Workbook object or any of the Worksheet objects.
VBA code that is run automatically is always considered a risk.
How to avoid?
Set up an Excel model so that automated actions are nevertheless initiated by end users. A common use of events is to update external data sources, but it’s usually better to have a button for it. If you really want to do things automatically, it’s usually best to ask the end user first to confirm.
Cramming every last line of VBA code into a single Sub or Function is usually a sign of bad code organisation. To keep code maintainable, write small units of functionality. Ideally, have every Sub and Function handle one responsibility.
A function or subroutine that contains more than 50 lines of code is flagged as a Long Sub or Function that should probably be split up into logical parts.
A procedure (Sub or Function) longer than 50 lines of code is flagged as a weakness, anything longer than 80 lines a risk.
How to avoid?
Split long Functions or Subs into smaller, logical parts to keep your VBA code maintainable.
By default, Excel creates a VBA module called “Module1” when you first start adding VBA scripts. This is a stub that should be renamed to reflect the functionality contained within it. Use additional modules to separate logical units of functionality. Many lines of code crammed into a single module is usually a sign of bad code organisation.
This inspection triggers when a single module contains over 90% of all VBA code and is at least 100 lines long.
A non-modular code base a always considered a risk.
How to avoid?
Take note early in development to find names that most accurately reflect the functionality you’re writing, then rename modules to keep everything maintainable.
It’s the dreaded sight of the Excel user: a VBA script that ends in an error. End users don’t know what it did and didn’t do, how to fix it, or whether they should save or close without saving. Error handling in VBA is essential to decide up front what happens if things go wrong. Are there settings to reset? Cells and formulas to restore? Is an automatic retry in order?
This inspection triggers when there are no statements anywhere in VBA that begin with ‘On Error’.
Lack of error handling is always marked as an observation.
How to avoid?
Don’t try to predict what can go wrong. VBA scripts have a tendency to be creative when it comes to throwing errors. Instead, always consider what should happen if things go wrong. Implement `On Error` statements to catch errors and handle them appropriately. For example, make sure to reset settings like `ScreenUpdating` and `DisplayAlerts`.