Md. Asaduzzaman
Sometimes, we need to input a number with the leading zeroes for our dataset. But if the proper settings are not used, we can face difficulties to input the leading zeroes of the number. Are you looking for solutions to input leading zeroes in your dataset? In this article, we will give 9 solutions to the problem leading zero not showing in Excel. Let’s get started!
Download Practice Workbook
You can download the Excel workbook from here.
What Is Causing Leading Zero Not Showing in Excel?
There are multiple reasons behind the problem leading zero not showing. These are 2 common reasons why a user faces this problem.
- Default General number format is active in Microsoft Excel.
- Number data type is active for the numbers.
9 Possible Solutions for Leading Zero Not Showing in Excel
We can solve leading zero not showing in Excel problem in 9 possible ways. Some methods contain formatting of the data type, some use formula, and some use power query and pivot table to solve this problem. To solve this problem, we are taking some numbers without the leading zeroes like the dataset below. We have created another column to input these numbers with leading zeroes. Now we will discuss all of these 9 methods to learn how to input leading zero in a dataset.
1. Use of Format as Text Method
This is one of the easiest methods to solve the problem leading zero not showing in excel. One can input any number of leading zeroes in excel as they want by following this method. In order to use this method, we have to follow the steps below:
Steps:
- First, select the cells where you want to input numbers with leading zeroes. In this example, we have selected cells (C5:C9).
- Second, go to the Home tab.
- Third, click on the Number Format drop-down.
- After that, select Text as the Number Format as shown below.
- Hence, input the numbers in your dataset with as many leading zeroes as you want like the image below.
- Finally, your leading zero not showing problem will be solved.
Read More: Excel Convert Number to Text with Leading Zeros: 10 Effective Ways
2. Applying Custom Format
We can use a custom format for the cells where we want to input leading zero in our excel data. This method gives us more control over our data input and managing these data. In order to apply this method, we have to follow the steps below.
Steps:
- First, select all the numbers for which you want to add leading zeroes.
- Second, copy the numbers by pressing Ctrl+C.
- Third, paste the numbers to a column where you will add leading zeroes by pressing Ctrl+V.
- Next, select the cells containing the numbers.
- Furthermore, click on the Number Format option as shown below.
- As a result, it will open the Format Cells window like the below one.
- Afterward, click on Custom.
- Subsequently, type ‘000000’ in the Type field.
- Then, click OK.
- As a result, you will see the numbers of your selected column with 6 digits with leading zeroes. If the number was 1 digit at the beginning, it will have 5 leading zeroes. If the number was of 4 digits at the beginning, it will have 2 leading zeroes.
Read More: How to Add Leading Zeros to Make 10 Digits in Excel (10 Ways)
3. Using TEXT Function
Using the TEXT function is one of the common approaches to input leading zeroe in an excel dataset. It is an easy and efficient way to perform the task. In order to add leading zeroes using the TEXT function, we have to follow the steps below.
Steps:
- First, select the cell C5 and type the following formula:
=TEXT(B5,"000000")- Second, press Enter.
- After that, select cell C5 and drag the Fill Handle to the entire column With Leading Zeroes.
- As a result, you will see the numbers of this column with 6 digits with leading zeroes. If the number was 1 digit at the beginning, it will have 5 leading zeroes. If the number was of 4 digits at the beginning, it will have 2 leading zeroes.
Read More: How to Add Leading Zeros in Excel Text Format (10 Ways)
4. Inserting Leading Apostrophe
We can also insert a leading apostrophe to a number in order to add leading zeroes to this number. This is an easy way to solve the problem leading zero not showing in excel. In order to apply this method, we have to follow the steps below.
Steps:
- Firstly, select the cell where you want to input the number.
- Secondly, insert a leading apostrophe and type the number you want as shown below.
- Now, press Enter and you will see an output like the image below where your number will have as many leading zeroes as you have inserted after the apostrophe.
- Hence, do the same for all the cells and you will be able to solve the leading zero not showing the problem.
Read More: Add or Keep Leading Zeros in Excel (10 Suitable Ways)
5. Use of RIGHT Function
Using the RIGHT function is also a common approach to input leading zeroes in a dataset. It is an easy and efficient way to perform the task. In order to add leading zero in excel using the RIGHT function, we have to follow the steps below.
Steps:
- First, select the cell C5 and type the following formula:
=RIGHT("000000"&B5,6)- Second, press Enter.
- After that, select cell C5 and drag the Fill Handle to the entire column With Leading Zeroes.
- Finally, you will see the numbers of this column with 6 digits with leading zeroes. If the number was 1 digit at the beginning, it will have 5 leading zeroes. If the number was of 4 digits at the beginning, it will have 2 leading zeroes.
6. Utilizing BASE Function
Utilizing the BASE function is a convenient way to input leading zeroes in a dataset. It is an easy and efficient way to perform the task. In order to add leading zeroes using the BASE function, we have to follow the steps below.
Steps:
- First, select the cell C5 and type the following formula:
=BASE(B5,10,6)- Second, press Enter.
- After that, select cell C5 and drag the Fill Handle to the entire column With Leading Zeroes.
- Finally, you will see the numbers of this column with 6 digits with leading zeroes. If the number was of 1 digit at the beginning, it will have 5 leading zeroes. If the number was of 4 digits at the beginning, it will have 2 leading zeroes.
7. Applying CONCATENATE Function
Applying the CONCATENATE function is an easy way to input leading zero in the cells of an excel worksheet. It is an easy and efficient way to perform the task. In order to add leading zeroes using CONCATENATE function, we have to follow the steps below.
Steps:
- First, select the cell C5 and type the following formula:
=CONCATENATE(0,B5)- Second, press Enter.
- After that, select cell C5 and drag the Fill Handle to the entire column With Leading Zeroes.
- Finally, you will see each of the numbers of this column with 1 leading zero like the below one.
8. Using Power Query and Text.PadStart Function
Using Power Query and Text.PadStart function is also a way to solve the problem leading zero not showing in Excel. But it is suitable for advanced Excel users rather than beginners. To apply this method, you will need a dataset of numbers for which you want to add leading zeroes. In order to use this method, we have to follow the steps below.
Steps:
- First, go to the Data tab.
- Second, click on the From Table option.
- As a result, it will open a Create Table window like the below one.
- After that, select your entire dataset with the heading as shown below.
- Furthermore, check the My Table has Headers option.
- Then, press OK.
- Hence, a Query Editor will be shown on your screen like the below one.
- Afterward, right-click on the table heading Numbers >> Click on Change Type drop-down>> select Text as shown below.
- Subsequently, go to the Add Column option.
- Next, click on Add Custom Column.
- Now, Add Custom Column window will open. Type the new column name as With Leading Zeroes.
- Next, type the following formula in the Custom column formula bar:
=Text.PadStart([Numbers],6,”0”)- Then, click OK.
- As a result, a customized column having leading zeroes in the numbers will be added to your Query Editor like the image below.
- Furthermore, go to Home >> click on Close & Load drop down >> select Close & Load To as shown below.
- Hence, it will open a Load To pop-up window like below.
- Now, select Existing Worksheet.
- Next, select the location you want to load the table. In this example, we have selected cell $C$4 to load the table.
- Finally, click on Load.
- As a result, you see an output like the image below where a table will be loaded in your worksheet consisting of the numbers with leading zeroes.
9. Inserting Pivot Table and Dax Measure
Inserting Pivot Table and Dax Measure is also a method to solve leading zero not showing problem in Excel. But it is also suitable for advanced Excel users rather than beginners. To apply this method, you will need a dataset of numbers for which you want to add leading zeroes. In order to use this method, we have to follow the steps below.
Steps:
- First, go to the Insert tab.
- Second, click on the PivotTable option.
- As a result, Create PivotTable window will open.
- Next, select the cells of your table for which you want to add leading zeroes. In this example, we have selected the cells ($B$5:$B$9) in the Table/Range field.
- Furthermore, select the location where you want to insert the PivotTable. In this example, we have selected the $D$4 cell in the Location field to insert the PivotTable.
- Then, press OK.
- After that, right-click on your table name and click on Add Measure.
- Subsequently, Type With Leading Zeroes in the Measure Name field.
- Next, type the following formula in the formula bar as shown below:
=CONCATENATEX('Table3 Numbers',FORMAT('Table3 Numbers'[Numbers],"000000"),",")
- As a result, a function named With Leading Zeroes will be added under your Table Name as shown below.
- Now, drag the function to the Values field.
- Finally, you will see an output like the image below where your numbers will be listed in a Pivot table with the leading zeroes.
🔎 How Does the Formula Work?
FORMAT(‘Table3 Numbers'[Numbers],”000000″): Firstly, this part of the formula converts the numbers of the table into 6-digit numbers including the leading zeroes.
CONCATENATEX(‘Table3 Numbers’,FORMAT(‘Table3 Numbers'[Numbers],”000000″),”,”): Finally, this formula makes a list of all the numbers with leading zeroes in a single cell.
Things to Remember
- Use of Text format and inserting leading apostrophe give more control to a user to insert as many leading zeroes as they want before a number.
- Applying Custom format and using TEXT, RIGHT, and BASE functions convert the numbers to specific digits and add leading zeroes according to this.
- CONCATENATE function can add a specific number of leading zeroes to all the numbers.
- To use Power Query and PadStart function and insert Pivot Table and Dax measure, a user will require advanced Microsoft Excel skills to add leading zeroes before any number.
Conclusion
Hence, follow the above-described methods. Thus, you can easily learn how to solve the problem leading zero not showing in Excel. Hope this will be helpful. Follow the ExcelDemy website for more articles like this. Don’t forget to drop your comments, suggestions, or queries in the comment section below.
