Excel drops off values after saving the file

Question

I have an MS Excel file that if I open and "save as" or "Save" it truncates (or I guess you can say cuts off) decimal values that are not being displayed in the cells.

For example, if the cell has a value displaying 28.74% but in the formula bar I have "28.7377125382263%".

So, in this case, "IF" I were to "SAVE" or "SAVE AS" this file it will save with only two decimals (28.74%), instead carrying out to it's original values (28.7377125382263%) with 13 decimals.

Is there something in the setting causing this? Also, FYSA - the file type is CSV (Microsoft Comma Separated Values File) and using Office 365.

Answer 1

In the case of .xlsx (or .xls, .xlsb) files there are a lot of additional information (non-visible digits, formula, formatting, page settings ...) saved together with the information you can see on the screen.

However, for .csv files, there is nothing else saved besides what you see in the cells (same case for .txt too). That's by design and no way to change it.

You've two options:

• make sure enough number of decimals displayed before saving
• use different file format

Answer 2

Reformat the Numbers as General or Text

If you reformat all the affected numbers as General prior to resaving to CSV, Excel will save the underlying decimal number, without any rounding, or clipping. A second approach converting the numbers to strings is necessary if you want to save numbers with the percentage symbol.

Sample Data

I used the following sample data in A1:A5

	A
1	Percentage
2	34.56789%
3	10.987654321%
4	12.1110098%
5	45.67890987%

In Excel, I then reduced the number format to display only 2 decimal places.

	A
1	Percentage
2	34.57%
3	10.99%
4	12.11%
5	45.68%

Approach 1:   Save Decimal Values

If you are okay with returning the stored decimal values in the CSV, then simply applying the GENERAL formatting to any affected numbers will save the underlying decimal numbers in the CSV without rounding or clipping. This means however that 10.523% would be saved as its decimal equivalent 0.10523.

In my Excel table, B2:B5 is a duplicate of A2:A5 but then formatted as GENERAL

Approach 2:   Save Percentage Signs

If you want 10.523% saved as 10.523% then you can instead apply a formula to the affected range(s): =range*100&"%" If you convert the values in this way there is no longer any need to change the formatting as they are now strings.

In my Excel table, C2:C5 is returned by placing =A2:A5*100&"%" in cell C2

Excel's Actual CSV Output

Percentage,General Format,Formula
34.57%,0.3456789,34.56789%
10.99%,0.109876543,10.987654321%
12.11%,0.121110098,12.1110098%
45.68%,0.456789099,45.67890987%

Manually Aligned Output

Reformatted CSV output to be TAB-ish delimited for legibility

Percentage   General Format   Formula
34.57%       0.3456789        34.56789%
10.99%       0.109876543      10.987654321%
12.11%       0.121110098      12.1110098%
45.68%       0.456789099      45.67890987%