How To Fix #VALUE! Error in Excel [EASY TRICKS]

Summary – Seeing the #VALUE error while working and using formulas in Excel and wondering why you see 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 and 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:

  1. Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
  2. Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
  3. 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 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 finds characters, spaces, or text within the formulas where it expects a number.

Why I am Getting #Value! Error in Excel?

You may receive the #VALUE error if you type a wrong formula or if something is 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 depends upon the formulas and calculations. Focusing on the causes, we have listed some 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 causing #VALUE! Error. Follow the below-given tricks to remove spaces that are causing the problem.

1: Use the Find & Replace option

Here you need to go to the Cells 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, choose the cells from where you want to replace the space.
  • (If 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.

How To Fix #VALUE! Error in Excel

  • Now, you need to replace the spaces with nothing:
  • In the Find what box, you must insert the space by pressing the Spacebar key.
  • Next, in the Replace with box, you need to delete everything.
  • Finally, tap on Replace All

How To Fix #VALUE! Error in Excel

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

How To Fix #VALUE! Error in Excel

  • Select the checkbox which is not have anything next to it.

How To Fix #VALUE! Error in Excel

  • And as Excel shows the blank cells select all of them and click on Delete. This will simply clear the entire hidden characters presented in the cells.

How To Fix #VALUE! Error in Excel

  • Now again, click on the Filter arrow at the top and choose the option, Clear Filter fromExpense” to make the cells visible.

Now, if the spaces are causing the #VALUE! Error is estimated now the problem is solved and formula start showing. But if the case still sees the error, then repeat the steps 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.

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.

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 easily repairing and recovering corrupted, damaged, or inaccessible Excel files. This unique tool is used to repair multiple excel files in one repair cycle and recover the entire data in the preferred location.

* Free version of the product only previews recoverable data.

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:

g1
g2
g3
g4
g5
g6
g7

Conclusion

This article covers the whole information regarding how to solve #value! error in Excel.

Here, I tried to list the complete fixes to fix the Excel #value! Error. Make sure to follow the steps given carefully to get rid of the error.

I hope you have found this tutorial helpful. If you are facing any other issue related to this specific one, then let us know by sharing your experience on our social media FB and Twitter Page.



Priyanka is an entrepreneur & content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels.