Resolving #VALUE! Errors in Microsoft Excel

Paperwork
cmcderm1_iStock_paperwork
Share this content
Tags

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 Apple

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

BONUS: If you register now you can opt to receive a digital copy of "Transform!" , Richard Francis' new book for growing firms [US/Canada ONLY].

About David Ringstrom, CPA

David Ringstrom

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.

Replies

Please login or register to join the discussion.

avatar
By jim erickson
Jun 26th 2015 01:11

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 (0)
avatar
By David Ringstrom
to Caleb Newquist
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By Rick
Jun 26th 2015 01:11

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 (0)
avatar
By CJ
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By David Ringstrom
to Rowan Webb
Jun 26th 2015 01:11

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.

Thanks (0)
avatar
By lee
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By David Ringstrom
to fstitely
Jun 26th 2015 01:11

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.

Thanks (0)
avatar
By sangeen
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By Kishan
Jun 26th 2015 01:11

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.

Thanks (0)
avatar
By GoodWolf
Jun 26th 2015 01:11

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!

Thanks (0)
avatar
By GoodWolf
to mobileaccountantaz
Jun 26th 2015 01:11

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 :-)

Thanks (0)
avatar
By David Ringstrom
to clare.smith
Jun 26th 2015 01:11

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.

Thanks (0)
avatar
By tie
Jun 26th 2015 01:11

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?

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

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.

Thanks (0)
avatar
By Kristen
Jun 26th 2015 01:12

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?

Thanks (0)
avatar
Jan 5th 2016 11:02

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.

Thanks (0)
avatar
Jun 28th 2017 15:07

Hey I found a new thing about it that when you type 1,42,000 instead of 142,000 (Look at the commas i have put in different way) shows up the error named #VALUE!
Firstly Some people, mostly Asian (I'm an Asian Too) Type in there writing format just like as 1,42,000 but Microsoft is not an Asian country so excel understands only the 142,000 type of format .

Thanks (0)