How to Add Leading Zeros in Microsoft Excel

Excel contains many useful and valuable features for the user. Still, it has a lot of quirks and issues that could be annoying to someone who isn't well-versed in Excel. For example, the zeros in Excel could prove to be a challenging number to deal with. Microsoft Excel automatically removes zeros written on the left, or leading zeros in a cell, whenever you have to enter zeros in a specific numbering system. This could be the case if you maintain records in Excel, such as Serial numbers, Social Security Numbers, format codes, postal codes, account numbers, employee ids, zip codes, or phone numbers starting with zeros. In this article, you can learn how to control leading zeros in spreadsheets and switch between modes based on your current work. You can also follow us on YouTube for more amazing tips, tricks, and template tutorials.

How to Add Leading Zeros in Excel?

Given that you may not like adding leading zeros in Excel manually, there are many ways to add leading zeros in Excel using different Excel features automatically. We will learn all these other methods in the following few lines in detail: Type some leading zeros into the cells, as shown in the screenshot below. Now, Press enters, and you will find that the numbers are gone because Excel does not automatically read the leading zeros. So, now the leading zeros have vanished, and I assume your working day has got more challenging. There are different ways to add leading zeros in Excel. Let's find out the methods that fit your needs.

Method 1: How to add leading Zeros by using the Format Cells Dialog Box

To apply this custom format method adds zeros follow the next few steps: To facilitate your work tasks to add leading zeros, it may be good for you to know the number of digits in each cell by a simple formula. Step One: Write these LEN functions in a blank cell to know the number of digits: =LEN(A1) You will know the importance of the above formula to add leading zeros in Excel later in this method. Note: The formula will be written in the cell and appear in the Excel text function string above or the formula bar. See the red box in the screenshot. After writing the above formula in the cell, press Enter, and the number of digits written in the numeric procedure will appear in the cell. As shown below, see the screenshot. Note: You have to change the column number and letter each time you type the other formulas according to the place of the cell you want to know how many digits it has. For Example, The numeric formula will be =LEN(A1) if you want to know the number of digits in the cell in column A, row 1. And it will be like the following formula =LEN(B2) if you are targeting to know the number of digits in the cell located in column B, row 2. etc. Write the same formula in all the cells; you need to know the number of digits in the text string. Step Two: Select all the cells you want to edit and add leading zeros. You can do this by dragging the cursor or holding down the transform file on the keyboard while clicking with the mouse. As shown below, See the screenshots. You can select adjacent cells by clicking the keyboard shortcuts as follows: Click on the first cell in the cells field with the left mouse button, Then press the Shift button on the keyboard, Finally, Click on the last cell you want to access in the cells field while holding the Shift button. Step Three: Right-click on your selection, then choose Format Cells. Alternatively, you can use the keyboard shortcut by pressing Ctrl+1 to open the format cells window faster. Step Four: The Format Cells dialog box will appear. Select Custom Number Format or the Number tab. As shown in the screenshot. Step Five: Select Custom Format Cells. Step Six: Create the following custom number format by adding as many zeros as the number you got from the digits. Step Seven: You can add zeros in the custom format code by replacing the word General type box or the text string with zeros. Note: The format code or the number of zeros here is variable length numbers. It is selected according to the number obtained by using the formula. You can add five zeros or six or more. In our example, we added the fixed-length numbers or the fixed number of zeros, 10 Zeros to add only one leading zeros, or inserted fixed zeros before a number. Step Eight: Press Ok to add the leading zeros. Here you are, the final result of adding leading zeros in the custom format. Note here this method didn't change the numeric format; it only shows the fixed length of the pre-added zeros without messing with the cell format. See the screenshot below. There is one problem with this method that it only works later. You must set the cell format to text values before typing any text. If you try to do this after that, the zeros will disappear, and you must re-write them. Note: Selecting a cell with formatting will still show the original number in the formula bar. They only appear in the worksheet with the leading zero formats. It's only the way it's displayed that is changed. This also means if you copy and paste the data as values, you will lose the leading zeros. It might help if you see How to Change Currency in Excel

Method 2: How to Add Leading Zeros by Adding an Apostrophe

The LEN functions method is not the only way to keep the leading zeros. But adding a leading apostrophe A quick and easy way to get the same result is to use an apostrophe, single quotation mark, or magic apostrophe. Doing so will prevent Excel from automatically converting your number. You can keep the leading zeros in the dataset available in Excel by this quotation mark (') method. For example, if you have phone numbers or numeric values, you must keep their arithmetic calculations right. To do this, follow these few steps for adding leading zeros: Step One: Write this apostrophe (') at the beginning of the number or the numerical value. As shown below, see the screenshot. Step Two: Press Enter, and you'll keep the leading zeros in this cell. See the screenshot. As the above example shows, this method will not change the number or customs number formatting. It only shows the leading zeros without messing with the cell format. Note: You might see a small green triangle (An Error indicator) in the top-left corner of the cell, and when you select that cell. This will show you a warning sign indicating that you have stored the number as text. The leading apostrophe will appear only in the formula bar but not in the cell. Also, Note: This method may achieve the desire of keeping the leading zeros, but it is neither practical nor productive. Doing this in each separate cell may take a lot of time. It may be a proper method for you when you import data or numbers for the first time yourself; in this case, the quotation mark method will not take much time, and maybe it is the easiest to follow.

