May 31st 2013

21

*By David Ringstrom, CPA*

It's a frustrating experience when a simple Excel spreadsheet displays #VALUE! in a worksheet cell rather than the expected result. Many times the problem is obvious, in that you've tried to do arithmetic using text and numbers, but sometimes the culprit is harder to track down.

As shown in Figure 1, the formula =C2/A2 returns #VALUE! because I purposely mistyped the formula and attempted to divide the value 5000 in cell C2 by the word Apples in cell A2. The same error would have appeared if I were to try to divide cell A2 by C2, or add or subtract one of those cells from the other. With that said, I could sum cells A2 through C2, as in =SUM(A2:C2), and the result would be 5500 and not #VALUE.

**Figure 1:**Dividing a number by a cell that contains text will return a #VALUE! error.

Now, let's change the scenario a little and assume that in cell D2 we have the formula =C2/B2, but cell C2 is blank. In this case, Excel should return 0, but you may still encounter a #VALUE! error as shown in Figure 2. The reason is that cell C2 may not be truly blank. Users, either on purpose or unintentionally, erase values by tapping the spacebar. Insidiously, this makes it appear as if a cell is blank when in actuality it isn't. Before we explore this further, keep in mind that if cell B2 is blank but C2 contains a number, then the aforementioned =C2/B2 will return a #DIV/0! error, which signifies division by zero.

**Figure 2:**Although cell C2 looks blank in both examples, Excel returns a #VALUE! error if the cell isn't actually blank.

Sharp-eyed users can press F2 within a cell and then make note of where the blinking cursor is, as shown in Figure 3. If the cursor is adjacent to the left border of the cell, the cell is most likely blank. However, if the cursor is a couple of millimeters to the right, then there's a space. To categorically check, click once on a cell, and then press the Delete key.

**Figure 3:**Zoomed-in view of individual worksheet cells contrasts empty vs. non-empty worksheet cells.

You can also use the ISBLANK worksheet function to determine whether a cell is blank or not. For instance, the formula =ISBLANK(C2) will return TRUE if cell C2 is blank or FALSE if it isn't.

Curiously, if you're doing simple arithmetic with cells that contain numbers stored as text, Excel will perform the calculation without issue. To try this out, type a single quote in cell B2, followed by a number such as 500 and then two spaces. In cell C2, enter a single quote followed by 5000 and two spaces. In cell D2, the formula =C2/B2 will return 10. Of course, if you were to try to sum the current values of cells B2 and C2, the SUM function would return 0, because worksheet functions don't generally convert text to numbers on the fly. Should you encounter numbers stored as text, the easiest solution is to use the Text to Columns wizard:

- Select one or more cells in a single column, choose Text to Columns from the Data tab or menu, and then click Finish.

This technique only allows you to convert one column at a time. If you need to convert multiple columns:

- Enter the number 1 in a blank worksheet cell and then copy it to the clipboard.
- Select the range of cells you wish to convert to values and then right-click and choose Paste Special.
- Double-click on Multiply.

This action will multiply all of the values by 1, which in turn also converts them to numeric values instead of text.

Regardless, there are other situations in Excel that can cause the #VALUE! error, such as a recalculating a linked reference to a closed workbook or including a text-based reference in a formula that's expecting a value, such as =SUM("Apples",5,500). The Microsoft website offers additional guidance as well.

Read more articles by David Ringstrom.

**About the author:**

*David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at* *[email protected]** or follow him on **Twitter**. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner **CPE Link**.*

## Replies

## Please login or register to join the discussion.

David, this resolved my issue when I had #value!.....you are a BIG star in the Excel galaxy. thanks for the clues on that "single space" gap of the cursor....Jim

Thanks, Jim! I'm always glad to help, and thank you for posing a question that served as the inspiration for this article.

The best web-suggested solution I found so far for #VALUE on simple formula calculation on MAC. The solution is to "...choose Text to Columns from the Data tab or menu…" This link should be moved up to page 1 of Google search for #VALUE! Thanks

Thanks, Rick! Your comment will help it move up in the rankings. In any case, I'm glad you found my article. Text to Columns is one of my favorite features in Excel.

i tried to put 20140502888004427 in spreadsheet but after i pressed enter it would appear as 20140514888005500 which is now different. Please help

