# Maintaining Accuracy of Significant Digits

Written by Allen Wyatt (last updated October 6, 2018)
This tip applies to Excel 97, 2000, 2002, and 2003

Ken asked if it is possible to maintain the number of significant digits on values entered into Excel. For instance, "12.345600" and "1.230".

The short answer, Ken, is that you cannot. Why? Because Excel always transforms everything to 15 significant digits internally. This, if you enter 12 or 12.0 or 12.00000, Excel always considers these the same as 12.0000000000000, even though this may not be technically correct in the truest mathematical sense. Further, since Excel converts everything to 15 significant digits, it truncates any trailing zeros in any numbers displayed using the General number format.

(It should be obvious from the above explanation that the terms "significant digits," "precision," and "number of decimal places" mean entirely different things. For instance, the number 12.000 has three decimal places, but has five significant digits. (I'm sure the math theorists in the audience will be all too glad to correct me if I am wrong.)

You can use a custom numeric format for individual cells, which will display your information using the proper number of significant digits, but this will not affect how Excel works internally. For instance, you could format a cell to display 12 or 12.0 or 12.00000, but this does not change the fact that Excel still considers all the numbers to be 12.0000000000000.

One way around this problem is to enter your numbers as text, by putting an apostrophe in front of them. In other words, instead of entering 12.0 you would enter '12.0. Excel then parses the entry as text (because of the apostrophe) and displays 12.0 in the cell. The only drawback to this approach is that you cannot directly reference the cell in all mathematical functions; some of them will return an error because you are including what Excel considers as text in the function. The easiest way to handle this is to make sure you include the =VALUE function as part of your overall formula. For instance, if you enter a number as text into cell B7, and then need to reference it in a formula, you could do it in this way:

```=VALUE(B7) * 100
```

This works great for formulas with single-cell references. It does not work well for range references, however. If you need to do a sum or an average on a range of values entered as text, Excel will choke every time, returning an error based on what the function is trying to do.

This brings us to the second potential solution: maintaining either the significant digits or the number of decimal places separate from the actual value. For many technical mathematical purposes, this is the best way to handle the situation. For example, in column C you could enter your numbers, as numbers. Excel, of course, converts them internally to 15 significant digits. In column D you could enter various numbers that represent the number of decimal places you want applied to the values in column C. You could then use the values in column D to help in formulas that refer to the values in column C.

A final potential solution is to simply format each individual cell so that it shows only the number of decimal places that you want displayed. With the caveat noted above (that such formatting does not affect the internal representation of numbers by Excel), this might be the best solution. Of course, individually adjusting the display formatting of a couple of cells is easy, but doing so to a hundred or five hundred cells is a different story. This is where a macro can come in handy. Consider the following macro:

```Sub SigPlaces()
Dim c As Range
Dim strcell As String, NumFormat As String, DecSep As String
Dim DecPtLoc As Integer, stringLen As Integer
Dim numDecPlaces As Long

DecSep = "."

For Each c In Selection.Cells
strcell = c.Value
If IsNumeric(strcell) Then
NumFormat = "#0"
DecPtLoc = InStr(strcell, DecSep)
If DecPtLoc > 0 Then
NumFormat = NumFormat & DecSep
stringLen = Len(strcell)
numDecPlaces = stringLen - DecPtLoc
NumFormat = NumFormat & String(numDecPlaces, "0")
End If
c.Value = Val(strcell)
c.NumberFormat = NumFormat
End If
Next c
End Sub
```

If you enter your numbers as text (with the apostrophe, mentioned above), you can then select the cells and run this macro. The text entries are converted to numbers, and the formatting applied to those cells is changed so that the same number of decimal places is displayed as you entered in the text entry.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2045) applies to Microsoft Excel 97, 2000, 2002, and 2003.

##### 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. ...

##### MORE FROM ALLEN

Selecting Tabs in Dialog Boxes

Dialog boxes normally present information in a series of tabs. If you want to move from tab to tab without taking your ...

Discover More

Specifying a Browser in a Hyperlink

Excel allows you to easily add hyperlinks to a worksheet. Click on it, and the target of the link is opened in a browser ...

Discover More

Jumping to the End of Page after Enter

Imagine you start typing in a new document, and when you press the Enter key the cursor jumps a huge distance to the ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

##### More ExcelTips (menu)

Clearing Large Clipboard Entries

Need to clear out a large amount of information saved on the Clipboard? All you need to do is to replace it with a small ...

Discover More

Zooming with the Keyboard

Excel doesn't provide a keyboard shortcut that allows you to zoom in or out on your workbook. It is easy, however, to ...

Discover More

Setting the Calculation Default

Excel can recalculate your worksheets either automatically or manually. The default is to calculate them automatically, ...

Discover More
##### Subscribe

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 4 - 1?

2018-10-06 15:17:44

Rick Rothstein

You can ignore the <pre> and </pre> tags I placed around my code... I looked at the source code for this webpage and saw those tags around the code Allen post in his article and hoped they would work in my comment (they didn't, obviously).

2018-10-06 15:13:54

Rick Rothstein

I note three problems with your SigPlaces macro... (1) if the value starts with a \$ sign (my currency symbol), the cell value becomes 0, (2) if the value contains a comma (my thousands separator), the cell value becomes 1 and (3) if the value is in scientific notation, that notation is thrown away if the value can be displayed without the "E" notation. Below is a macro that I have written which I believe handles all numeric text value (including the above three problems) correctly. The only thing is handle slightly differently than I would like is if the cell value is in scientific notation without a plus or minus sign in front of the exponent, '1.230E4 for instance... my code adds the plus or minus symbol.

<pre>Sub FormatToSignificantPlaces()
Dim X As Long, Cell As Range, Txt As String
For Each Cell In Selection
If IsNumeric(Cell.Value) Then
Txt = Cell.Text
For X = Len(Txt) To 1 Step -1
If Mid(Txt, X, 1) Like "#" Then
Mid(Txt, X) = "0"
ElseIf Mid(Txt, X, 1) Like "[!.,E\$+-]" Then
Txt = Application.Replace(Txt, X, 0, "\")
End If
Next
Cell.Value = Cell.Value
Cell.NumberFormat = Replace(Txt, "E0", "E+0")
End If
Next
End Sub
</pre>

##### This Site

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