A guide to correcting a #VALUE! error

silver and gold coins

  • Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Web App, Excel Starter 2010. 

This post will teach you what #VALUE! error means and offer you solutions for correcting a #VALUE! error whenever it comes up. Anytime you receive a #VALUE! error, Excel is in effect telling you that there’s a problem with the way you entered your formula. Alternatively, there’s an issue with the referenced cells in your formula.

However, the error can be problematic in the sense that it is non-specific, and so it can be difficult to decipher the particular cause of it. The details found within this page presents frequent problems and solutions for the error. You might have to carry out trial and error with one or several of the solutions to repair your given error.

Fix the #VALUE! error for a specific function

AVERAGE – Find further information at Correct the #VALUE! error in AVERAGE or SUM functions.

CONCATENATE – Refer to more details at Correct the #VALUE! error in the CONCATENATE function.

COUNTIF, COUNTIFS – Check further more information at Correct the #VALUE! error in the COUNTIF/COUNTIFS function.

DATEVALUE – Find further details at Correct the #VALUE! error in the DATEVALUE function.

DAYS – Find further information at Correct the #VALUE! error in the DAYS function.

FIND, FINDB – Check further details at Correct the #VALUE! error in the FIND/FINDB and SEARCH/SEARCHB functions.

IF- Check more information at Correct the #VALUE! error in the IF function.

INDEX, MATCH – Explore further information at Correct the #VALUE! error in the INDEX and MATCH functions.

SEARCH, SEARCHB – Take a look at some more information at Correct the #VALUE! error in the FIND/FINDB and SEARCH/SEARCHB functions.

SUM – Check further information at Correct the #VALUE! error in AVERAGE or SUM functions.

SUMIF, SUMIFS – Check more information at Correct the #VALUE! error in the SUMIF/SUMIFS function.

SUMPRODUCT – Discover further information at Correct the #VALUE! error in the SUMPRODUCT function.

TIMEVALUE – Explore extra information at Correct the #VALUE! error in the TIMEVALUE function.

TRANSPOSE – Find out more details at Correct the #VALUE! error in the TRANSPOSE function.

VLOOKUP – Learn more about this at Correct the #VALUE! error in the VLOOKUP function.

* None of the above – Is your function not included in this list? Test the other solutions listed below.

Problems with subtraction

How to do basic subtraction

If you’re unfamiliar with Excel, you could be entering a formula for subtraction mistakenly. Here are two ways to do it:

Subtract a cell reference from another

Cell D2 with $2,000.00, Cell E2 with $1,500.00, Cell F2 with formula: =D2-E2 and result of $500.00

Input two values in two individual cells. In a third cell, subtract one cell reference from the other. In this example, cell D2 contains the budgeted amount, and cell E2 includes the actual amount. F2 has the formula =D2-E2.

Or, use SUM with positive and negative numbers

Cell D6 with $2,000.00, Cell E6 with $1,500.00, Cell F6 with formula: =SUM(D6,E6) and result of $500.00

Enter a positive value in one cell, and a negative value in another. In a third cell, apply the SUM function to add the two cells together. In this example, cell D6 includes the budgeted amount, and cell E6 contains the actual amount as a negative number. F6 has the formula =SUM(D6,E6).

#VALUE! with basic subtraction

If you’re using Windows, you may receive the #VALUE! error during the process of performing the most fundamental subtraction formula. The following may overcome your issue:

  1. Start by doing a quick test. In a new workbook, input a 2 in cell A1. Enter a 4 in cell B1. Then in C1, write type this formula =B1-A1. If you get the #VALUE! error, go to the next step. If the error does not appear, test other solutions on this page.
  2. In Windows, launch your Region control panel.
    • Select Start, enter Region, and then click the Region control panel (Windows 10).
    • Windows 8: At the Start screen, input Region, pick Settings, and then choose Region.
    • Windows 7: Press Start and then enter Region, and then choose Region and language.
  3. On the Formats tab, pick Additional settings.
  4. Find the List separator. If the List separator is formatted to the minus sign, amend it to an alternative sign. For example, a comma is a common list separator. The semicolon is even regular. However, a different list separator may be more suitable for your specific region.
  5. Press OK.
  6. Access your workbook. If a cell has a #VALUE! error, double-click to modify it.
  7. If there are commas in any areas where minus signs are supposed to be for subtraction, edit them to minus signs.
  8. Tap ENTER.
  9. Follow this process continuously for other cells that contain the error.

How to subtract dates

Subtract a cell reference from another

Cell D10 with 1/1/2016, Cell E10 with 4/24/2016, Cell F10 with formula: =E10-D10 and result of 114

Enter two dates in two separate cells. In a third cell, subtract one cell reference from the other. In this example, cell D10 includes the start date, and cell E10 contains the End date. F10 contains the formula =E10-D10.

