How do I make Excel display raw cell data

Question

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.

Solution

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.

Comments

[Bandersnatch]

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

[Rhys Jones]

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

[Bandersnatch]

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]

[Scott Craner]

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.

[Bandersnatch]

@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]

If I format this number to 10 decimals, I get 2668935.5699999900. That is rounded to 15 sigfigs because of Excel's precision limit, but definitely not the same as 2668935.57 or 2668935.5700000000

[Rhys Jones]

@Bandersnatch Sorry for the ambiguity, yes, I do see 2668935.5700000000.

[Bandersnatch]

Again, that can't be if the underlying data is actually 2668935.5699999998. It makes me think that the data is really 2668935.57. I don't know enough about XML to explain why it says differently, but Excel clearly thinks the number has only 2 decimal places.

[Rhys Jones]

Editing the cell does not expose the raw data, it only exposes the rounded values, i.e. 2668935.57 instead of 2668935.5699999998.

[wizlog]

Mine also shows only ...35.57, but when I insert the apostrophe, it shows the full number.

[wizlog]

(edited my comment)

[wizlog]

I tried, but I need a username and password to access it?

[Bandersnatch]

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]

But, like wizlog, if I paste the 17 sigfig number into a cell, it initially shows 3E06 and typing a leading apostrophe gives '2668935.56999999. So Excel is truncating the last 2 digits, not rounding here. Lots of clues, no answers yet.

[Bandersnatch]

Hey, maybe your numbers are somehow not connected to the XML anymore? I'm not sure how that could happen, but that's what its acting like. I'll try changing one of the numbers, saving the file, and then checking the XML. Mañana.