Method 3: How to Add Leading Zeros by Using the Custom Format as a Text Value

This method is a very productive one, and it is somewhat similar to the first method in some steps. So, How do you convert numbers to text in Excel without losing zeros? Step One: Select all the empty cells you want to enter and add leading zeros. You can do this by dragging the cursor or holding down the transform file on the keyboard while clicking with the mouse. As shown below, See the screenshots.

You can select adjacent cells by clicking the keyboard shortcuts as follows:

Click on the first cell in the cells field with the left mouse button, Then press the Shift button on the keyboard, Finally, Click on the last cell you want to access in the cells field while holding the Shift button. Step two: Right-click on your selection, then choose Format Cells. Alternatively, you can use the keyboard shortcut by pressing Ctrl+1 to open the Format Cells dialog box faster. Step Three: The Format Cells dialog box will appear. Select Custom Number Format or the Number tab. As shown in the screenshot. Step Four: Select the Text Format. As shown below in the screenshot. Step Five: Press OK, to get the text values in the chosen cells. See the screenshot. This method will make Excel treats the number format as a text string, not a number. If you enter a number with leading zeros, that will force Excel to show the number as not a number. Step Six: Write any number leading to zeros and press Enter. See the screenshot below; the leading zeros appear in the cell format. Note: You might see a small green triangle (An Error indicator) in the top-left corner of the cell, and when you select that cell. This will show you a warning sign indicating that you have stored the number as text.

Method 4: How to Add Leading Zeros by Using The Text Function

The text function is one of the best tools in Excel to keep the leading zeros. This is more or less similar to setting your custom number formatting in the Format Cells dialog box. So, let's learn what the text function does in the following few lines: The text function enables you to refer to text or numbers, then the selecting of the format for the output. This function uses the syntax "=text(value, format)." The value can be any cell reference or number. The format follows the same structure as the custom number configuration, except the formatting is enclosed in the quotation mark. The value function converts the value in A1 to a number. As an example, the following formula converts the number in cell A1 into an 11-digit phone number: = text(A1, "0000-000-0000") This method can copy and paste numerical data into other cells while adding leading zeros to the new cell. Note that: You will have two copies of the data; The original version that does not include the leading zeros and the new version that contains the actual number in addition to the leading zeros. So, How can you use the text function to show the leading zeros? Follow these few steps to keep the leading zeros in a text format by using this TEXT formula: Step One: Write this TEXT formula = text(A1, "0000-000-0000") in any empty cell. As shown in the screenshot. Note: You will find the text formula in the cell and the formula bar. As shown in the text string inside the red box. See the screenshot below. Step Two: Press Enter. You will find the exact number in the original cell plus the leading zeros. See the screenshot below. For more great Excel tips and tricks, check out our blog post-Excel is Fun

Method 5: Insert the Same Number of Leading Zeros Into Numbers Using The Concatenate Function

The custom number formats and the text function specify a certain number of digits. Still, the number of leading zeros varies with the number of digits entered, excluding the leading zeros. However, the Concatenate function allows you to add a specified number of leading zeros, no matter how many digits are included in the source cell. Use the structure "=Concatenate([zeros], A1)" and enter the zeros enclosed in quotes. For example, to add three zeros to the text or number in A1, use the formula =CONCATENATE("000", A1) Add or insert the same number of leading zeros into numbers using the Concatenate function. If you want to enter a certain number of leading zeros in each number, say three leading zeros for each number, you should try the concatenation function. Step One: Enter the formula =CONCATENATE("000", A1) in a blank cell. Step Two: Press Enter. Three zeros have been added before the numbers according to the prefixed zeros. See the screenshot above. The formula bar will show the added formula while the numbers appear in the cell. In this article, we've given you several ways to get the same result, and as the above methods show, plenty of options are available to you if you're looking for a simple way to add leading zeros to cells in Excel. If you find out that these methods are too hard for you to use, take up more of your work, or don't have the luxury of using them because you're short on time, you can learn how to use Excel's features to process your data at scale using the ready-to-use template that provided by Simple Sheet. Excel is estimated to be used by more than 1.2 billion people worldwide in over 140 countries. With such widespread use comes the need to accommodate and process data specific to different countries. So you can see the Simple Sheet Ready-to-use Template Catalog Here.