HomeMicrosoft ExcelHow to Insert 0 Before Any Number in Excel?

How to Insert 0 Before Any Number in Excel?

Excel automatically removes zeros from the beginning of numbers when you enter them. This can cause problems with phone numbers, IDs, bank account numbers, etc. For example, write 000012 and hit Enter; Excel will automatically remove the leading zeros.

If you don’t know how to make Excel keep those leading zeroes, it can be frustrating. Don’t worry, there are a few ways through which you can accomplish your goal in Excel.

The goal of this gearupwindows article is to help you add leading 0 to a number in Excel. Here is how to do it.

How to Add 0 Before Any Number in Excel?

You can insert or add 0 before any number in an Excel worksheet in several ways. We will guide you through these procedures.

Format as Text

It is possible to change the format of a range in Excel to text. If you do this, Excel will treat any numbers you enter as text values.

Step 1. Open a new or existing Excel worksheet.

Step 2. Select a range of cells you want to enter leading zeros in.

Step 3. In the menu bar, click on the Home tab to change the ribbon.

Step 4. Click on the Number Format drop-down selection in the Number section.

Step 5. Select Text in the drop-down list.

Because text values are entered instead of numbers, numbers with leading zeros will not disappear when you enter them.

Format Cells

Using the Format Cells dialog, you can also add leading 0 before a number in Excel. This is useful if you have similar types of data, such as several 10-digit phone numbers, or if you want to display several numbers in a similar manner. In such cases, you don’t need to add 0 before a number, Excel will do it for you. The Excel spreadsheet will add the leading 0 to a 9-digit number when you press Enter. Here is how to do it.

Step 1. Open a new or existing Excel worksheet.

Step 2. Select several blank or filled cells where you want to display numbers in a similar manner.

Step 3. Right-click on the cells and select the Format Cells option to open the Format Cells dialog box.

Step 4. Switch to the Number tab.

Step 5. Select Custom in the left sidebar.

Step 6. On the “Type” box, enter several 0 you want to see in chosen cells.

Step 7. Finally, click the OK button.

As an example, if you have entered five 0 in the above step, when you type any number, such as 1, in an Excel cell and press Enter, Excel will automatically display 1 as 00001. That means you don’t need to type 00001 to match with other numbers in your cells.

Leading Apostrophe

If you use a leading apostrophe () to force Excel to enter a number as text, you will keep the zeroes in front as you enter your data. This method is quick and easy if you have to enter a few numbers leading to 0. You can do this by typing  before any numbers. This will make Excel see the data as text and not a number.


In conclusion, adding leading zeros to numbers in Excel can be essential for accurately displaying data, such as phone numbers, IDs, and bank account numbers. However, Excel automatically removes leading zeros, which can be frustrating for users. Fortunately, there are several ways to add leading zeros in Excel, including formatting as text, using the format cells dialog, and using a leading apostrophe. By following the steps outlined above, users can easily add leading zeros to their numbers in Excel, ensuring accurate data representation.

Leave a Reply

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

How to Rotate Text in Microsoft Word?

Microsoft Word is a versatile word processing software that offers a wide range of tools to make your documents more visually appealing and informative....

How to Blur Image Background in Photos on Windows 11?

In the ever-evolving world of technology, Windows 11 has brought forth a plethora of exciting features, and one such feature is the ability to...

How to Enable or Disable Widget Notifications on Windows 11?

Windows 11 introduced a feature called Widgets, designed to provide users with quick access to information and enhance productivity. These Widgets offer a variety...

Get Your Free MiniTool ShadowMaker Pro License Today – Limited Time Giveaway!

Are you tired of constantly worrying about losing your important files and data due to unexpected system crashes or hardware failures? Look no further...

Free Giveaway: MobiKin Backup Manager for Android – Safeguard Your Data!

In today's digital age, our smartphones have become our lifelines, containing a treasure trove of personal information, memories, and essential data. Unfortunately, the risk...

Related Posts