Or, use the DATEDIF function

Cell D15 with 1/1/2016, Cell E15 with 4/24/2016, Cell F15 with formula: =DATEDIF(D15,E15,"d") and result of 114

Write two dates in two separate cells. In a third cell, employ the DATEDIF function to uncover the difference in dates. For further information on the DATEDIF function, see Calculate the difference between two dates.

#VALUE! error subtracting dates stored as text

Extend your date column. When your date is aligned to the right, then it’s a date. Conversely, if it’s aligned to the left, then this actually reflects that the date is text rather than a date. More importantly, Excel will overlook text as a date. Listed below are some solutions that can aid this problem.

Check for leading spaces

  1. Double-click a date that is currently being applied in a subtraction formula.
  2. Move your cursor at the beginning and check if you can choose one or multiple spaces. Here’s what a chosen space appears like at the start of a cell: Cell with space selected before 1/1/2016 If your cell includes this problem, continue to the following step. If you don’t notice one or several spaces, proceed to the next section on reviewing your computer’s date settings.
  3. Pick the column with the date by selecting its column header.
  4. Select Data > Text to Columns.
  5. Choose Next twice.
  6. On Step 3 of 3 of the wizard, below Column data format, press Date.
  7. Click a date format, and then press Finish.
  8. Repeat this method for other columns to guarantee they have no leading spaces before dates.

Check your computer’s date settings

Excel employs your computer’s date system. If a cell’s date is typed using a different date system, then Excel will fail to see it as a real date.

For example, imagine that your computer shows dates as mm/dd/yyyy. If you entered a date like that in a cell, Excel would identify it as a date and you’d be able to apply it in a subtraction formula. However, if you inputted a date like dd/mm/yy, then Excel would consider it as text instead of a date.

There are two solutions to this problem: You could amend the date system that your computer uses to correspond to your desired date system to enter in Excel. Alternatively, in Excel, you may design a new column and deploy the DATE function to generate an actual date according to the date stored as text. Here’s how to accomplish that presuming your computer’s date system is mm/dd/yyy and your text date is 31/12/2017 in cell A1:

  1. Make a formula like this: =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))
  2. The result would be 12/31/2017.
  3. If you want the format to display like dd/mm/yy, press CTRL+1 (or Image of the MAC Command button icon + 1 on the Mac).
  4. Pick a different locale that uses the dd/mm/yy format, for example, English (United Kingdom). Once you’re finished setting the format, the result would be 31/12/2017 and it would be a real date rather than a text date.

Note: The formula above is written with the DATERIGHTMID, and LEFT functions. Please notice that it is written with an assumption that the text date contains two characters for days, two characters for months, and four characters for year. You might have to customise the formula to accept your date.

Problems with spaces and text

Remove spaces that cause #VALUE!

Normally #VALUE! happens because your formula refers to other cells that include spaces, or even trickier: hidden spaces. These spaces can cause a cell to look blank, when they are actually not blank.

1. Select referenced cells

Column selected

Find cells that your formula is referencing and select them. In many cases, deleting spaces for a whole column is a best practice because you can replace numerous spaces at the same time. In this example, selecting the E chooses the entire column.

2. Find and replace

Find & Select > Replace”>

On the Home tab, press Find & Select > Replace.

3. Replace spaces with nothing

Find what box containing a space, Replace with containing nothing

In the Find what box, enter a single space. Then, in the Replace with box, erase anything that might be there.

4. Replace or Replace all

Replace all button

If you are certain that every space in the column should be erased, press Replace All. If you’d prefer to walk through and replace spaces with no other value on a case-by-case basis, you can select Find next first, and then click Replace when you are confident the space isn’t needed. After you’re complete, the #VALUE! error might be resolved. If not, continue to the next step.

5. Turn on the filter

Sort & Filter > Filter”>

Often, there are hidden characters apart from spaces that can result in a cell to  appear blank, but it’s not really blank. Single apostrophes inside a cell can cause this. To eliminate these characters in a column, switch on the filter by navigating to Home > Sort & Filter > Filter.

6. Set the filter

Filter menu with the Select all checkbox unselected, (Blanks) checkbox selected

Choose the filter arrow Filter arrow , and then deselect Select all. Then, click the Blanks checkbox.

7. Select any unnamed checkboxes

An unnamed checkbox selected

Pick any checkboxes that have no values beside them, such as this one.

8. Select blank cells, and delete

Filtered blank cells selected

Once Excel returns the blank cells, pick them. Then, press the Delete key. This will delete any hidden characters in the cells.

9. Clear the filter

Filter menu, Clear filter from ...

Pick the filter arrow Filter arrow , and then choose Clear filter from… to ensure that all cells can be seen.

