One issue that might prove problematic is that Excel likes to remove leading zeros from numbers you might want to enter. For example, if you type ‘000123’ into a cell, Excel will convert it to ‘123’ automatically, changing the number entirely. This isn’t suitable for everyone, but there are several ways around the problem. If you want to add leading zeros to Excel data, here’s what you’ll need to do.
Formatting a Cell as Text
One of the simplest ways to stop Excel from removing your leading zeros is to format your cells as text values, rather than number values. Doing this ensures that anything you type in the cell will stay exactly the same, rather than being automatically converted. To do this, select the cell (or cells) you wish to format. If you want to apply the formatting to a whole row or column, click on the column letter or row number. Right-click one of the highlighted cells, then click the Format Cells option.
From the Number menu in the Format Cells window, select Text, then click OK to save.
Any values you type in these cells now will retain their leading zeros. One problem with this method is that it only works in retrospect. You need to set the cell formatting to text values before you type any text. If you try to do it afterward, the zeros will already have disappeared, and you’ll need to readd them.
Adding a Leading Apostrophe
A quick and easy way to get the same result is to use a leading apostrophe (also called a magic apostrophe). Doing this will prevent Excel from automatically converting your number. To do this, simply type in and add an apostrophe before you enter your numerical value. When you hit Enter or move to another cell, the apostrophe will disppear from view. Your number (including leading zeros) will remain unconverted.
This tricks works by fooling Excel in thinking that you’re entering a text string, rather than a number.
Using the TEXT Function
If you’ve already entered a lot of data into your spreadsheet, with the leading zeros all removed, and you want to add them back in, the TEXT function can help. Using TEXT, you can specify how long your number should be. If the number of zeros is longer than your numerical value, the additional zeros will be added as leading zeros in front of your number. To do this, click on an empty cell and type =TEXT(A2, “000000”). Replace A2 with the cell reference containing the value you wish to convert. You can also reduce or increase the length of your value by adjusting the number of zeros in the second parameter. In this example, the numbers returned by TEXT should have six digits. Press Enter on your keyboard to confirm.
Your cell (or cells) should now contain your value in the required format. If you want to apply this format to multiple cells, you can use the handle in the bottom-right corner of the cell to repeat the formula across your data range.
Adding Custom Formatting
The method above requires you to keep the original values that are missing the leading zeros in your spreadsheet alongside your updated values. Using custom formatting, you can convert the values you have already entered without the need to generate a second set. To do this, select the values that you want to amend. Right-click on one of them, and choose the Format Cells option.
In the Number tab of the Format Cells window, select the Custom option. In the Type field, enter the same number of zeros to specify how long you wish the number value to be, then click OK to save. For instance, typing 000000 will ensure that each number has six digits. These will be added to the start of your number as leading zeros.
This will convert the values to your desired format, adding leading zeros as required.
Adding a Set Number of Zeros
You may want to ensure that a set number of leading zeros is always added to your values. Excel allows you to do this by combining your original values with an additional number string. To do this, click on an empty cell and type =(“00″&A2), replacing A2 with the cell reference of the original value.
For instance, if the value 19874 is in cell A3, this formula will show 0019874 as the new value in the new cell.
Many Methods, Same Result
As the methods above show, there are plenty of options available to you if you’re looking t0 add leading zeros to cells in Excel. Excel can be used for more than just manipulating simple numerical values, however. You could learn how to use goal seek in Excel to solve optimization problems, convert text to date formats of choice, or manipulate your data extensively using Excel pivot tables.
