UPC-A barcodes are 12 digits long, and many start with a zero. Excel treats them as numbers, so it drops the leading zero and may switch long values to scientific notation, such as 1.23457E+11. A custom number format fixes the display without changing the value, and it works in all current desktop versions of Excel.
Quick Answer
Select the UPC column, press Ctrl+1, choose Custom, type 000000000000 (twelve zeros) in the Type box, and click OK. Every code now shows all 12 digits, including leading zeros.
How to Format UPC Codes in Excel
- Select the cells or the whole column that holds the UPC codes.
- Open the Format Cells dialog with the Ctrl+1 shortcut, or from the right-click menu.
- In the Category list on the Number tab, pick Custom.
- In the Type box, enter 000000000000 (twelve zeros). Use thirteen zeros for EAN-13 codes.
- Click OK. Widen the column if you see number signs instead of digits.
UPC Formatting Options
| Method | Best for |
|---|---|
| Custom format 000000000000 | Numbers you still want to sort as numbers |
| Text format before typing | Codes you paste or type by hand |
| Apostrophe before the code | Fixing a single cell quickly |
| Import as Text | Bringing codes in from a CSV file |
Troubleshooting
- Leading zeros are already gone: the format only adds display zeros, which works for UPCs because the length is fixed. For codes of mixed length, retype them into cells formatted as Text.
- Codes turn into scientific notation after opening a CSV: import the file with Data, From Text/CSV and set the column to Text.
- For more on stopping unwanted conversions, read how to stop Excel from changing numbers into dates.
- For the basics of number formats, see how to apply number formatting to a cell in Excel.
Frequently Asked Questions
Why does Excel show 1.23457E+11?
The General format switches to scientific notation for long numbers in narrow columns. A custom or Number format with no decimals fixes it.
Will a custom format change the UPC value?
No. It only changes how the number is displayed, so formulas and lookups still use the same value.
Should I store UPCs as text or numbers?
Text is safest if you never do math with them, especially for codes longer than 15 digits, which Excel cannot store exactly as numbers.
Summary
- Select the UPC cells.
- Open Format Cells with Ctrl+1.
- Choose Custom and type twelve zeros.
- Use Text format for mixed-length codes.
Kermit Matthews is a freelance writer based in Philadelphia, Pennsylvania with more than a decade of experience writing technology guides. He has a Bachelor’s and Master’s degree in Computer Science and has spent much of his professional career in IT management.
He specializes in writing content about iPhones, Android devices, Microsoft Office, and many other popular applications and devices.