How to Enter Zero Before a Number in Excel 2016

Have you ever tried putting a “0” before a number in the Excel sheets? In mathematics, there is no use of 0 before a number as 012 is equal to 12. Yet, there are many circumstances in which we need a 0 before a number.

For instance, in phone numbers or bank account numbers, the 0 has its importance even it exists at the start of a number. When you put a zero (0) before a number in the excel sheets, the Excel will remove the “0” automatically because for MS Excel 01 is same as 1.

If you need to store a phone number, a bank account number, or in any other scenario, you need to have that zero before that number.

How can you put a “0” before any number in MS Excel? There are different ways to accomplish this task.

One simple way is to put an Apostrophe (‘) before the number.

You will see a green marking on the left top corner of the cell.

Click on the cell, you will notice a yellow notification mark on the left side of the cell with a dropdown arrow. Click on the notification and click on Ignore Error.

The number will be stored as text and the zero will be staying with the number also the green triangular mark will also disappear.

A stepwise guide to another method is as under.

Open the MS Excel and enter your desired number in any cell (for Example 123)

Right-click on the cell and click on the “Format Cells” option. This will open a new dialogue box for cell formatting.

From the “Format Cells” window, click on the “Numbers” tab and select the “Custom” option form the category on the left.

Choose “0” from the Type section of the “Numbers” tab.

Now type the zeros as many as the digits of your number. For instance, if you want to add 0 before 123, type the four zeros as “0000”.

Click “OK” from the bottom of the window. Now your number will contain “0” at the start.

In this way, you can add 0 to the phone numbers, account numbers, and other data, which require 0 at the start of the numbers in MS Excel.

The main difference between the two methods discussed here is that the number in the first method is stored as text and it cannot be included in the mathematical formulae of excel while on the other hand the number in the second method is stored as a number.

Leave a Reply