Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

The #VALUE Excel Error – How to Find and Fix #VALUE Errors in Excel

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. 

The word value spelled with wooden blocks.

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

Evaluate formula box in excel.

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. 

Fixing the #Value? error screen shot.

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. 

ISBLANK formula screen shot.

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. 

Fixing the #Value?.

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. 

ISTEXT function in excel.

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.

NETWORKDAYS screen shot.

#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

Customize Format.

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. 

Screen shot B5-B4+B3

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

Convert Text to Columns Wizard  Step 3 of 3

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. 

IFERROR screen shot.

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.

Solution examples
using excel as a checkbook -- formula =IF(ISBLANK(E283),"",N282-J283+L283) after 280 lines it is now just saying VALUE in the balance column (N) What have I done wrong?
Solved by T. Y. in 44 mins
I have a list of data on one sheet, that I want to have copied to another sheet if a particular criteria is met, namely that it matches the client code listed in cell C1. Can you help?
Solved by A. H. in 53 mins
I need a formula that has these 2 rules. If letter was rec'd between 1st and 15th = 1st of the month following letter rec'd date. If letter was rec'd between 16thst and end of the month= 1st of the 2nd month following letter rec'd date.
Solved by V. F. in 20 mins
I need a formula that (1) finds a value in Column A, (2) copies all data in Column A between the cell found in (1) to a new value in Column A. (3) pastes what is copied to a designated range of cells (column) in Column B.
Solved by V. D. in 37 mins
I have 2 sheets. One sheet with the demographics of contractors. eg. Company A, Hispanic, Male. In the second sheet I would like to be able to choose the company from a drop down menu (listing all companies) in column A and have columns B and C automatically populated with Hispanic and Male.
Solved by A. Y. in 34 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc