How to add leading zeros to numbers in Excel

To display numeric data in Excel documents with different purposes, sometimes you need to have a leading zero. Come to our tutorial, TipsMake.com will guide you how to add the number 9 to the beginning of the sequence.

How to add leading zeros to numbers in Excel Picture 1

Parameter cells in Excel are classified into different data cells, for example, the parameter cell contains a range of numbers, when you finish filling that parameter cell, they automatically switch to the numeric data cell type. .

When you enter a leading sequence of zeros, Excel automatically cleans up with the most accurate number for that range, which means eliminating the zero.

How to add leading zeros to numbers in Excel Picture 2

So adding zeros to the beginning of the sequence can be done in many ways, but you need to note that you want to keep the numeric data cell for that parameter cell or not, otherwise we can turn it. Becoming a text cell, Excel will not automatically shorten your number range.

1. Add zeros at the beginning of the sequence with the resulting parameter data text

To convert a parameter cell to a text data type, you can do the following:

Method 1: Convert the parameter cell type before filling the number range

To convert the parameter cell type into a text data box, click on the data cell or you can select an entire data array.

Next, open the Home ribbon on the toolbar, and then click Number Format in the Number section .

Then you select Text in the list just displayed.

How to add leading zeros to numbers in Excel Picture 3

Then you enter the sequence of zeros at the input in the converted data cell, you will see that your zero has not been omitted as before but there is a blue triangle in Excel. at the corner.

How to add leading zeros to numbers in Excel Picture 4

This is an Excel reminder that the data you enter is a continuous number, and it asks if you entered the wrong cell and want to return the current cell to the numeric data cell.

Of course we intentionally so, you need to click on the triangle and select Ignore Error to ignore the error that Excel mentioned, the triangle will disappear.

How to add leading zeros to numbers in Excel Picture 5

Method 2: Format the parameter cell immediately while entering a number range

It is not necessary to select a parameter cell and then convert before you enter the number range, you can choose how to insert apostrophes in front of the required number range.

That means you have defaulted this parameter cell to be a text field.

How to add leading zeros to numbers in Excel Picture 6

After you press Enter you will also see Excel error message, you follow the same way above, ignoring this error message Ignore Error .

How to add leading zeros to numbers in Excel Picture 7

For both of the above, you can highlight all the data boxes with error messages and then choose to remove this message at once by selecting Ignore Error .

How to add leading zeros to numbers in Excel Picture 8

2. Add zeros at the beginning of the sequence but keep the numeric data cell intact

To add zeros to the beginning of the sequence but keep the numeric cell intact, do the following.

First, right-click on the parameter cell you need to enter the number sequence at the beginning.

Next, select Format Cells from the displayed list of options.

How to add leading zeros to numbers in Excel Picture 9

You open the Number tab in the Format Cells window box that appears.

Continue to change to Special in Category .

You choose Zip Code in the Type section .

How to add leading zeros to numbers in Excel Picture 10

Then you enter the sequence of numbers with 0 at the beginning as usual.

How to add leading zeros to numbers in Excel Picture 11

As you can see, the two numbers in Zip Code form can still perform calculations as usual.

How to add leading zeros to numbers in Excel Picture 12

Thank you for following our article TipsMake.com on how to add leading zeros to numbers in Excel. Wish you can successfully perform the operation of this trick.