Sometimes, we need to input a number with the leading zeroes for our dataset. However if the proper settings are not used, we can face difficulties in inputting 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!

**Table of Contents**Expand

## 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.

## Leading Zero Not Showing in Excel: 9 Possible Solutions

We can solve the 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.

### 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 would have 5 leading zeroes. If the number was of 4 digits at the beginning, it would have 2 leading zeroes.

**Read More: **How to Keep Leading Zeros in Excel CSV

### 3. Using TEXT Function

Using the** TEXT **function is one of the common approaches to input leading zero 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 would have 5 leading zeroes. If the number was of 4 digits at the beginning, it would have 2 leading zeroes.

**Read More:** How to Keep Leading Zero in Excel Date Format

### 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:** How to Keep Leading Zeros in Excel

### 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 a 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 would have 5 leading zeroes. If the number was of 4 digits at the beginning, it would 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 would have 5 leading zeroes. If the number was of 4 digits at the beginning, it would 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 the **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 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 problems 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.

**Download Practice Workbook**

You can download the** Excel workbook** from here.

## 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. Don’t forget to drop your comments, suggestions, or queries in the comment section below.

## Related Articles

- Remove Leading Zeros in Excel
- How to Add Trailing Zeros in Excel
- How to Remove Zeros in Front of a Number in Excel

**<< Go Back to Pad Zeros in Excel | Number Format | Learn Excel**