How to enter zeros before numbers in a Microsoft Excel cell

- Advertisment -
- Advertisement -

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.

Sarbasish Basu
From B.Tech (Hons.) Electronics & Instrumentation Engineer to Photography and Writing blogs; he ultimately has answers to everything. Oh, and he's also quite good in rapidly building a relationship and set up the trust; his articles are proof of that...


Please enter your comment!
Please enter your name here

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

Latest Posts

How to create Ubuntu 20.04 bootable USB drive on Windows

If you are Ubuntu's fan and want to try out its latest Focal Fossa 20.04 LTS Linux distro, then...

Different common BSOD (Blue screen of death) on Windows

Here are some common BSOD (Blue screen of death) that you might come across on a Windows 10/8/7 computer,...

How to get started with, and use Bixby Routines on a modern Samsung

The modern Samsung handsets come with a number of new features and one of the most useful features that...

How to install Docker-CE on Ubuntu 20.04 LTS Focal Fossa

Are you using Ubuntu 20.04 LTS and want to install Docker Composer to start with container-based virtual machines then...
- Advertisement -

How to change screen brightness in Ubuntu using command terminal

Do you want to control or reduce the brightness of the Ubuntu 18.04/19.04/20.04 or in earlier versions of this...

How to install VirtualBox on Ubuntu 20.04 Focal Fossa

The final launch of the Ubuntu 20.04 Focal Fossa is around the corner however the beta builds are available...

- Advertisment -
- Advertisement -

You might also likeRELATED
Recommended to you