How To Fix Excel #Value! Error

The #Value! error is an extremely general error message and can often be difficult and frustrating in finding where it is coming from. This is completely dependent on the complexity of the formula that is generating the error message. Think of the #Value! error like this, “Hey! Your formula is typed incorrectly, or you are passing an incorrect argument to your formula and I don’t know what to do now”. Vague, right? Well you have come to the right place!

There are many common problems that can occur that would result in a #Value! error and they all need to be handled differently.

Spaces and Text

Often when creating excel documents we rush or jam our keyboards in frustration. Sometimes are fingers run from us and type values in hidden unknown depths of excel. Many times, #Value! errors occur due to values being stored as text when they should be numerical. Spaces will certainly stop things from working too. They are various methods to check for things like this.

1. If you know the cells in which your formula is referencing, then you can use “Find and Replace”
2. Highlight the cells your formula is referencing.
3. On the Home Tab, click Find and Select > Replace. Or press Ctrl+F on Windows or CMD+F on MAC.
   1. Make sure your cursor is in the “Find What” field and press the spacebar one time. Then press “Replace All”
   2. You’ll see after completion that the space between the 2 and 5 in the price column become $300.25 and the #Value! error goes away.

#Value Error Unresolved Using SUM and Subtraction

And now the error goes away.

Sometimes you will get values that are stored as text incidentally. You will see a small red error triangle in the corner of the cell. When you hover over it you will see that it says, “Number Stored as Text”. If this is causing an issue with your formula, simply select “Convert to Number” and this should resolve your issue.

Other formulas that may cause a #Value! error are subtraction formulas, referencing formulas and query formulas. Be sure you don’t have incorrect spacing, syntax, referencing or arguments.

Other ways to handle #Value! Errors

Sometimes the error is not as simple to figure out as others. Maybe you have a complex formula or you are referencing other sheets using a formula and can’t see everything at the same time. You can try the below solution to assist you in figuring out the problem.

• Select the cell showing the #Value! Error and select “Show calculation steps”.
• You’re going to get a popup box that is going to assist you in evaluating your formula. Once the box is open look at the info provided in the “evaluation:” box. Items that are underlined are going to be the first part of the formula that gets evaluated. If you have a simple formula like the one shown, it will likely only have one step to evaluate.
• Sometimes when viewing the calculation steps it will show the result of the formula. Hit evaluate and then restart. This will show you the formula from the beginning.
• You’ll see that in the next example the first SUM portion of the example completes successfully. The subtraction portion is the portion that will give the error message such as the image to the right.

Once you figure out the error message and where the issue is coming from you can simply fix it and move on with your project.

Although not always recommended, you can use the IFERROR() function to handle the #Value! Error message. The IFERROR() function basically says, “If there is an error, skip this and ‘do’ this”. In the below example you’ll see that we want to see the word “Error” in the event of an error. There are many ways to handle and use this although it is important to understand what IFERROR() really does. Often times this will make it difficult to notice or see that you have an issue somewhere in your workbook and can often times make tracing errors more difficult

The IFERROR() function will also handle other errors as well such as #NA, #REF and a few others. It’s best practice to only use this function if necessary or if you are expecting these types of things to happen.

Simply “wrap” IFERROR() around the entire formula as shown.