Why do some of my Excel cells show a '#VALUE!' error?

Q  I have a couple of Excel workbooks where I have a formula that relies on input from another cell. The cells containing this formula show unwanted data while the trigger cell remains blank (ie, before I type data into it).

Some cells show a ‘#VALUE!’ error, while others show incorrect results until the trigger cell contains the correct data. Why does this happen and is it possible to keep the formula-containing cells blank until the trigger cells have content?
Dave Gale

A  That’s a few questions for the price of one – and there are three answers, so here goes.

The reason some cells show incorrect results is because the formula is using performing calculations based on incomplete data. You didn’t tell us the formula but to keep it simple, let’s imagine it is =SUM(A1+B1) and it has been attached to cell C1, so that the result appears in this cell.

When the formula is entered, C1 will initially read ‘0’ (zero), because Excel considers blank cells also to contain zero – so that is the effective sum of cells A1 and B1.

However, this simple example should help you to understand why some cells in your spreadsheet are displaying the ‘incorrect’ results: Excel is in fact displaying the correct sum but as the trigger cell has yet to be populated with data, it is treated as a zero. So the sum is correct, but incomplete.

In fact, we think you already know that and simply wish to hide the incomplete result until the trigger cell is populated, and we will answer that momentarily.

However, we first need to address the second part of your question – the ‘#VALUE!’ error that appears in some cells until the trigger cell contains data.

This error occurs when the cells on which the formula is based contain values that are incompatible with the formula’s argument or operand. To put that into plain English, one or more of the cells contain something that is confusing the formula – and our bet is a space.

To a human, a space in a cell might look blank but to Excel, it looks like any other alphabetic character. So, the formula tries to tally numbers and alphabetic characters, gets confused and displays the ‘#VALUE!’ error. And we think you may have inserted the spaces in an effort to ‘blank’ the cells.

Now that the two probable causes of the problem are clear, we can offer a solution.

In fact, there are several possible solutions but, because we don’t know the precise nature of your formula, we’ll offer an example of how to keep the trigger or results cells blank until the relevant data is entered into them.

To keep it simple as possible, we’ll consider the same formula as before – =SUM(A1+B1), entered into cell C1 – but we’ll modify it to use the IF function to apply a logical test before the result is calculated.

By using the IF function, it is possible have Excel check if something is true before doing one thing or another. Combined with a second function, ISBLANK, we can use IF to check if whether one of the trigger cells contains a value: if so, the calculation can proceed; if not, the results cell will be left blank.

The formula would look like this: =IF(ISBLANK(B1),"",SUM(A1+B1)). To interpret this from left to right, it basically says: check to see if cell B1 is empty and if so, enter a null value (the pair of double quotes); otherwise perform the SUM(A1+B1) formula.

All you need to do from here is substitute the first B1 for your trigger cell reference and add your original formula for the SUM(A1+B1) part after the second comma.