If you’ve ever entered a long number into Excel, only to see it shortened to something like 1.79E+21, you’re not alone!
This happens because Excel automatically formats large numbers using scientific notation. This can be a major problem, especially when dealing with tracking numbers, order IDs, or other precise numerical data. Fortunately, there’s an easy fix! Follow the steps below to ensure your numbers display correctly.
Topics
How to format Excel cells as numbers
1. Select the cell(s) containing the numbers.
2. Click on Format -> Cells from the top menu. Notice that Scientific is chosen by default.
3. In the Format Cells window, choose Number instead of Scientific.
- This ensures long numbers are stored and displayed correctly without being converted into scientific notation.
4. Set Decimal places to 0 and click OK to Save.
Why are there trailing zeros after reformatting?
After reformatting, you might notice that Excel replaces some digits with trailing zeros. For example:
- Original value: 17897870708707312121212
- Incorrectly formatted value: 17897870708707300000000
This occurs because Excel has a built-in limit for numerical precision and may round large numbers. The original data is lost once a file has been opened in Excel and reformatted incorrectly.
Here’s how to prevent this:
- Start with a fresh file
- If you’ve already opened the file in Excel, delete it and download a new copy.
- Use a different application first
- Open the file in Google Sheets or Numbers (Mac) instead of Excel.
- Format the column as Number
- Follow the formatting steps above before making any edits.
- Export the file as an Excel spreadsheet
- Download it in .xlsx format to preserve the correct values.
- Then, open the file in Excel. The long numbers should now display correctly.
- Be sure to save your work after verifying the data.
- Contact Ordoro Support
- If you need further assistance, please feel free to email support@ordoro.com.
