Have you ever tried to enter some data like **000123** into Excel?

You’ll probably quickly notice Excel will automatically remove the leading zeros from any numbers.

This can be really annoying if you want those leading zeros in your data and you don’t know how to make Excel keep them.

Fortunately there are quite a few ways to pad your numbers with zeros at the start.

In this post, I’ll show you 9 ways to add or keep those leading zeros to your numbers.

Table of Contents

## Video Tutorial

## Format as Text

There is an option to change the format of a range to text.

Doing this will treat any data you enter as text values, even if they are numbers. This will cause Excel to keep any leading zeros in your numbers.

Change the format from General to Text.

- Select the range of cells you want to enter leading zeros in.
- Go to the
**Home**tab. - In the
**Numbers**section click on the**Format Dropdown**selection. - Choose
**Text**from the format options.

Now if you try to enter numbers with leading zeros, they won’t disappear because they are entered as text values instead of numbers.

## Custom Format

You can add a custom formatting to format numbers with leading zeros.

They will only appear to have leading zeros though. The underlying data won’t be changed into text with the added zeros.

Add a custom format to show leading zeros.

- Select the range of cells you want to add leading zeros to and open up the Format Cells dialog box.
- Right click and choose Format Cells.
- Use the Ctrl + 1 keyboard shortcut.

- Go to the
**Number**tab. - Select
**Custom**from the category options. - Add a new custom format in the
**Type**input. If you want the total number of digits including any leading zeros to be 6 then add**000000**as the custom format. - Press the
**OK**button.

After applying the custom format you will notice the data has not actually been changed.

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

This also means if you copy and paste the data as values, you will lose the leading zeros.

## Leading Apostrophe

You can force Excel to enter a number as text by using a leading apostrophe.

This means you’ll be able to keep those zeros in front as you’re entering your data.

This method is quick and easy while entering data. Just type a `'`

character before any numbers. This will tell Excel the data is meant to be text and not a number.

When you press Enter, the leading zeros will stay visible in the worksheet. The `'`

will not be visible in the worksheet, but is still there and can be seen in the formula bar when the active cell cursor is on the cell.

## TEXT Function

The **TEXT** function will let you apply a custom formatting to any number data already in your spreadsheet.

`= TEXT ( Value, Format)`

**Value**– This is the value you want to convert to text and apply formatting to.**Format**– This is the formatting to apply.

`= TEXT ( B3, "000000" )`

If you wanted to add zeros to a number in cell **B3** so that the total number of digits is **6**, then you can use the above formula.

## RIGHT Function

Another way to get your zeros in front with a formula is using the **RIGHT** function. You can concatenate a string of zeros to the number and then slice off the extras using the **RIGHT** function.

The **RIGHT** function will extract the right most N characters from a text value.

`= RIGHT ( Text, [Number])`

**Text**– This is the text you want to extract characters from.**Number**(*Optional*)- This is the number of characters to extract from the text. If this argument is not entered, then only the first character will be extracted.

`= RIGHT ( "000000" & B3, 6 )`

The above formula will concatenate several zeros to the start of a number in cell **B3**, then it will return the right most **6** characters resulting in some leading zeros.

## BASE Function

You’ll notice this article describes 9 ways to add leading zeros, but my YouTube video only shows 8 ways.

That’s because I didn’t know you could use the **BASE** function to add leading zeros until someone mentioned it in the video comments.

The **BASE** functions allows you to converts a number into a text representation with a given base.

The numbers you usually use are base 10, but you could use this function to convert a base 10 number into a base 2 (binary) representation.

`= BASE ( Number, Base, [MinLength])`

**Number**– This is the number you want to convert to a text value in another base.**Base**– This is the base you want to convert the value to.**MinLength**(*Optional*) – This is the minimum length of the characters of the converted text value.

`= BASE ( B3, 10, 6)`

The above formula will convert a number in cell **B3** into base **10** (it’s already a base 10 number but this will keep it as base 10) and convert it to a text value with at least **6** characters. If the converted value is less than 6 characters, it will pad the value with zeros to get a minimum of 6 characters.

Definitely a cool new use for a function I otherwise never use.

## Power Query and Text.PadStart Function

Power query is all about data transformation, so it’s no surprise you can transform your numbers to add leading zeros in power query.

Unfortunately, it’s not a command that’s in the ribbon and it will require using a power query function in a custom column.

`= Text.PadStart([Number],6,"0")`

After importing your data into power query, you can add a new column with the above formula to create leading zeros.

- Go to the
**Add Column**tab. - Select
**Custom Column**from the ribbon. - Create a new name for the custom column and add the above formula in the
**Custom column formula**box. - Press the
**OK**button.

This will create a new column where each row has at least 6 characters padded by zeros if needed.

## Pivot Table and DAX Measure

If you want to add leading zeros to the numbers in your pivot table, you can do so by using the data model and some DAX measures.

`= CONCATENATEX ( Numbers, FORMAT ( Numbers[Number], "000000" ), ", " )`

You can add the above measure formula to display your numbers in a pivot table with leading zeros.

Notice the **FORMAT** DAX function is exactly like Excel’s **TEXT** function, but DAX measures need to aggregate to a single value so you need to wrap it inside a **CONCATENATEX** function.

When you add the new measure into the **Values** area of a pivot table it will result in a comma separated list of all the numbers with the leading zeros added.

## Power Pivot Calculated Column

There is another option to add zeros into you pivot table. You can add them into a calculated column in **Power Pivot**.

This way you can use the new column in the **Filter**, **Rows** or **Column** area of a pivot table.

`= FORMAT ( Numbers[Number], "000000" )`

In the Power Pivot add-in, you can add a new column and use the **FORMAT** function to create leading zeros in the column with a formula like above.

A calculated column calculates a value for each row, so there is no need to wrap the function inside a **CONCATENATEX** function like in the DAX measure.

Now you can use this new column inside a pivot table’s **Rows** area just like any other column of data!

## Conclusions

It can be frustrating to see all your zeros disappear when you don’t know why it’s happening or how to prevent it.

As you can see, there are lots of ways to make sure all those zeros stick around in your data.

Whatever your requirement, there is surely a good solution for you.

Do you have a favourite way that I missed?

Hey boss, I want and need the leading zeros in my data as I am doing an import into another sucky microsoft product (powerapps) and the zeros must be there.

my data column is formatted General and contains CS000000035678. (the last 5 digits varies) i do a replace all to remove CS0000 from all values in the column. of course the leading zeros are removed and I end up with 35678. thats a given.

using a custom format 00000000 does not work for my import because the zeros are not actually part of the data.

I format the column to text and do the replace all, again 35678 is the result.

I edit a cell in the column and delete the CS0000 and the zeros remained and I get 00035678. (looks like I am going to have some tired fingers after manually updating 4000 cells)

Thanks so much sucky microsft product. ( i am in my 50s and I have seen this so many times with M$ products. why couldnt I get an apple-centric job! LOL) M$ does many great things but then they fail fail fail at tiny things that should work. The replace has something in it that even in a text formatted column it removes zeros.

I am going to try the apostrophe, doubt my import process will accept it though.

hit me back if you have a clue/solution for me. Thanks DJ

You could import to power apps as is, then take the last 8 characters with RIGHT function in power apps.

Thank you for the reply. Your method of using the BASE formula worked. it even allowed me to add an apostrophe in front of the zeros. The apostrophe does not show in the cell, but does show in the formula bar when you click on the cell.

Great fix for me and others.