As committed Excel users, we often go on and on about it as a productivity solution and essential program powerhouse. But catch us on a bad day and we are cursing that little green arrow and #VALUE! error like nobody’s business! Have you ever poured time into a worksheet, only to realize that the Excel formula is not calculating and you don’t even know where to start looking for fixes?
It doesn’t take much to break your formula – a formula that could be fairly simple or incredibly complicated. It is a whole new issue when the formula was created by someone else! That’s why the error checking feature in Excel makes us feel like a superhero when we are a bit too amped on caffeine and facing massive deadlines.
Here’s how to troubleshoot your formula and function errors like a champ!
- You have an error in your calculations when you see the error marker and/ or the text #VALUE! in a cell.
- Click the Error Checking button in the Formula Auditing group of the Formulas tab to launch the Error Checking dialog box.
- If there are errors in your formula, the dialog box lists them and explains the problem below. Click through the errors found using the Previous and Next buttons.
- For each error, choose one or more actions:
- Click the Help on this error button to review the Excel Help text on the error.
- Click the Show Calculation Steps button to view the formula with all source data in place.
- Click the Ignore Error button to take no corrective action on any given error.
- Click the Edit in Formula Bar button to edit the calculation directly in the formula bar.
- Once you have viewed and taken action on each error found during the error check, click the OK button in the confirmation dialog box to conclude the check.
That’s it! Errors vary, but using Excel’s form process makes fixing errors more manageable. So the next time that #VALUE! error lights up your screen, take a few deep breaths then follow this simple process to make your life much easier.