Excel #value Error: Make it Go Away!

One of the most frustrating things you can experience with Microsoft Excel (or any program for that matter) is when you get an error message that just doesn’t seem to make sense. You’ve plugged in your formula; you’re reasonably sure everything in it is precise; and still, instead of the data you want to see returned, you get these six characters: #value. Bafflingly, you might only have it in certain cells, whereas others look just fine.

Well, to really get on top of what causes this malfunction, and others like it in Excel, you might want to take a course covering Excel from beginner to advanced level. This should help you get a grasp on all of its features and teach you to overcome its idiosyncrasies.

For now, though, you have a specific problem you want to solve, so let’s take a look at some causes and solutions for the #value error

The fundamental logic of a #value error

The reason Microsoft tagged your formula as an error is simply this:  it tried to perform a numerical calculation on cells that included a text value. So somewhere in the data you are trying to crunch, Excel is finding text, and it doesn’t know what to do with it.

Now, that could, on its own, give you the answer you need, or you could still be scratching your head on this.  So why don’t we continue with some examples of what could have gone wrong? It could be that you are misunderstanding what a formula or function can do. Or you might need to look closely at the data to find some common, hidden problems.

The most obvious causes for #value (and some suggestions if this is your issue)

If you are fairly new to Excel, it’s quite possible you are making a mistake like the one described here. If that’s the case, don’t feel bad. In fact, it’s good news that this is easy to spot and take care of, and you will come away with a better understanding of Excel. Then if you want to get a better handle on the program, you can start learning from seasoned instructors in an introductory Excel course.

Take a look at the following table:

Here, we have a hypothetical sales chart for a music store covering the sales of three top items in the first business quarter. For the sake of this example, we are adding sales totals for each item to column F, which simply displays the sum for each instrument.

The first “totals” cell is already filled out, and you can see in the formula bar that the method for calculating the total was to add each cell from C4 to E4. Note: Excel veterans will know there is a better method for doing this, using the sum function, but for this demonstration, it’s best to use this approach. Here we get our result of 10, which is correct. No issue.

Now let’s move to the next row and calculate the total for violins:

Oops! Now we have our value error. And you may spot right away what the issue is: we included cell B5 in the calculation.  This means that Excel tried to perform the calculation 2 + 3 + 3 + Violins, and it didn’t know what to do with the text input.

You will want to note a few things about this. First, I mentioned the SUM function as another way to perform this calculation. And if you use that function to perform this calculation, it will ignore the information in the B column, essentially treating it as a zero. Second, if you set up a calculation like this, it’s likely you did it by mistake, in which case you should now understand why you got your error. And finally, it could be that you are trying to combine text and numerical elements and are going about it the wrong way.

For those who are stuck on this third point, here is some guidance.  Let’s return, once again to our formula, assuming we want to add the units to the totals column. There is most certainly a way to do this, but we have to use a special function to combine the text and numeric values.  Take a look:

The function in the formula bar may look like Greek to you, but if automatically combining text and numeric elements is something you want to learn to do, then you will need to get acquainted with the concatenate function. Here, the function is combining the sum ot the items sold (Sum(C6:E6)), a space between the numbers and the text (“ “) and the text in cell B6.

If you are interested in learning to save time with functions like concatenate, you may want to take a course covering the advanced features of Excel.

When #value is tricky

So you don’t think you added a text element to a calculation you were trying to process and you are still wondering why you’re getting a #value error. This is certainly not uncommon, and most of the time, it is because Excel is reading data that is supposed to be numeric, but is, in fact, entered as text. And usually, this is because there are extra spaces inserted in some of your cells.

Most often, Excel will auto-correct extra space if you have entered a number. However, in cases of a blank cell, which you want the program to treat as a zero, you need to make sure there is no extra space inserted.

Fortunately, the solution is simple. You can filter for your blank cells, highlight them, and delete their contents.  If that is not a process you are familiar with, it may be to your advantage to look in to a beginning level course on Excel that will take you through these operations.

Hopefully, after reading this short guide, you know what caused your #value error and how to fix for it, both in the present situation and in future instances. If you feel a little lost, don’t worry. Take a look at some of the recommendations for further guidance, and before you know it, you can be a master of Excel.