Thursday, August 22nd, 2019 / by Angie
Excel is an incredibly powerful tool for working with long lists of numbers and even complex datasets. The bigger and more complex the data gets, however, the more likely it is that errors will creep into the formulas that you are using. Sometimes, diagnosing those errors can be tricky. The cell which throws the error message could be several steps removed from where the error is. Indeed, in some cases there may not be an error in a specific cell. The spreadsheet could simply be giving you incorrect results with no sign that there is a problem at all.
As always, preventing errors with sound development practices is the best idea. When you are building your worksheet, save regularly. Keep a few old versions so that you can revert if you run into problems.
Pay attention to what Excel is telling you, too. One thing that a lot of people don’t realize is that error messages for serious syntax errors are not the only kind of warning or notification that Excel gives. While it’s true that error messages should not be ignored, Excel also offers some “polite warnings” about other potential problems and you should pay attention to those as well.
Excel will flag up syntactically correct, but potentially problematic, formulas with a green triangle in the top left corner of the cell. For example, it may warn you that you have an “inconsistent formula” if the formula in one cell is looking at a different range to the formula in other cells.
To find out what Excel doesn’t like, use the “Trace Error” option from right-clicking on the cell. This will tell you what it thinks is wrong. It could well be that you intended to look only at the specific cell range that you included in the formula. Excel doesn’t know that, though, and simply tries to guess what you were doing based on the rest of the layout of the sheet.
The Trace Error option is very powerful, and can help to diagnose all manner of problems. If your sheet has #VALUE! or #N/A! errors in it, you can hide them using IFERROR, but you should only do this if you expect certain results to throw an error and you know for sure that this will not have an impact on the rest of the sheet. The vast majority of the time, an error is a problem, and you should fix it immediately.
Use Trace to see where the error originated. You can step through all the formulas in the sheet, so if one cell acts based on the value of another cell, which contains a formula, and that cell’s input is other cells with a formula as well, then you can step through that for as many layers of formula as it takes until you find the cell with the problem. That issue could be anything from text in a numbered cell, to an incorrect date format. It’s easier to find when you can follow the path of data to the final output.
I can not recommend ExcelHelp more highly. We had been struggling to complete a critical project and finding them was a lifesaver. Tom was knowledgable, professional, personable, patient and responsive. Look no further for your database programming needs. Thanks Tom and ExcelHelp.com!Randy Hartnell
We anticipate a long business partnership with ExcelHelp and look forward to working with them. I have been nothing but impressed with your firm’s services, your team members tenacity and the overall results. Sometimes you have to travel a bumpy road trying different firms until you find the one that fits.Posted By: Charlene Faber
Chief Operation Officer, Current Builders
I’d like to express my gratitude for the help and cooperation we’ve received from your entire organization during the development of our Modeling Utility. Over the past several weeks we’ve engaged key customers worldwide...We have confidence that this effort will add significant capability and value to the overall success of the program.Business Development Manager
Network Architecture and Strategy
Posted By: Marty M.