The #VALUE error in Excel is an incredibly general error that usually signals something isn’t right with a formula or function. Whether you are working with a simple calculation or complex formulas, there’s a good chance that you’ll run into this error at some point.
This guide will not only show you how to identify a #VALUE error, but also the different ways to fix them. If you’re still having trouble or would like to skip this tutorial completely and get some personalized help, Excelchat is always standing by to lend a hand.
Understand the causes of the #VALUE error in Excel and learn to correct them.
Excess Spaces Cause the #VALUE Error
Just as we’re no longer supposed to put two spaces after a sentence when typing, too many spaces will also mess up your Excel formula. If you have two spaces where there should be just one (or one space where there should be none), you’re going to get a #VALUE error. Fortunately, this is simple to fix.
First, you can eyeball it and fix it manually. If you have several errors or are not sure where they are, this could be a tedious solution.
The other way to address this is with the Find & Replace feature in Excel. Select your referenced cells (or CTRL-A for your entire worksheet). Then, go to Replace from the Find & Select option on your Home ribbon. Under Find What, enter a single space. Under Replace With, make sure it is blank (no spaces). Choose Find Next to go through all instances or Replace All to replace all spaces in your selection with “no spaces.”
Sometimes there are extra spaces inside cells that are not readily apparent by looking at the formula. You can locate these by using the Evaluate Formula Wizard. Choose the cell with #VALUE Error and select Formulas > Evaluate Formula.
When you click on the “Evaluate” button, this checker can locate hidden spaces so you can fix them and your formula will work properly.
Another way to check for hidden spaces is to go to the cell. Double-click on the cell and see if there are extra spaces to the left of the cursor (see below). If there are, remove them.
If you have a large area to check, you can use the ISBLANK function to check whether cells in a column are blank or have hidden spaces. You’ll need to set this up in a new column, and then it will tell you if cells in the column you specify are blank.
Text or Special Values Can Cause the #VALUE Error
In addition to blank spaces, text or special values occurring where they shouldn’t can also lead to the #VALUE error. While this won’t solve the error, you can locate instances of these by using the ISTEXT function.
As you can see in the image above, C10 has an extra text character inserted after the price that is resulting in a value error with the formula.
The ISTEXT function can be used in a new column to check if there is any unwanted text in your source data. In column H, this produces a False result, except for H10, where there it indicates that there is additional text in column C10. You would have to then remove this text manually to fix your error.
You might also have text in a cell, and it just doesn’t work with the formula that you’re using. If the function expects a certain format, such as a date, and it finds a text value, you’ll get a #VALUE error. For example, the NETWORKDAYS function (below) is meant to compute working days, but the value in C5 (cheese) is not a date.
#VALUE Errors with Basic Subtraction
If your Excel spreadsheet is returning a #VALUE error with a simple subtraction formula, such as =C1-B1, you might have an issue with the way that Excel and your computer is set up.
You can often fix this in your settings. In Windows, click on the Start button, then select Region. On the Formats tab, choose Additional Settings. Look for the List Separator. If it is currently set to the minus sign, you’ll want to change it to something else such as a comma or semicolon. Click OK.
You will also get a #VALUE error if you attempt to subtract dates in Excel, but the dates are formatted as text instead of “dates.” The first thing you should check is that there are not any unnecessary spaces in your date. The next step is to check the format of your dates.
Highlight the dates being used in your formula. Click on Data > Text to Columns. Choose Next, and then Next again. Under Column data format, select Date. Pick your format and click Finish.
Another Option for Fixing the #VALUE Error
Although this isn’t always a recommended solution, you can handle #VALUE errors with the IFERROR function. What this tells Excel is, “if there is an error, do this.” Unfortunately, you might get rid of the error message but not get the answer you want from your formula.
For example, you can tell Excel to replace any “issues” with “0” instead of giving you an error message. This works on all errors, not just #VALUE. As you can see in the example below, the operation returns an answer for the formula instead of an error, but it’s not the correct or intended answer.
Still Need Help with Excel Errors?
Excel errors can be frustrating to locate and solve on your own. If you’re struggling to troubleshoot an issue in Excel, you can access quick and friendly help through Excelchat. Even better, it’s available around the clock and at your convenience.
Contact us now to find out more about how we can help solve your error. Your first session is always free.
Leave a Comment