3 Methods for Adding Leading Zeros in Excel

By Indeed Editorial Team

Published April 2, 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Excel is a very useful software for keeping records and doing calculations. In accounting or record-keeping of any kind, you may use leading numbers to analyze your data accurately. To use various formulas and format your financial records accurately, you may use leading zeros in Excel. In this article, we discuss what leading zeros are, how to add them in Excel and tips you can use to make the process easier.

Related: 12 Excel Interview Questions and Answers To Help You Prepare

What are leading zeros in Excel?

Leading zeros are zeros you add at the front of a string of numerals to indicate empty spaces. There are many cases where leading zeros are helpful or necessary, so there are other ways to add them if your spreadsheet requires them. In Excel, if you type leading zeros before a number, Excel can automatically remove them to create a cleaner-looking spreadsheet.

An example of a leading number is the product number. If your shop has 37 products, and each product is labeled one through 37, then in a spreadsheet, each product that only has one digit, like one through nine, may be recorded with a leading zero, like 01. Having the same number of digits is important to many different formulas you may use in Excel so the leading number is essential to the correct display of the product number.

Related: How To Create a Balance Sheet in Excel (With Tips and FAQs)

How to add leading zeros in Excel

Here are a few different ways you can add leading zeros in Excel:

1. Convert the format to text

When you type numerals into a cell, the cell automatically formats the contents as a number. You can change the format to text to eliminate some of the embedded formatting rules in a number cell. You can follow these sites:

  1. Select the cells. This process works best for small batches of cells, so click and drag your mouse until you've selected all the cells you want to reformat.

  2. Go to the "Home" tab. The "Home" tab is in the toolbar at the top of the program window, on the left side.

  3. Select "Number Group." One of the icons in the "Home Toolbar" is "Number Group." When you select it, it displays a drop-down menu.

  4. Scroll to "Text." Scroll through the drop-down menu until you find the "Text" button.

Once you select the text button, the cells you selected should format numbers as text strings, allowing you to type in the zeros and have them show up when you exit the cell.

2. Use custom number formatting

This method uses the different numerical formats that excel includes to format numerical cells. If you have a dataset and you need to use a formula where a result is a number instead of text, you may need to use this method. You can follow these steps:

  1. Select the cells. This process works best for small batches of cells, so click and drag your mouse until you've selected all the cells you want to reformat.

  2. Go to the "Home" tab. The "Home" tab is in the toolbar at the top of the program window, on the left side.

  3. Select "Number Group" dialog launcher. One of the icons in the "Home Toolbar" is "Number Group." Select the arrow at the bottom right of that section to open the "Format Cells" dialog box.

  4. Select "Custom." In the "Number" tab, there is a list of formats in the "Category" section. Scroll to the bottom is select "Custom."

  5. Type your desired number of numerals. You can enter a zero for each integer you want to appear in the cell in the field labeled "Type." For example, you can enter five zeros if you want numbers to have five digits regardless of their amount. The number 37 then appears like 00037.

  6. Click "OK." Click the "OK" button at the bottom right side of the dialog box to populate the cells you selected with the number of digits you requested, regardless of value.

Related: How To Add Numbers in Excel (Plus Tips)

3. Use The REPT and LEN functions

If you have numeric data but want the result to be text when you use the cells in formulas, you can use these functions to format the leading zeros accurately. This method only works with cells that are formatted to read as numbers. You can follow these steps:

  1. Select the cells. Select the cells next to the cells that contain the numbers you want to format with leading zeros.

  2. Input the formula. Input the following formula with the cell reference as the cell you want to format: =REPT(0,number of zeros-**LEN(cell reference))&cell reference**

  3. Press "Control + Enter." Once you've entered the correct formula, you can press "Control + Enter" to apply it to the cell.

  4. Drag the formula to other cells: If you want a mass of data reformatted, you can click the box at the bottom right side of the cell and drag it down to populate the formula in the following cells.

This method has the formatted cells populate in the cell next to the original cells instead of formatting the original cell. After completing this step, you can hide the original column to only reference the formatted cells. The formula works like this:

  • =REPT(0,number of zeros: This string tells excel how many zeros you want to include before the string, and the answer is 5.

  • ( - ): The subtraction symbol tells excel to include five zeros minus the number of strings in the cell reference.

  • LEN(Cell reference): This section provides the length of string currently in the cell.

  • &Cell reference: This last section of the formula indicates that you want to add the contents of the cell you reference in addition to the zeros.

Tips for adding leading zeros in Excel

Here are some additional tips for adding leading zeros in Excel:

Determine your formulas

If you want to format a cell, ensure that you understand what formulas you intend to include that cell in so, you can format it correctly. Formatting a cell as text won't return the correct answer if you want a formula to return text strings and vice versa. Before you begin to format your cells, try to plot out what formulas you want to use them in, and where so that you can consider those needs when choosing the correct method.

Related: Basic Excel Formulas and How To Use Them

Be precise

When using a formula, it's important to be precise about the symbols and letters you use because a mistake could return the wrong information and make the rest of your spreadsheet inaccurate. When you can, try to copy and paste formulas so you know they are entirely accurate and do not include any additional text or strings that may affect the product of the formula.

Try different methods

Each method can be appropriate for different causes. In Excel, it is helpful to know different methods to accomplish each task so you can troubleshoot your spreadsheet if one method isn't working. You can work around formats and embedded rules as long as you are familiar with the program and can study different methods to come up with the figures you want.

Please note that none of the companies mentioned in this article are affiliated with Indeed.

Explore more articles