Various studies report that nearly 9/10 spreadsheets contain errors, with the majority a result of human error relating to formulas. Spreadsheet errors have cost companies millions, and they can put your reputation and career on the line.
In this post, I'll guide you through a simple yet powerful Excel formula checklist to help you avoid those costly, embarrassing mistakes that could derail your professional progress.
And, as a bonus, I've got a free tool that will help you catch errors before they catch up to you.
Table of Contents
Excel Formula Checklist Video
Excel Errors Check List and Cheat Sheet PDF
I've prepared a handy PDF that covers all these steps. It contains an Excel Formulas Checklist and a Cheat Sheet that explains all those confusing Excel error messages. You can get the PDF here:
Enter your email address below to download the sample workbook.
1. Start with a Visual Scan
Your first line of defence is a visual scan. Thankfully, formula errors in Excel, like the classic #VALUE!, usually stand out.
But when you're working with large spreadsheets, it's easy to miss them. Here's how to quickly find them:
- Error Checking Tool: Head to the Formulas tab and use the Error Checking feature to jump between errors one by one. You can even get detailed help or choose to ignore the error if needed.
- Find Tool (CTRL + F): Search for all formulas that start with the hash sign. Simply type #* in the Find tool, set it to search within the entire "Workbook" and look in 'Values. Click Find All to get a list of every error in your file.
Tip: click the links in the 'Book' column to jump to the error and make corrections without closing the Find dialog box.
- Fix Circular References: A common issue people overlook is circular references, where a formula refers to itself, causing an error. Excel will give you a warning in the bottom left of the window when this happens:
You can either navigate through the sheets to locate the error or go to the Formulas tab > Error Checking > Circular References to pinpoint the issue.
- Error Indicators: Excel places green error indicators on cells when there are inconsistencies. For instance, in Excel Tables, each row in a calculated column should have the same formula, but if some cells have hardcoded values, you'll see an indicator.
Click the warning dropdown for more information and decide whether to restore the formula across the column, ignore the error, or manually fix it. If you want to ignore all similar errors, select the cells, click the error flag, and choose Ignore Error.
2. Check Your Inputs
Once you've tackled errors, check your inputs to make sure your formulas are referencing the correct cells:
- Use the Trace Precedents feature under the Formulas tab to see which cells are feeding into your formula.
Tip: double click on the blue lines to jump to the cells if they're out of sight.
- Press F2 to enter edit mode. Excel will color-code the cells used in the formula, making it easy to spot any incorrect references.
- Be wary of hidden cells: Sometimes, hidden rows or columns contain data affecting your formulas without you realizing it. Unhide everything momentarily to check for any surprise data, especially totals or subtotals that could cause duplicate calculations.
3. Validate Logic
Step back and think, "Does this formula actually make sense for what I'm trying to do?" It's easy to overcomplicate things.
If you find yourself writing complex nested formulas, consider breaking them into smaller, more manageable formulas across multiple cells. It's not only easier to verify and debug, it's often more efficient for Excel to calculate.
4. Calculation Test
Finally, put your formula through a calculation test. Enter simple values that are easy to calculate in your head to see if the formula produces the expected result.
This will help you confirm if your logic and math are sound.
Test edge cases like zero values or blank cells — these can reveal hidden bugs in your formula.
5. Range Check
Another step is to verify that your formulas are referencing the correct range. If you're using whole column references, be cautious. These can cause issues if there's extra data in the column that you didn't intend to include in your calculation. Always ensure that your formulas are looking at exactly the cells you want them to.
Also, check if you're using absolute ($A$1) or relative (A1) references correctly, especially if you're copying the formula down multiple rows. A missing dollar sign can cause errors that are difficult to detect at first glance.
Error Checking Tools
Fellow Microsoft MVP, Craig Hatmaker has created two tools that catch formula mistakes, model design errors, and undesirable results.
Integrity Checks - These are formulas that catch formula mistakes, model design errors, and undesirable results. Read about them here.
Testing - We can run our formulas through hundreds of randomized scenarios to make sure they work under ALL conditions. I use a free tool to help automate it. Read about it here.
Next Steps
If you're looking to level up your Excel formula game even further, check out my Advanced Excel Formulas Course.
In just 5.5 hours, you'll learn everything from basic lookups to advanced functions like LAMBDA and Dynamic Arrays, with real-world examples that will truly set you apart.
How to Learn Excel Fast
jim
Don’t use IFERROR() as it can hide unexpected or unintentional errors
If there’s likely to be an error, anticipate it and deal with it – don’t be lazy and brush it under the iferror rug
Totally agree with the edge cases testing – they often cause the unanticipated errors
Watch out for numbers-stored-as-text, simple test data should help highlight those
Avoid the missing absolute dollars problem by using named ranges – makes verification and debugging easier too
American dates can cause havoc (why can’t they just be like the rest of the world?) – always have dates formatted to show the month in letters (eg dd-mmm-e)
Mynda Treacy
Hi Jim,
Thanks for sharing.
I agree with most of your points, except if there’s an anticipated error and you deal with it i.e. hide it from displaying in your report, one way is to use IF. I don’t see that this is any less of an issue than using IFERROR. IFERROR is great for handling #DIV! errors and #N/A! errors returned by lookup formulas, so I don’t think a blanket ban on IFERROR is necessary. I usually recommend you write the formula without it, and then when you’ve tested the edge cases and verified that any errors are acceptable, go ahead and use IFERROR.
Mynda
jim
up to a point
IF can be used, for example, to check a specific cell for zero that you’re going to divide by and alert the user or use an alternative value
unfortunately, we often see =IFERROR(myformula,””), which just blanket hides any error and doesn’t necessarily DEAL with all circumstances that may arise
and if you’ve already dealt with all the possible errors, then why would you need it?
the only way in which I would recommend its usage is =IFERROR(myformula,”Contact Jim immediately!”), or something less self-aggrandising
perhaps we should use the sheet option to hide all error values (in the absence of that, use conditional formatting to show white-on-white if ISERROR is true)
those last two suggestions weren’t serious, but that sort of sloppiness is how error-laden spreadsheets come into general use and give Excel a bad name
I’ve had to fix too many bad spreadsheets, rant over