3

I have an Excel workbook with raw numeric data that probably originated as FLOAT data in a database. Looking at the xl\worksheets\sheet1.xml doc inside the XLSX shows values like this (abbreviated to just the column of interest);

<c r="E2" s="3"><v>428315</v></c>
<c r="E3" s="3"><v>0</v></c>
<c r="E4" s="3"><v>3167839.84</v></c>
<c r="E5" s="3"><v>3050037.97</v></c>
<c r="E6" s="3"><v>2926514.28</v></c>
<c r="E7" s="3"><v>2800695.64</v></c>
<c r="E8" s="3"><v>2668935.5699999998</v></c>
<c r="E9" s="3"><v>2534727.5299999998</v></c>
<c r="E10" s="3"><v>2394350.12</v></c>
<c r="E11" s="3"><v>2251364.64</v></c>
<c r="E12" s="3"><v>2101973.2000000002</v></c>
<c r="E13" s="3"><v>1949806.21</v></c>
<c r="E14" s="3"><v>1790987.69</v></c>
<c r="E15" s="3"><v>1629218.48</v></c>
<c r="E16" s="3"><v>1460542.84</v></c>
<c r="E17" s="3"><v>1288733.3799999999</v></c>
<c r="E18" s="3"><v>1109752.94</v></c>
<c r="E19" s="3"><v>0</v></c>
<c r="E20" s="3"><v>182305.72</v></c>

Some of these values are clearly imprecise, i.e. 2668935.5699999998 instead of 2668935.57 in cell E8. When I look at the worksheet in Excel how do I make Excel display the raw data, i.e. the full imprecise value and not the rounded value? I've tried all of the standard format categories, none of which do what I want.

The reason for wanting to display raw data is that when Excel sums the values it uses the raw data, not the displayed figure, so differences arise. I'm aware of the Precision as Displayed setting, but this isn't what I want - the SUM is correct, I just want the screen to reflect what is being summed.

Edit I've created a sample file to demonstrate the issue. This file includes the raw data above and a few more rows for good measure. If you can't see the raw data (i.e. the value 2668935.5699999998 in cell A8) then look at sheet1.xml inside the xlsx to confirm it really is there. If you don't know about the XLSX file format then start here.

Improve this question
8
  • I would format the cells in the column as Number with 10 decimal places. It sounds like you may have tried that, so please explain how that doesn't "do what you want".
    – Bandersnatch
    Commented Jul 25, 2017 at 15:08
  • @Bandersnatch Using Number with any number of decimal places does not display the raw data, it displays a rounded value. For example, cell E8 displays 2668935.57 instead of 2668935.5699999998.
    – Rhys Jones
    Commented Jul 25, 2017 at 15:12
  • That can't be. It should at least display 2668935.5700000000. Could you send me a spreadsheet with a partial column of that data? [email protected]
    – Bandersnatch
    Commented Jul 25, 2017 at 15:18
  • 1
    That is because Excel only stores 15 digits and then rounds whats left. If you want to "see" the numbers you will need to store it as text, but that opens up other problems.
    – Scott Craner
    Commented Jul 25, 2017 at 15:22
  • @Scott, that's correct, but he should still see e.g. 2668935.5700000. Maybe that's what he meant. Rhys? And here is an explanation of Excel's precision
    – Bandersnatch
    Commented Jul 25, 2017 at 15:36

1 Answer 1

Reset to default
1

Try inserting a apostrephe (') in the beginning of the cell, this will show you the raw data within the cell.

You could also round each term before summing it using the Round function.

Improve this answer
7
  • Editing the cell does not expose the raw data, it only exposes the rounded values, i.e. 2668935.57 instead of 2668935.5699999998.
    – Rhys Jones
    Commented Jul 25, 2017 at 18:25
  • Mine also shows only ...35.57, but when I insert the apostrophe, it shows the full number.
    – wizlog
    Commented Jul 25, 2017 at 18:26
  • (edited my comment)
    – wizlog
    Commented Jul 25, 2017 at 18:29
  • I tried, but I need a username and password to access it?
    – wizlog
    Commented Jul 25, 2017 at 20:00
  • I was able to open the file, and I tried adding the apostrophe. I think that forces Excel to interpret the cell as text. In any case, doing that did not show any hidden decimals, it just dropped the trailing zeros that were showing because I had formatted the column as Number with 10 decimal places. Here is what the apostrophe gave: '2668935.57
    – Bandersnatch
    Commented Jul 26, 2017 at 2:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .