How to Add Leading Zeros in Excel (Two Most Common Ways)

How to Add Leading Zeros in Excel

In today’s tutorial, we’re gonna have a look at two most common ways how to add leading zeros in Excel. The first one will help when you’re yet about to enter data into a table, the other one is handy when there already are figures in the table and you only need to add leading zeros to these numbers.

Let’s start!

Would you rather watch this tutorial? Click the play button below!

Two Most Common Ways How to Add Leading Zeros in Excel

When you need to enter data starting with zero in an Excel table, the leading zeros get automatically removed in Excel and eventually, only the number itself shows in the cells.

How to Add Leading Zeros in Excel - Enter data starting with zero
How to Add Leading Zeros in Excel - The leading zeros get automatically removed in Excel

But there are at least two ways to sort this out. The first one can be applied before the numbers are entered into the table cells. In this case, you can simply format the cells as Text.

Here’s how.

First Way – Format as Text

Select the cells with which you’re gonna work, then right-click and choose the option Format Cells. In the Category field, select Text and click on OK.

How to Add Leading Zeros in Excel - Format the cells as Text
How to Add Leading Zeros in Excel - Format the cells as Text

When you insert leading zeros to the numbers in the table now, Excel will keep them in the same format as you entered.

How to Add Leading Zeros in Excel - Insert leading zeros to the numbers

Now, as soon as we’re done adding the leading zeros, Excel marks each cell with this little green triangle in the corner.

How to Add Leading Zeros in Excel - Cells marked with the little green triangle in the corner

This way, you’re being notified that there are numbers in the cells, but they’ve been formatted as text.

How to Add Leading Zeros in Excel - Numbers in the cells formatted as text

Since we’ve done this on purpose, we can click on this mark, select Ignore Error and carry on at work.

How to Add Leading Zeros in Excel - Ignore Error
How to Add Leading Zeros in Excel - Error ignored

Second Way – Custom Format

And here’s the second simple way to display leading zeros before numbers in cells. In this case, the numbers are already in the table, so the cells are not blank.

How to Add Leading Zeros in Excel -The numbers are already in the table

Again, select the area containing the data and use a right-click to select ‘Format Cells’.

How to Add Leading Zeros in Excel - Format the cells

This time, instead of choosing Text, select Custom. In the field above, enter as many zeros as is the count of digits you want the cells with the numbers to display.

How to Add Leading Zeros in Excel - select Custom

For example, I want these cells to display four-digit numbers, so I enter four zeros and then press OK.

How to Add Leading Zeros in Excel -To display four-digit numbers

Excel’s now added as many zeros in front of the numbers as needed to make all figures show four digits (along with the leading zeros).

How to Add Leading Zeros in Excel -Added as many zeros in front of the numbers as needed

Don’t miss out a great opportunity to learn:

If you found this tutorial helpful, give us a like and watch other video tutorials by EasyClick Academy. Learn how to use Excel in a quick and easy way!

Is this your first time on EasyClick? We’ll be more than happy to welcome you in our online community. Hit that Subscribe button and join the EasyClickers!

Thanks for watching and I’ll see you in the next tutorial!

Comments

Add comment

Your email address will not be published.

Your personal information will only be used for the purpose of processing this comment.

This site uses Akismet to reduce spam. Learn how your comment data is processed.