Excel allows users to perform not only basic calculations but complex ones too, with tricky and confusing formulas which you may need to implement following up the need.

When you work in Excel, it is possible that you might come to face the #VALUE! Error in one of the cells of the Excel Workbook.

Article Includes:

  1. Blank Cells Return #VALUE! Error
  2. Replace Spaces Causing #VALUE! Error
  3. Locate the Source of Error
  4. Check Data Connection

When there is something wrong with the typed formula or with the cells you are calling for maths, this is when Excel would throw the #VALUE! Error.

However, it’s not easy to track the exact cause, as it depends on the formula or calculation which you performed or hidden cell values which you might consider to be blank.

In this article, we’ll walk through a few situations professing the common #VALUE! Error scenarios and the possible solution.

Blank Cells Return #VALUE! Error

You may encounter the #VALUE Error on applying a specific formula in Excel, and this is because of one of the cells which contains some value but is hidden. It happens when you unknowingly press Spacebar while dealing with the cell value, pressing the space bar hides the cell value.

How to check if the Cell is Blank or Has some value?
To check if a cell is blank or has some value:

  1. Select the cell with hidden value.
  2. Press F2.
    Press F2

On pressing the F2 function key, you can see that the cursor adjacent to the cell’s left side is a little towards the right side. The cursor in a blank cell won’t show that millimeters of space, the difference is shown above.

Note: Alternately, you can use ISBLANK worksheet function to check if the cell is blank or vice-versa.

Example, formula = ISBLANK(C2) will return True if the cell is blank or False if it is not.

Replace Spaces causing #VALUE Error

When the cell contains spaces or hidden spaces, Excel would throw #VALUE! Error. Because of the spaces, it looks like a blank cell, but it is not actually.

To replace spaces or hidden spaces from some or all of the cells follow the steps below:

Before proceeding any further, select the cell or cells you are referencing to by the formula.

  1. Select the cells you wish to replace space from.
    Select the cells you wish to replace space from

Note: If you wish, you can replace space from one or the whole column at once. Clicking on the column letter selects the entire column.

  1. Click Home tab > click Find & Select > click Replace.
    Click Home tab click Find & Select click Replace
  2. In this step, replace spaces with nothing:
    • In Find what box, insert a space (press Spacebar once).
    • In Replace with box, delete everything.
      Replace with box, delete everything
    • Click Replace All.

Note: If you want to replace space from columns individually,

  1. Click on Find Next (instead on Replace All), and now click Replace.

Locate the Source of Error

In Microsoft Excel, you can try to locate the error by evaluating the formula you put. The Evaluate Formula utility will examine it, and you can see the result upon completion. With Evaluate Formula you can check for both normal spaces and hidden spaces.

  1. Select the worksheet cell showing the #VALUE! Error first,
    Select the worksheet
  2. Click Formulas > click Evaluate Formula.
    Click Formulas click Evaluate Formula
  3. Click Evaluate.
    As you can see the cell E2 has hidden space quoted within the double quotes, but you can’t find this by normally looking at the cell.
    Click Evaluate

Note: Upon evaluating your formula, you can find out the spaces residing in the worksheet cells.

Check Data Connection

If you’re working on a Worksheet online which is created by someone else, you may face the #VALUE! Error data connection stability being the reason. If the connection is loose, the worksheet won’t be retrieving the formulas and cell values.

Data Connection restoration or asking the creator to make a new worksheet file are the possible solutions. The creator can prepare a new worksheet by copying all the cells to a new file, pasting only as values.

Follow steps below to paste cell data as values:

steps below to paste cell data as values

Excel Repair

Excel Repair is an absolute companion for dealing with corrupt, damaged, inaccessible Excel worksheet file(s). Excel Recovery software lets you repair corrupt excel file(s) and recover data in the original hierarchy in two recovery mode choices available. No technical expertise is necessary to engage with the tool as Excel Repair’s GUI is simple & user-friendly.

Excel Repair

Conclusion

Working on Excel worksheets can be tricky and frustrating if you are facing errors like #VALUE! Error one after the other. There could be numerous reasons for the #VALUE! Error to pop up on the screen, and you would need to either fix your formula or the calculation to sort the issue out, also you can try locating the #VALUE Error source.

Download Now