scientific notation

Question

i have scientific notation in excel how to fix it & save

Answer 1

Hello @Julie Paul

Based on the information you have provided, I understand that you are experiencing an issue related to scientific notation in Excel.

To assist you more effectively, could you please provide a screenshot showing the current notation displayed in Excel, along with a screenshot or example of the expected notation you would like to achieve?

Additionally, are you using Excel on the web or the Excel desktop application? If you are using the desktop application, please let us know which version of Microsoft Office you are currently using.

You also mentioned that only one cell has been changed. May I know which function or formatting was applied to that specific cell? Furthermore, please confirm whether you would like the same function or formatting to be applied to all other cells.

Once we have this information, we will be happy to further assist you.

Thank you, and I look forward to your response.

Answer 2

one cell changed then how can i change for 4640 rows

Answer 3

Excel uses scientific notation either because the cell is formatted as Scientific, or because very large numbers are being stored as numeric values (with a 15‑digit precision limit). To “fix it and save,” choose the approach that matches the data type.

1. If these are normal numeric values (and can be approximated)

To stop Excel from displaying scientific notation while keeping numeric behavior:

1. Select the cells that currently show values like 1.23E+10.
2. On the Home tab, in the Number group, click the small More button next to Number.
3. In Category, select Number (or another non‑scientific format such as Currency).
4. Set Decimal places to the number of digits needed.
5. Select OK.

Key points:

• The Scientific format explicitly shows E+n (for example, 1.23E+10 = 1.23 × 10¹⁰). Changing to Number or General removes that explicit scientific display.
• A number format only affects how the value is displayed; the actual value used in formulas remains the same and can be seen in the formula bar.
• With the General format, Excel automatically switches to exponential notation for large numbers (12 or more digits). To avoid that, prefer Number or another explicit format.

After changing the format, save the workbook; Excel will reopen it with the same number format and without the scientific display.

2. If these are IDs, credit cards, or any value where every digit must be preserved

Excel’s numeric engine only keeps 15 digits of precision. For numbers longer than 15 digits:

• Digits after the 15th are converted to zeros in the stored value.
• Excel may display them in scientific notation (for example, 1.23456789012345E+17).

To keep all digits exactly as typed, store these values as text, not numbers.

2.1. Before entering or pasting data

1. Select the target cells/column.
2. On the Home tab, in Number Format, choose Text.
3. Now type or paste the values.

Result:

• Excel will not convert them to scientific notation.
• Leading zeros are preserved.
• All digits beyond 15 are preserved because the value is treated as text.

This is the recommended approach for:

• Credit card numbers
• Account numbers
• Product codes
• IDs and other long codes

2.2. If the data is already in the sheet and showing scientific notation

There are two cases:

• Case A: Values are ≤ 15 digits
  • The full numeric value is still intact.
  • To stop scientific display:
    1. Select the cells.
    2. Change the format from Scientific to Number (as in section 1).
    3. If you want to treat them as text (for example, to keep leading zeros), change the format to Text first, then re‑enter or re‑import if needed.
• Case B: Values are > 15 digits
  • The extra digits beyond the 15th are already lost in the numeric value (replaced with zeros). Changing the format cannot restore them.
  • To preserve full values, the data must be re‑imported or re‑entered as Text:
    1. Format the destination column as Text before importing/pasting.
    2. Import/paste again from the original source.

The article on keeping leading zeros and large numbers explains that Excel “truncates numerical data to 15 digits of precision and converts to a number displayed in scientific notation.” To avoid this, use text formatting for such fields.

3. Special case: values containing the letter “E” (like 1e9)

Excel interprets patterns like 1e9 as scientific notation and converts them to 1.00E+09.

To keep the literal text (for example, 1e9) and avoid scientific notation:

• Format the cell as Text before typing, or
• Type an apostrophe before the value: '1e9 (the apostrophe will not show in the cell display, only in the formula bar).

4. Using Automatic Data Conversions (Microsoft 365 / Excel 2024)

For supported versions (Excel for Microsoft 365, Excel 2024), Automatic Data Conversions can be used to change Excel’s default behavior for:

• Truncating numerical data to 15 digits and converting to scientific notation.
• Converting numerical data around the letter “E” to scientific notation.

If available in the environment, configure this feature so that imported long numbers are not automatically converted to scientific notation and can be kept as text.

5. Excel for the web: extra note on long numbers

In Excel for the web, when entering numbers that are 12 digits or longer:

• Excel may automatically apply the Scientific format.
• Digits after the 15th become zeros if stored as numbers.

To keep all digits:

1. Format the cells as Text (right‑click → Format Cells → Text) before entering/pasting.
2. For long numeric strings where calculations are not needed, this is the safest way to “fix” scientific notation and save the exact value.

Once the correct format (Number for true numeric values, Text for identifiers/long codes) is applied and the data is re‑entered or re‑imported if necessary, saving the workbook will preserve the non‑scientific display and the exact stored values.

---

References:

• Display numbers in scientific (exponential) notation
• Keeping leading zeros and large numbers
• Stop automatically changing numbers to dates
• Format numbers to keep leading zeros in Excel for the web
• Number format codes in Excel for Mac