Excel is a great option for storing information about products that your company sells. But you may run into a problem where you need to learn how to format UPC codes in Excel, as it will commonly convert them so that they display as scientific notation.
The data is still stored correctly in Excel, but converts to display this way in an effort to simplify the display. This is not ideal for UPC numbers, so you need to make some adjustments to your cell format so that the full UPC number displays in the cell instead.
Formatting UPC Codes in Excel
The steps below were performed in Microsoft Excel 2013, but the same principle will apply to other versions of Excel as well. These steps are also specifically intended for UPC numbers that do not start with the number “0”. If your UPC numbers start with a “0”, then you need to use a different format, which we will cover at the end of this article.
Step 1: Open your spreadsheet in Excel 2013.
Step 2: Click the column letter at the top of the spreadsheet that contains the cells with UPC numbers.
Step 3: Right-click the column letter, then click Format Cells.
Step 4: Click the Number option from the list at the left side of the window, change the number of Decimal Places to 0, then click OK at the bottom of the window.
The above solution will work if all of your UPC numbers start with a number other than 0, but will remove any leading zeroes. If you have barcodes that do contain a leading zero, then use the following option instead. Note that all of your UPC numbers will need to have the same number of digits for this solution to work.
Step 1: Click the column letter at the top of the spreadsheet that contains the cells with UPC numbers.
Step 2: Right-click the column letter, then click Format Cells.
Step 3: Click the Custom option at the left side of the window.
Step 4: Click inside the field under Type, then enter 12 zeroes (or however many digits are in your UPC numbers), as in the image below. Click the OK button to apply your changes.
But what if you have UPC numbers that contain leading zeroes and a variable number of digits? Then you need to approach it in a slightly different way.
Step 1: Right-click the column letter at the top of an empty column, then click Format Cells. This step is needed because you need to format the cells as text before you enter the UPC numbers into the cells.
Step 2: Click Text from the options at the left side of the window, then click OK.
Step 3: Paste your data from its’ current location, or manually enter it. Any leading zeroes will remain, as your cells are now formatted as text.
Are your printed Excel spreadsheets difficult to read? Learn how to print the lines on your Excel 2013 spreadsheet to make them easier for your audience to understand on the printed page.
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.