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 [1] as well.
A previous version of this article first appeared on www.accountingweb.com .
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 david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
