Summary – Seeing the #VALUE error while working and using formulas in Excel and wondering why you are seeing the #VALUE! Error and how to correct this problem then this article is for you. Here follow the workarounds given to fix #VALUE! Errors in Excel.
MS Excel is a spreadsheet program that allows us to perform basic as well as complex calculations with tricky formulas.
But at times, while working in an Excel workbook, it’s quite possible to experience the #VALUE! error. If you are also getting this error message while working on the spreadsheet then this blog will help you out.
To recover corrupted Excel file data, we recommend this tool:
This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:
- Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
- Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
- Preview the repaired files and click Save File to save the files at desired location.
What is a #Value error in Excel?
As the name suggests, #Value error is nothing an Excel’s way of saying that there is something wrong with a formula you type or there is something wrong with the cells you’re referring to.
When is The #Value! Error Raised in MS Excel?
Generally, the #value error is raised in Excel when it’s finding characters, spaces or text within the formulas where it’s expecting a number.
Why I am Getting #Value! Error in Excel?
You may receive the #VALUE error if you type a wrong formula or if there is something wrong with the cell. However, there are some other unexpected causes that may lead to this error.
Below, has a look at some of the common ones:
- Using the text in the Arithmetic Operations
- When the dates are stored as text
- If the cells remain blank
- The array formula isn’t closed properly
- If the function expecting a number is the text value
Well, it’s very hard to find an exact cause of this error as it totally depends upon the formulas and calculations. Focusing on the causes, here we have listed some of the workable techniques to fix #value error in Excel.
How To Solve #Value! Error In Excel?
Technique #1: Replace the Spaces
You may receive the #value error if the cells contain the spaces. Because of these hidden spaces, the cell looks empty which is not actual.
So here you need to remove the spaces which are causing #VALUE! Error. Follow the below-given tricks to remove spaces which are causing the problem.
1: Use the Find & Replace option
Here you need to go to the Cells which is showing the formula and then choose them. Moreover, you can remove the space by selecting the entire column as this allows you to remove the entire space and then follow the steps given:
- First of all, choose the cells from where you want to replace the space.
- (In case, you want to replace the hidden space from all columns at once click on a column letter then select entire columns).
- After that, tap on the Home tab then -> Find & Select -> Replace.
- Now, you need to replace the spaces with nothing:
- In the Find what box, you have to insert the space by pressing the Spacebar key.
- Next, in the Replace with box, you need to delete everything.
- Finally, tap on Replace All
In case, you wish to replace the space from the single column:
- You need to tap on Find Next
- Now, instead of clicking on Replace All, tap on Replace option.
2: Set Filter in Excel
In some cases, hidden characters rather than the space can make the cell appear blank, whereas it is not blank in reality. So, to remove the characters in the column easily you need to turn on the filters by heading to the Home then click on Sort & Filter and lastly click on Filter.
- And then click on filter arrow > remove the Select All tick mark and after that choose Blanks box
- Select the checkbox which is not have anything next to it.
- And as Excel show the blank cells select all of them and click on Delete This will simply clear entire hidden characters presented in the cells.
- Now again click on the Filter arrow at the top and choose the option Clear Filter from “Expense” to make the cells visible.
Now if the spaces are causing the #VALUE! Error then it is estimated now the problem is solved and formula start showing. But if the case still sees the error then repeat the steps given for those cells.
Technique #2: Check for Special Characters & Text
If there are some special characters or any text within the cell, it can cause excel #VALUE! Error. However, sometimes it’s difficult for us to find which cells contain special cells and texts.
In order to inspect the cells, you can use ISTEXT function. But, you must note a thing, ISTEXT will not solve this error, it will only help you to find the cells which might cause this issue.
Technique #3: Check the Dates If it stored as Text
Many times, the dates stored on your Excel workbook are considered invalid. This is because; the dates are stored in the form of text.
Just as an example – An EDATE function, is specially used for calculating the expiration date 3 months after the purchase date.
The formula given in C3 returns a #VALUE! error as the date in the B3 is formatted as text (i.e. it’s not recognized as a date)
Best Way to Repair Corrupted Excel Workbook
If meanwhile performing the task or fixing Excel errors unfortunately you lose your Excel data then you can use Excel File Recovery Tool.
This professional tool is best for easy repair and recovery of corrupted, damaged, or inaccessible Excel files. This is a unique tool to repair multiple excel files in one repair cycle and recovers the entire data in the preferred location.
It is well capable of fixing Excel file corruption issues and various errors frequently encountered by users. It allows us to easily restore entire data including the charts, worksheet properties cell comments, and other data. You can try this tool’s free demo version.
Steps to Utilize Excel File Recovery Tool:
This article covers the whole information regarding how to solve #value! error in Excel.
Here, I tried my best to list down the complete fixes to fix Excel #value! Error. Make sure to follow the steps given carefully to get rid of the error.