Top Ways to Add a Zero in Front of a Number in Excel (+ video tutorial)

We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)

Sometimes referred to as “leading zeros,” it can be surprisingly tricky to add a zero in front of a number in Excel.

Consider a set of numbers in Excel where you want them all to be the same number of characters but they’re not, so you need to add a zero in front of them. You type a zero in the front, but then it disappears! GRRRRRR!!!!  

Excel automatically removes the zeros from the beginning of numbers because no numbers naturally begin with zero. So we have to get a little creative.

3 Ways to Add a Zero in Front of a Number in Excel (+ a bonus way!)

Watch the tutorial: How to Add a Zero in Front of a Number in Excel

1.      Use an Apostrophe to Add a Zero in Front

If you only need to add zeros in front of a few numbers, beginning the cell contents with an apostrophe (‘) is the easiest way to go.

Using an apostrophe at the beginning of a cell automatically converts its contents to text formatting, allowing you to add as many zeros, or other characters, in front of the numbers as you want.

This is done one cell at a time so it can be time-consuming; I don’t recommend it if you need to add zeros to a range of cells.

Keep in Mind…

Because this converts the cell contents to text formatting, Excel will flag these as an error. Specifically, a “Number Stored as Text” error.

You’ll see this flagged with a tiny, green triangle at the top-left corner of each cell formatted this way.

If you need to use the values in these cells for any calculations, this method may not be for you as it could lead to calculation errors. If so, you should leave your cells formatted as numbers and use the third method below.

If you simply want to hide the green triangle, select all the cells with the error and click “Ignore Error.”

2.      Use the REPT Function to Add Leading Zeros

The second way you can add zeros in front of numbers in Excel is by using the Repeat or REPT function.

REPT works by repeating whatever character you want a certain number of times.

The REPT function needs 2 items:

1. the text you want to be repeated and
2. the number of times it should be repeated

So you can use a 0 as the text to repeat, followed by the number of times you would like it to appear.

For example, to create a list where all cells begin with two zeros, you can use REPT to combine the number of zeros you need with the contents of the cells.

It would look something like this:

=REPT(0,2)&A2

Here, we are repeating 0 two times and using the “&” to join the result with the contents of cell A2.

Then, if you like, you can replace the original values with the formula results – pasting them as values so that you do not undo the new formatting.

Keep in Mind…

If you want for all your cells to contain the same number of characters, you have to be careful when using REPT and copying it down to other cells.

REPT is ideal if all the cells you are starting with contain the same number of characters.

If they do not, adding the same number of zeros to all will not result in a uniform list.

To get around this, you can use an IF function along with REPT. For example, you could try something like:

=IF((LEN(A2)<6),REPT(0,6-LEN(A2))&A2,A2)

3.      Apply Custom Formatting to Add Leading Zeros

The third way you can add zeros in front of a number in Excel is by using custom formatting. You can format the cells to display a set quantity of zeros in front.

Let’s say you want to format your cells to display 6 numbers. To do this:

1. Select all the cells you need to add a zero to, right-click and select Format Cells
2. In the Number tab, select Custom
3. Under Type, enter 6 zeros
4. Click OK

With this special formatting, Excel will force the cells to display at least 6 numbers. If a cell contains less than 6 numbers, Excel will correct itself by inserting the necessary number of zeros at the beginning of the cell.

4.     BONUS TIP: Keep Zeros in Front by Updating the Leading Zeros Settings (still in beta)

If you subscribe to Office 365, you likely have access to some betas.

Excel is, as of the time I’m writing this update in November 2022, beta testing a setting that lets you easily add zeros in front of other numbers (i.e., Leading Zeros) simply by unchecking a box.

WATCH: Excel Leading Zeros Setting (Beta) – Video Tutorial

NOTE: Being that this is a beta, there is, of course, no guarantee that it will be rolled out. Just something to keep in mind.

You can check if you have the Leading Zeros setting by going to your Excel Options. Here, you can also activate it.

Activate the Leading Zeros setting by:

1. Going to your Excel File menu
2. Click on Options
3. In Excel Options, click on AdvancedLook for the section labeled “Automatic Data Conversion (BETA)”
4. Uncheck the box labeled “Remove leading zeros and convert to a number”
5. Click OK

NOTE: This setting will convert the contents of cells with zeros in front to Text formatting.

---

---

FYI, this was created using the desktop version of Excel in Microsoft 365.