How to Add Leading Zeros in Excel?
Tags: exceltutorials, addleadingzerosinexc, excel
Published: 2021-11-15 12:01:56

---
title: How to Add Leading Zeros in Excel?
published: true
date: 2021-11-12 04:23:00 UTC
tags: ExcelTutorials,AddleadingzerosinExc,Excel
canonical_url: https://geekexcel.com/add-leading-zeros-in-excel-365-simple-and-quick-ways-in-excel/
---

## Add Leading Zeros in Excel:

Excel permanently removes the Leading Zeros from any numbers automatically. To overcome this problem, you need to know how to  **Add Leading Zeros**  in Excel 365. In this article, you will learn some quick and straightforward ways to  **Add Leading Zeros**  in Excel. Let’s step into this article and know the methods.

## Change Format as Text:

- You can change the format of your data into Text. So that Excel will treat any data you enter as Text data.
- If you enter the numbers, it will take it as a text by enabling this option.
- To Change the format from general to Text, you need to follow the below steps.

1. **Select**  the  **cells**  you want to give input with leading zeros.
2. On the  **Home**  tab, in the  **Number**  Section, Click the  **Format Drop-down**  Option.
3. Select  **Text**  from the menu.

![](https://geekexcel.com/wp-content/uploads/2020/07/sun1.png)

Now, you can enter the leading zeros in your data, and it will not disappear because you changed the format as Text. So it will take the leading zeros as text values.

## Add Custom Format:

- You can add a  **Custom**   **Format ** to format numbers with leading zeros.
- To add a Custom Format, Select the ** range of cells**  that you want to add leading zeros.
- You have to open the  **Format**   **Cells**   **dialog**   **box** ,  **Right-Click**  on anywhere in the spreadsheet and click Format cell, or press CTRL + 1 keyboard shortcut to open the dialog box.
- On the  **Number ** tab, Select  **Custom**  Category from the menu.
- Now, you need to add a Custom Format in the  **Type**  option. If you want to add 6-digit numbers, including leading zeros, give six zeros in the input box.
- Hit the ** Ok**  button.

![](https://geekexcel.com/wp-content/uploads/2020/07/sun2.png)

- Now, you can see the selected cells with leading zeros. If you want to copy and paste this values, leading zeros will disappear.

![](https://geekexcel.com/wp-content/uploads/2020/07/sun3.png)

## Leading Apostrophe:

- You can Add Leading Zeros with the help of (Apostrophe) ‘ added in front of your data.
- Excel will consider the data after the apostrophe is text data, not numbers.
- When you click  **Enter** , the added leading zeros will stay visible at the cells. But ‘ will not be visible in the spreadsheet. It is visible only in the Formula bar when the cells are selected.

![](https://geekexcel.com/wp-content/uploads/2020/07/sun4.png)

## Text Function:

- You can Add Leading Zeros using  **Text**   **Function**  in Excel.
- To do this, first, you need to enter the data in the spreadsheet.
- Then, you have to  **apply the Text Function formula**  to add leading zeros.
- ” **=TEXT( Value, Format )** “uses this function in the formula bar.
- **Value**  – This is the value you want to convert as a text.
- **Format ** – Define how many digits you want to use, and add zero for each number.

![](https://geekexcel.com/wp-content/uploads/2020/07/sun5.png)

- In the above example, you can see the Format of Text Function, “=TEXT(C4,”000000″)” and it will add leading zeros in the Text Function column.

## Right Function:

- You can also use this  **Right Function**  to Add Leading Zeros to your data.
- **Right-Function**  will get the rightmost text characters from your data.
- ” **=RIGHT( Text, [Number] )**” this is the formula you need to enter in the  **Formula bar**.
- Let’s see the example.

![](https://geekexcel.com/wp-content/uploads/2020/07/sun6.png)

- The above image has shown how to Add Leading Zeros with the Right Function.

## Base Function:

- You can also use the  **Base**   **Function**  to convert the data as text data.
- “**=BASE ( Number, Base, [Min Length] )**” this the Base Function formula.
- **Number**  – In this section, you must give the input data you want to convert as text.
- **Base**  – This is the base you want to convert the data to.
- **Min**   **Length**  (optional) – This is the minimum length of your text data.

![](https://geekexcel.com/wp-content/uploads/2020/07/sun7.png)

- In the above image, you can see the  **Base**   **Function**  structure in the formula bar. Apply this function to convert the data as Text.

## Power Query and Text.Pad Start Function:

- Generally,  **Power**   **Query**  is used for data transformation, So it’s one of the solutions for Converting data into Text.
- To do this, you need to  **select**  the  **data**  and  **Open**  the  **Power**   **Query**   **Editor**  Window.
- After inserting your data into Power Query, you need to add another column using this formula ” **= Text.PadStart ( [Number], 6, “0”)** “
- Then, On the  **Add**   **Column**  tab, Select the  **Custom**   **Column**  option.
- You have to create the new name for the custom column, add the above formula on the Custom Column Formula box.
- Hit the  **Ok**  button.

![](https://geekexcel.com/wp-content/uploads/2020/07/sun8.png)

- It will create a new column with data. All data is added with leading zeros.

## Bottom Line:

In the above article, you can understand the Steps to **Add Leading Zeros in Excel 365** with practical examples. Share your **suggestions/queries** in the below comment section. Thanks for visiting **[Geek Excel.](https://geekexcel.com/)** Keep Learning!!