Strange Value Results in a Cell

Written by Allen Wyatt (last updated August 17, 2019)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


Lynn uses Excel to keep track of her checkbook. She has the cells in the worksheet formatted to show values like $1,234.56. Sometimes, but not always, if Lynn changes the formatting of a cell it may look like 1234.56000000001. This seems like an error to her and she finds it annoying, so she is wondering why the figures look this way at times.

Lynn has inadvertently discovered the strange, wonderful world of Excel math. You see, when you do math in an Excel formula, because of the way that Excel does the calculations internally, you could easily end up with a rounding error at the most extreme ends of the calculation. (Excel maintains internal precision to 15 significant digits, which is why Lynn is seeing the "error" out at the very furthest reaches of her number.)

In routine calculations—like the ones being done by Lynn—this typically isn't a problem. In her usage, she only cares about numbers being correct to a precision of two decimal places. When the "error" is in the eleventh decimal place, it isn't going to affect the validity of what Lynn sees. It would only affect the results if a particular result relied upon thousands and thousands and thousands of previous calculations. This is the only way that the "error" could compound to the point where it affects Lynn's results.

In day to day usage, this shouldn't be an issue. The "error" is hidden easily by using a cell format such as Currency or Accounting that displays values, so they show only two decimal places.

If the rounding issues bug you a lot, the easiest solution is to force your formulas to round to the number of decimal places you want. For instance, let's say you have a very simple formula such as the following:


You could "wrap" the formula in the ROUND function, in this manner:


This forces the result returned by Excel to two decimal places, which is what Lynn would expect to see if she removes the cell formatting.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13663) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Mixed Page Number Formats in a TOC

When you create a Table of Contents for a document that contains multiple sections, the page numbers in that TOC should ...

Discover More

Last Non-Zero Value in a Row

If you have a lot of values in a single row, you might want to pull the last non-zero value from that row. There are a ...

Discover More

Reference to a Range of Endnotes

When multiple endnote references are used at a given point in your document, you may wonder if there is a way to compress ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

How Operators are Evaluated

Operators are used in formulas to instruct Excel what to do to arrive at a result. Not all operators are evaluated in the ...

Discover More

Shortening ZIP Codes

US ZIP Codes can be of two varieties: five-digits or nine-digits. Here's how to convert longer ZIP Codes to the shorter ...

Discover More

Calculating Monthly Interest Charges

Trying to calculate how much people owe you? If you charge interest or service charges on past-due accounts, there are a ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three more than 9?

2019-08-17 21:34:27


RH: Great tip!!

However when I applied it (unless I did something wrong) I immediately realized your instructions only reset this setting on the current Workbook you are working in; it is not a universal change.

In order to affect all of a user's new Excel files by default they must locate and make your recommended setting change in all of their template files (i.e. where they want this change to occur) and save them as their default template.

And as anybody unfamiliar with template files will find out, making a universal change in the default template file(s) can be a challenge.

So one should first search Allen's Excel Tips website for Template Changing instructions.

2019-08-17 08:54:17

Lynn Gates

Thanks for the rounding tip...I had stumble on the solution after I sent the email for help. I do appreciate you looking into it.


PS 72+ years ago when I was born, my parents named me Lynn...a name that is shared with many females...however I was born a male and have been all my life. Interestingly enough in my high school class there was another male named Linn. Granted not a common name...but nicer than what I was called by some people I arrested while being a policeman.


2019-08-17 05:20:22


Another fix is to click on the File tab, go down the left side menu and select options.
Select the Advanced option, scroll down to almost the bottom of the window and check "Set Precision As Displayed".
Click the ok button on the popup screen that states "Data will permanently lose accuracy". This will not adversely affect your calculations when you are only going out to less than 10 decimal points and as an added bonus it will eliminate the annoying negative zero.
The other benefit is that you will not have to use the round formula for each cell, it will automatically adjust all cells.

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.