How to Fix the #VALUE! Error in Excel? 3 Different Scenarios
(Note: This guide on how to fix #VALUE! error in Excel is suitable for all Excel versions including Office 365)
When working with extensive data, convoluted calculations, or complex operations, the occurrence of errors is not strange. Based on the mismatch of data or mistakes in syntax, the errors in Excel are classified into different types. One such error is the #VALUE! error in Excel. This error occurs due to a variety of reasons and is very common.
In this article, I will tell you why the #VALUE! error in Excel occurs and how to fix the #VALUE! error.
You’ll Learn:
- What Is the #VALUE! Error in Excel and Why Does It Appear?
- How to Find the Cells With #VALUE! Error in Excel?
- How to Fix the #VALUE! Error in Excel?
Related Reads:
HOW TO FIX THE EXCEL NAME ERROR? 3 SIMPLE TIPS
HOW TO FIX THE #DIV/0 ERROR IN EXCEL? 2 EASY METHODS
IFERROR EXCEL-THE ULTIMATE GUIDE TO CATCHING ERRORS IN EXCEL
What Is the #VALUE! Error in Excel and Why Does It Appear?
The main reason why Excel throws a #VALUE! error is because Excel or the said function might be expecting a value of a different type, but the user has inserted or passed a value of a different type. In simpler words, the #VALUE! error in Excel appears when the value in the particular cell is not the expected value.
The #VALUE! error occurs due to a variety of reasons. Some of the reasons why the #VALUE! error occurs is:
- When the arguments passed in the formula are of a different type than the actual type.
- When you type in the wrong formula.
- When a text value is entered in place of a text value or vice versa.
- When operations are performed on dates, they are formatted as text values.
- When the cells are left blank (cells with a space or apostrophe, which make the cell appear blank).
- When arrays are not properly opened or closed.
How to Find the Cells With #VALUE! Error in Excel?
One of the most efficient means of fixing the #VALUE! error in Excel is to identify the place of error and input the right value type.
When working with relatively smaller data, it is easy to pinpoint if any error occurs. That particular cell will bear a #VALUE! text in the respective cell. But, when there are multiple rows and columns of data, it might be a bit difficult for you to search through the data and find the error. In such cases, you can easily use the Go To Special option to only show the errors.
- To find the cells with errors, first, navigate to the Home main menu ribbon.
- Under the Editing section, click on the dropdown from Find & Select and click on Go To Special.
- In the Go To Special dialog box, click on the radio button for Formulas and only check the checkbox for Errors.
- Click OK.
This only shows you all the cells with errors, from which you can sort out and find the cells with #VALUE! error.
How to Fix the #VALUE! Error in Excel?
Fixing the #VALUE! error in Excel is relatively simple. All you have to do is identify the cell with the error and input the correct type of data Excel is looking for.
Let us now see some common occurrences of #VALUE! errors in Excel and how to fix them.
Case 1: When a Text Value Is Entered in the Place of a Text Value or Vise Versa
Consider an example, where you have a list of students and the marks they scored in 3 subjects. To find the total, you will have to add the marks scored in three subjects together.
When you add up the individual cells containing data, Excel throws a #VALUE! error in the resultant cell. This is because one cell (B6) contains a text value (NA) instead of a numerical value.
In case you want to bypass the cell and get the resultant total value, you can use the function SUM to ignore the text value and return only the total of the cells with the numerical value. Or, you can change the cell with an NA character into an empty cell for the addition (+) operator to ignore the empty cell.
Suggested Reads:
HOW TO FIX THE #REF ERROR IN EXCEL? 3 EASY METHODS
HOW TO FIX THE EXCEL CIRCULAR REFERENCE ERROR?
HOW TO FIX THE EXCEL SPILL ERROR? — 5 EASY FIXES
Case 2: When the Cells Are Left Blank
Blank cells are also a reason for the occurrence of #VALUE! error. There is a minute difference between empty cells and blank cells. Empty cells in Excel do not contain any characters within them. Blank cells only appear as empty cells, but might be housing a character like spaces or apostrophes, which do not show up to the user.
Consider an example where you have rows and columns of random data. Upon adding them, you find that the resultant value appears with a #VALUE! error. When you look at the cells, you don’t find any text values. However, you notice one empty cell.
Upon closer inspection, it is clear that the empty cell contains a couple of spaces, and this is what is causing the error.
To rectify this error, you can either find the empty cells manually and delete the spaces or you can use the Find and Replace option.
- Press Ctrl + H to open the Find and Replace dialog box. In the Find what: textbox, enter either a single space, double space, or an apostrophe. In the Replace with: textbox, leave the textbox empty.
- Click on Replace All.
- This converts all the blank cells to empty cells.
Note: To determine whether the current cell is an empty cell or a blank cell, you can use the LEN function or the ISBLANK function.
Case 3: When the Arguments Passed in Formula Are of a Different Type Than the Actual Type / When Operations Are Performed on Dates, but They Are Formatted as Text Values
Just like the addition operator (+) throws an error when a text value occurs, some functions also throw a #VALUE! error when an argument of a different type is passed in a function.
Consider an example, where you have the working days of a few employees in an organization. You can use the function =NETWORKDAYS to calculate the working days (weekdays) ignoring the weekends.
When you pass the data for the first three cases, the function shows the resultant number of working days. But for the fourth case, the function returns a #VALUE! error. This is because data in cell B8, in addition to containing the date value, contains the additional text. This is not the expected format for the =NETWORKDAYS function and hence it returns an error.
To rectify this error, first, you will have to find the cell causing the error and change the data to its respective format (format as date and remove additional text) to get the resultant value.
Also Read:
HOW TO ADD ERROR BARS IN EXCEL? 7 BEST METHODS
HOW TO APPLY A FORMULA TO AN ENTIRE COLUMN IN EXCEL? 6 WAYS
HOW TO USE THE CEILING FUNCTION IN EXCEL? A STEP-BY-STEP GUIDE
Frequently Asked Questions
What are the reasons for getting #VALUE! error in Excel?
The #VALUE! error appears in Excel when you input a value that is of a different type than the expected type. Some of the reasons for the #VALUE! error to occur in Excel is when the arguments passed in the formula are of a different type than the actual type, when a text value is entered in the place of a text value, or when the cells are left blank.
How to find the cells with errors in Excel?
To find cells with errors, navigate to Home. under the Editing section, click on the dropdown from Find & Select and click on Go To Special. In the dialog box, click on the Formulas radio button and only check the checkbox for Errors. Click OK. This shows you all the cells which have the error.
What is the easiest way to fix the #VALUE! error in Excel?
As the value error only appears due to the data type mismatch, the best way to fix the error is by identifying it and fixing them.
Closing Thoughts
Errors are sometimes inevitable when dealing with data in Excel. Finding the type of error and ways to fix them is easy to abate them and prevent them from recurring. It also makes you more efficient and faster.
In this article, we saw some of the reasons why value error occurs and how to fix them.
Please visit our free resources center for more high-quality guides on Excel and other Microsoft Suite applications.
Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.
Simon Sez IT has been teaching Excel and other business software for over ten years. For a low, monthly fee you can get access to 150+ IT training courses.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
