Why Excel shows numbers as scientific notation (and how to stop it)

Case 1: only the display (15 digits or fewer)

The sample sheet is a stock list with 13-digit barcodes and 22-digit tracking numbers. The scanned barcode in F2, 7350012345695, was typed as a number. In a General cell it shows as 7.35001E+12, but the cell still holds every digit, which you can check in the formula bar.

General switches to scientific notation for numbers with 12 or more digits, and for shorter numbers when the column is too narrow. Changing the format brings the digits back. If a cell formatted as Number is too narrow, Excel shows ##### instead, so widen the column too.

1. Select the cells or the whole column.
2. Press Ctrl+1 to open Format Cells.
3. Choose Number, set Decimal places to 0 and untick Use 1000 Separator (,) for IDs. Or choose Custom and type 0.
4. Double-click the column's right border to fit the width.

Case 2: more than 15 digits, the digits are gone

Excel stores numbers with 15 significant digits. Type or paste a 16-digit card number such as 4111111111111111 into a General cell and Excel stores 4111111111111110. A 22-digit tracking number keeps its first 15 digits and the last 7 become zeros. Changing the format afterwards only shows the zeros. It cannot restore the digits, and Undo is the only way back.

Formatting a cell as Text after the fact does not help either: the value is already a number with zeros at the end. Go back to the source (the original CSV, the system export, the email) and bring the values in as text using one of the methods below.

Be careful with CSV files. If you open a CSV in Excel and save it again, Excel writes the converted values back, so the original digits are lost from the file too. Keep the original until the data is safely in a workbook.

Keep long IDs as text when typing

An ID is a label, not a quantity. You never add up barcodes or tracking numbers, so store them as text. Column C holds tracking numbers typed with an apostrophe in front. The apostrophe is not part of the value: it only tells Excel to keep what follows as text, so all 22 digits survive.

Formatting the column as Text (Home > Number Format > Text) before you type does the same for every entry. A small green triangle may appear in each cell. It is Excel noting a number stored as text, and for IDs you can ignore it.

Import a CSV without losing digits

Double-clicking a CSV opens it with automatic conversion, so long numbers are converted before you can stop it. Import it instead and set the ID columns to Text.

In current Excel for Microsoft 365 you can also turn off the conversion: File > Options > Data, under Automatic data conversion, untick "Keep first 15 digits of long numbers and display in scientific notation if required". The same section has the option that turns codes like 1E5 into 1.00E+05.

1. Go to Data > From Text/CSV, pick the file and click Transform Data.
2. Click the ID column's header, then Transform > Data Type > Text.
3. If Excel asks to Change Column Type, choose Replace current, so the automatic number step is replaced rather than followed.
4. Click Home > Close & Load.

IDs that do not match in lookups

Mixing text IDs and number IDs breaks lookups. The supplier's barcodes in column B are text, but the scanned barcode in F2 is a number, and to XLOOKUP and VLOOKUP the number 7350012345695 is not equal to the text "7350012345695". =XLOOKUP(F2,B2:B6,A2:A6) returns #N/A.

Convert the lookup value to text with TEXT(F2,"0"). The "0" format writes out every digit with no E+ and no separators. For IDs of 15 digits or fewer you can turn the list into numbers instead, =XLOOKUP(F2,--B2:B6,A2:A6) in Excel 365, but text is safer because it also works for longer IDs.

Counting long IDs: COUNTIF reads them as numbers

Even stored as text, long IDs can trip up COUNTIF and COUNTIFS. They convert text that looks like a number into a number before comparing, so only the first 15 digits count. All five tracking numbers in column C share their first 15 digits, and =COUNTIF(C2:C6,C2) returns 5 instead of 1.

Compare with = inside SUMPRODUCT instead. It compares the text exactly.