Microsoft Excel provides a large variety of options that can help you to properly format your data. If you have ever worked with currency cells in a spreadsheet, for example, then you might already be aware of how useful this can be.
To format UPC cells in Excel you need to select the cells, right-click on them and choose Format Cells, then either adjust the number of decimal places to zero or use a custom formatting option where you specify the number of digits in the barcodes.
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.
How to Display UPC Numbers Properly in Excel
- Open your spreadsheet.
- Select the cells.
- Right-click and choose Format Cells.
- Select the Custom option.
- Enter a zero for each digit in the number.
- Click OK.
Our guide continues below with a few different formatting methods for working with Excel barcodes, including pictures of these steps.
Steps for Formatting UPC Codes in Excel (guide with Pictures)
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.
How to Format UPC Numbers with Leading Zeroes in Excel
Since a lot of barcode or UPC numbers are going to start with a zero or two, then we need to make some adjustments to accommodate that situation.
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 on 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.
Our next section discusses what to do when you have some barcodes that have leading zeroes, as well as some barcodes with different amounts of digits.
How to Format Barcodes with Variable Numbers of Digits in Excel
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.
More Information on How to Set Barcode Formatting in Microsoft Excel
In our guide above we specify to select an entire column and change the formating to whichever UPC option is better for you. However, if you don’t need to format an entire column, and only need to use it for a couple of cells, you can simply select those cells instead.
If you copy and paste data from one of these formatted cells then you might discover that leading zeroes aren’t included, depending on how and where you paste that data. If you are copying it to another cell in Excel then you might need to re-format that new cell to get the data to appear correctly.
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.