how to fix #VALUE!

Question

how to fix #VALUE!

LOO XIN YI 0

my formula is typing correct and change to number / accounting , no spaces yet it continue shows error while the same format for other cells is ok

0 comments

2 answers

Your answer

Answer 1

Bob Jones AKA CyberTaz MVP 437.7K

Contrary to popular misconception, once digits are entered as Text reformatting the cell as Number does not convert the content of the cell. There are several methods you can use:

If a red triangle appears in the upper left corner of the selected cell click the adjacent ⚠️ & select Convert to Number, or
Select the cell then on the Data tab click the Text to Columns tool & click the Finish button, or
Type the number 1 into a blank cell, copy that cel then use Edit> Paste Special – Multiply

Other methods are available as well.

Bob Jones AKA CyberTaz MVP 437.7K Reputation points

2026-09-25T00:10:56.7033333+00:00

Note: You may need to use the Trace Precedents tool on the Formulas tab to identify the cell containing the non-value data. See Excel Help on the topic if you aren't familiar with the feature.

Answer 2

If the formula looks correct and the cell format is already set to Number or Accounting, the issue may be related to the value or formula being interpreted differently in that specific cell.

You can try these steps:

Check the formula in the Formula Bar and compare it character-by-character with a working cell.

Press F2, then press Enter to force Excel to recalculate the cell.

Check whether the cell is formatted as Text before entering the formula. If so, change it to General or Number, then re-enter the formula.

Try copying the formula from a working cell and adjusting the cell references.

If the error remains, please provide the exact error message (for example, #VALUE!, #NAME?, or #REF!) and the formula you're using. The specific error code will help identify the cause.