Skip to content

Add Leading Zeros to Numbers

Have you ever tried adding leading zeros to a number in a spreadsheet? If so, you probably found that the zeros don’t stay, and all you have left are the remaining digits.

Most of the time, this isn’t bad because your spreadsheet application is just trying to keep your numbers clean, concise, and accurate. However, there are times when you want the zeros to stick around.

Two good examples are ID numbers for a database or SKU numbers for products. In both instances, having leading zeros may be preferable and potentially necessary, especially if your ID or SKU numbers need to contain an exact number of digits.

Therefore, this article will cover three ways to add leading zeros to numbers in both Google Sheets and Microsoft Excel.

Method 1 – Add an Apostrophe Before the Number

The first thing you can do to get leading zeros to stick is to add an apostrophe ( ‘ ) before the number when you type it into the spreadsheet.

The spreadsheet will then treat the number as a text value instead of a numerical value, and the zeros will stay visible.

This method works the same way in both Excel and Google Sheets.

Method 2 – Format the Number as Text

The second thing you can do to keep your zeros visible is to change the number formatting to “Text.” When a spreadsheet contains a text value, it will be sure to display all the characters, even if they are zeros.

Text Format in Excel

To change the number formatting to “Text” in Excel, follow the steps below.

Select the cell(s) you would like to format as “Text.”

Use the Home tab in the Excel Ribbon to change the format to “Text.”

Type your numbers into the formatted cells, and all leading zeros will now stay visible.

Excel might put little green triangle indicating an error in these cells. The error is warning you that you are storing numbers as text. In this case, select the cells, click on the small error box, and select “Ignore Error.”

Text Format in Google Sheets

Follow the steps below to change the number formatting to “Text” in Google Sheets.

Select the cell(s) you would like to format as “Text.”

Click on the More Formats button in the Google Sheets Toolbar and select Plain text.

Type your numbers into the formatted cells, and all leading zeros will now stay visible.

Method 3 – Use Custom Number Formatting

The final method to keep leading zeros visible is probably the most useful. In fact, this method will insert the leading zeros for you. You won’t have to add the zeros yourself every single time. Here is how it works.

You can apply custom number formatting that will always force a number to have a minimum number of digits. For instance, you can set up the formatting so that the numbers 1, 10, and 100 all show up with four digits.

  • 0001
  • 0010
  • 0100

You also have the option to make the extra digits whatever you like. You can make all the filler digits a two if you so choose.

  • 2221
  • 2210
  • 2100

Using this method, you can always ensure that your numbers show leading zeros and are the proper length as well.

Custom Number Formatting in Excel

To apply custom number formatting in Excel, follow the steps below.

Select the cell(s) you would like to format.

Use the Home tab in the Excel Ribbon, click the down arrow in the Number Format box, and select “More Number Formats.”

The Format Cells dialog box will appear. Under Category, select Custom.

Under Type, write in zeros for how long you would like your numbers to be. For example, if you want all numbers to contain at least four digits, type in 0000.

Finally, click Ok.

Type your numbers into the formatted cells, and zeros are added to all numbers to ensure each number is at least four digits long.

Custom Number Formatting in Google Sheets

To apply custom number formatting in Google Sheets, follow the steps below.

Select the cell(s) you would like to format.

Click the More Formats button in the Google Sheets Toolbar, and select “Custom number format.”

The Custom number formats box will appear.

In the space to the left of the “Apply” button, type in zeros for how long you would like your numbers to be. For example, if you want all numbers to contain at least four digits, type in 0000.

Finally, click Apply.

Type your numbers into the formatted cells, and zeros are added to all numbers to ensure each number is at least four digits long.

This Post Has One Comment

  1. I’ll have to try out the custom number formatting some time! For my line of work, I like using the “increase decimal” button in the Number section of the Home page. I use codes that require 4 decimal places (usually including zeros). It might just be easier to do a custom format to use when I need it! Thanks for the information!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top