10. Result

#VALUE! error gone, and replaced with the formula result. Green triange in cell E4

If spaces were the trigger of your #VALUE! error then luckily your error has been substituted by the formula result, as displayed here in our example. If not, repeat this process for other cells referenced by your formula. Alternatively, experiment with other solutions on this page.

Note: In this example, pay attention to how cell E4 has a green triangle and the number is aligned to the left. This represents that the number is stored as text. This could lead to some further problems later. If you notice this problem, we recommend converting numbers stored as text to numbers.

Check for text or special characters

Text or special characters among a cell can result in the #VALUE! error. But usually it’s tough to identify which cells have these problems. Solution: Use the ISTEXT function to inspect cells. Please note that ISTEXT doesn’t rectify the error, it merely locates cells that could be the culprits of the error.

Example with #VALUE!

H4 with =E2+E3+E4+E5 and result of #VALUE!

Here’s an example of a formula that has a #VALUE! error. This is probably due to cell E2. There is a special character that manifests as a small box after “00.” Or as the next picture illustrates, you may use the ISTEXT function in a separate column to check for text.

Same example, with ISTEXT

Cell F2 with =ISTEXT(E2) and result of TRUE

Here the ISTEXT function was added in column F. All cells are acceptable other than the one with the value of TRUE. This indicates that cell E2 has text. To resolve this, you could erase the cell’s contents and retype the value of 1865.00. Or you could also use the CLEAN function to clean out characters, or use the REPLACE function to replace special characters with other values.

After using CLEAN or REPLACE, you’ll want to copy the result, and select Home > Paste > Paste Special > Values. You could also have to convert numbers stored as text to numbers.

Try using functions instead of operations

Formulas with maths operations like + and * might be unable to calculate cells that have text or spaces. In this case, consider using a function instead. Functions will typically ignore text values and calculate everything as numbers, removing the #VALUE! error. For example, rather than =A2+B2+C2, input =SUM(A2:C2). Alternately, instead of =A2*B2, type =PRODUCT(A2,B2).

Other solutions to try

Try to locate the source of the error

Select the error

Cell H4 with formula =E2+E3+E4+E5 and result of #VALUE!

Start by choosing the cell with the #VALUE! error.

Click Formulas > Evaluate Formula

Evaluate formula dialog with " "+E3+E4+E5

Press Formulas > Evaluate Formula > Evaluate. Excel will guide you through the parts of the formula independently. In this case, the formula =E2+E3+E4+E5 malfunctions due to a hidden space in cell E2. You cannot observe the space by looking at cell E2. However, you can notice it here. It appears as ” “.

Replace the #VALUE! error with something else

Frequently, you simply want to replace the #VALUE! error with something else like your own text, a zero or a blank cell. In this case, you can insert the IFERROR function to your formula. IFERROR will scan to confirm if there’s an error, and if so, substitute it with a different value of your preference. If there isn’t an error, your initial formula will be calculated. IFERROR will merely work in Excel 2007 and later. For earlier versions, you can use IF(ISERROR()).

Warning: IFERROR will hide all errors, not just the #VALUE! error. Hiding errors isn’t recommended because an error is often a sign that something needs to be fixed, not hidden. We don’t recommend using this function unless you are absolutely certain your formula works the way that you want.

Cell with #VALUE!

Cell H4 with =E2+E3+E4+E5 and result of #VALUE!

Here’s an example of a formula that has a #VALUE! error arising from a hidden space in cell E2.

Error hidden by IFERROR

Cell H4 with =IFERROR(E2+E3+E4+E5,"--")

And here’s the equivalent formula with IFERROR added to the formula. You can read the formula as: “Calculate the formula, but if there’s any kind of error, replace it with two dashes.” Note that you could even use “” to present nothing rather than two dashes. Or you could replace your own text, like: “Total Error”.

Unfortunately, you can see that IFERROR doesn’t actually rectify the error, it merely conceals it. So be confident that obscuring the error is better than repairing it.

Make sure data connections are available

Your data connection might have become unavailable somewhere along the line. To repair this, restore the data connection, or be flexible with importing the data, if appropriate. If you don’t have access to the connection, ask the workbook creator to create a new file for you. The new file ideally would only include values, and no connections. They can do this by copying all the cells, and pasting only as values. To paste as only values, they can select Home > Paste > Paste Special > Values. This removes all formulas and connections, and thus would even delete any #VALUE! errors.

Post a question in the Excel Community Forum

If you’re not sure what to do at this point, you can search for similar questions in the Excel Community Forum, or post one of your own.

Post a question in the Excel community forum

See Also

Overview of formulas in Excel

How to avoid broken formulas

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

Leave a Reply

%d bloggers like this: