How to enter zeros before numbers in a Microsoft Excel cell

We all use Microsoft Excel or any other spreadsheet program to manage our finances and to deal with some official work, which can only be accomplished with the help of the application. Microsoft excel is an intelligent application, which can automatically recognize the type of data that you are entering within a cell and can make necessary arrangements to carry out different kinds of operations with the data entered by you. But sometimes, you can face problems just because of this auto recognition capability. For example, if you need to put in zeros before a number, those zeroes will automatically be omitted by pressing the enter key. 

Furthermore, if you try to enter any kind of mathematical operator in any of the cells, that will be displayed as an error and Microsoft Excel can also consider it as a formula that might not be the one that you are entering.  But that doesn’t mean you cannot enter numbers with zeros before it or any other mathematical operator back to back in any of the cells in Microsoft Excel. There are a few workarounds, which can help you enter numbers with zeros before it and mathematical operators and make Microsoft Excel treat that data within that cell as text

So, without any further delay, let’s get started with how we can do that on Microsoft Excel. This is also going to work on other Microsoft Excel alternatives like OpenOffice LibreOffice and others.

Entering zeros before numbers in Microsoft Excel


Select the cells where you want to enter numbers with zeros before it or mathematical operators, right-click on one of them and click on ‘Format Cells…’.

Format Cells

Format Cells option of Excel

Under the ‘Number’ tab, which should be selected by default, click on ‘Text’ under ‘Category’ and click on ‘OK’ afterwards.

Number tab in Format Cells Excel

Number tab in Format Cells Excel

Now, even if you enter numbers in one of the cells, that will be treated as a text. Even if you enter numbers with zeros before it, that will be treated as a text and you can get a small warning on the top left corner of the cell. It will just tell you that the number is stored as a text.  You can also convert that text to number, you can also do that with just a single click. However, those additional zeros before the number will be deleted as those additional zeros do not have any numerical value.

Enter zeros before numbers in Excel 30

Enter zeros before numbers in Excel

There is also a simple way, you can make Microsoft Excel treat the numbers or any other data that you enter, as text. Before you type in the data, just use a single quote or (‘), before the text that you are going to enter. This will make Microsoft Excel treat the data in that cell as text and you can add any number of zeros before the number.

Microsoft Excel treat the data in that cell as text

Microsoft Excel treat the data in that cell as text

This way, you can enter mobile numbers, which has zero before it if you are living in India, and there can literally be a number of situations when you need to enter 0 before a number or multiple mathematical operators in a single cell. Even though, there are additional data formats which can make Microsoft Excel consider the data as phone numbers are currencies, the way, which I have discussed here to make Microsoft Excel treat cell contents as text can come in handy in a number of different situations.

So, that was all about how you can enter zeros before a number for multiple mathematical operators within a cell in Microsoft Excel. Do you have any questions? Feel free to comment on the same below.

Leave a Reply

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