The number you entered exceeds the level of precision that Excel can handle. Add an apostrophe at the start of your number to store it as text. You won't be able to use numbers this large with mathematical functions in Excel.

Very helpful. Had watched videos and searched other sights but this helped me find the cell that wasn't really empty-it had a period in it. Thanks much

Great feedback! When I teach Excel classes I often demonstrate looking for spaces, but hadn't thought of periods, which can be hard to find as well.

HI I have

0191631 which i formated to number but still multiplying it by one would end up #Value error

Most likely you have a trailing space in the cell that contains 0191631. Try =VALUE() instead.

Hi David, I used VALUE() and N() formulae and also tried many other things but I am still unable to convert $ values stored in text format to numbers. I have removed all other characters from the text including the "$" mark but excel is still giving me #VALUE! error when I use VALUE() function. I appreciate your help.

N() is for putting notes in the formula bar and isn't useful for cleaning data. Try using the CLEAN() function, or the Text to Columns wizard. Worst case, there's some other character masquerading as a space within your text copy a single "space" from the text and use the Find and Replace feature to replace the "space" with nothing. That should then do the trick. Otherwise, without seeing your data I don't have any other suggestions.

Hi David, thank you for your article. I'm attempting to use the "minverse" function to invert a matrix and it's resulting in the #value! error. Very weird because the matrix inverts if I do it manually plus I've set the entire worksheet to "scientific" and there are ONLY numbers on the page. Also occurs for the "mmult" function. Any ideas? I've tried everything for several days now and I'm starting to question if there's a fault within Excel... Thanks for your consideration!

I must confess that I've never had a need to use the MINVERSE or MMULT functions in Excel. Since I'm a CPA I approach Excel from an accounting standpoint. I will say that whenever I can't get Excel to do what I want, I severely pare back my data set or formula to get to a place where Excel will do what I want, then I layer in more levels. So in your case, my educated guess is to try to start with a much smaller array, say 2 or 3 numbers to get to a point where Excel returns a result, and then expand the matrix one level at a time until you work your way back to the goal you're seeking.

Many thanks, David. Tried that. It's very erratic when it works and doesn't so I suspect a bug of sorts. I've found a workaround that takes a bit longer but it's a relief to get results. Merry Xmas :-)

You could be right. Make sure that you have SP3 installed for Excel 2007, SP2 for Excel 2010, or SP1 for Excel 2013. Google Excel 20xx SPx to find the download and replace the x's with the corresponding numbers. That's always a primary rule-out when Excel seems to misbehave.

i am trying to take a number * a currancey and I get the value erro

so in say B2 is a number 17 x C2 is a currency $600 why won't that work?

It's hard to say without seeing your spreadsheet first hand. I'd start by using this formula =ISNUMBER(C2) to make sure that cell C2 is truly a number and not a number masquerading as text. Do the same for cell B2. Worksheet cells sometimes contain hidden characters that make numbers be treated as words.

As an addendum to my article, a reader today sent me a spreadsheet that was returning #VALUE!. In Excel 2007 and later you can click on the cell that contains the error, and then on the Formulas tab click the arrow adjacent to Error Checking and then choose Trace Error. In the reader's case he had 4 cells that had dashes instead of zeros that were causing the issue with his spreadsheet.

I have a spreadsheet with multiple tabs and every single cell with text in it turned into #VALUE! on every single tab. How can I convert it back to the words?

This is such a common issue with running balances, where the column to the left of a table or the first row above a column of numbers, contains some text.

Suppose you have the following table:

month debit credit balance

jan 100 200 100

feb 100 200 200

The value in the balance column is meant to be the sum of credits less the sum of debits. Without using ranges, and just sticking to cell values, in d3 you'd put =d2 + c3 - b3.

The problem is, how do you put the same formula in d2 and d3 so that you don't encounter the #value error in d2 (which will be repeated throughout subsequent rows). The simplist way I've found to achieve this is the following readable formula:

=IFERROR(d2+0,0) + c3 - b3

The beauty here is that anything in d2 which does not behave like a number (it can be a text string, a series of spaces or a blank) is just converted to zero, so the formula always returns an intuitively correct result. I hope this helps others.