1

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.

1
  • 1
    Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Feb 3, 2022 at 19:44

2 Answers 2

3

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
2
  • Yes, the file needs to be in CSV. So, I see know that based on your finding CSV will only save what is being displayed on the screen. CSV file is generated by 3rd party 'add-on' tool to MS Project and we need the decimals. So, we will have to see how we can find work around it. Thank you for the response and sharing your knowledge. Feb 3, 2022 at 21:56
  • "nothing else saved besides what you see in the cells" That is not entirely accurate. It is really is based on the formatting of the cell. A cell formatted as 'General' might still show a number rounded because of a lack of space, however, the number will not be rounded or clipped when saved to CSV. Also, a text string (number or otherwise) might be partially hidden because of a lack of column width but will save unclipped to a CSV. Also, hidden columns and rows will still be saved in the CSV
    – Blindspots
    Jun 2, 2023 at 21:22
0

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%

You must log in to answer this question.

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