How to Format UPC Codes in Excel

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.

select the column with the data

 

 

Step 3: Right-click the column letter, then click Format Cells.

select the format cells option

 

 

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.

select number, then change decimal places to zero

 

 

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.

select the column with the data

 

 

Step 2: Right-click the column letter, then click Format Cells.

select the format cells option

 

 

Step 3: Click the Custom option at the left side of the window.

select the custom option

 

 

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.

enter 12 zeroes

 

 

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.

select the format cells option on an empty column

 

 

Step 2: Click Text from the options at the left side of the window, then click OK.

select the text option

 

 

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.

paste data into the text formatted column

 

 

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.