How to Add Leading Zeros in Excel? 4 Easy Methods
(Note: This guide on how to add leading zeros in Excel is suitable for all Excel versions including Office 365)
Excel, despite being one of the most commonly used spreadsheet software, can be confusing to use at times. Leading zeros in Excel is a good case in point. I am almost certain that you tried adding leading zeros to your data (something like 007281) but with no success.
This is because Excel automatically removes all the leading zeros from numbers since it prioritizes calculations over formatting. Though the intention behind this seems reasonable, it can be very frustrating if you want to add leading zeroes to your data for maintaining records.
Don’t worry. In this guide, I’ll teach you how to add leading zeros in Excel, using 4 easy methods.
Related:
The Excel SMALL Function – 3 Best Examples
How to Make An Excel Forecast Sheet in 3 Steps – The Best Guide
How to Use Excel COUNTIFS: The Best Guide
Add Leading Zeroes by Formatting to Text
The easiest way to add leading zeros in Excel is to simply convert the formatting from Number to Text.
But, use this method only if you are not planning to use the converted numbers in some form of calculations.
Now, let us see how to do this:
- Select the range of cells where you want to insert the numbers with leading zeroes.
- Click on the drop-down menu in the Number group of the Home tab and choose Text from the list.
- Now try adding leading zeros to your numbers. This time, Excel will allow you to keep the newly added leading zeroes.
Add Leading Zeros Using Custom Formatting
Sometimes you may want to retain the number formatting of these leading zeros for the sake of calculations. If that is the case, converting the numbers to text doesn’t make sense.
We will have to find a workaround to do this. Let us see how:
- Select the relevant cell range where you want to insert the leading zeroes.
- Press the Ctrl+1 keyboard shortcut. This will open the Format Cells dialog box. Alternatively, you can click on the small arrow at the bottom corner of the Number group of the Home tab.
- Go to the Numbers tab in the Format Cells dialog box and click on the Custom category.
- Enter the desired number of digits, inside the ‘Type’ box and click OK.
- This will make Excel display your numbers with the desired number of leading zeros automatically attached to them. Let us say, you entered ‘0000’ in the Type box. When you enter 45 in any of the selected cells, it will be displayed as 0045. Similarly, if you enter 123, it will be displayed as 0123.
Also Read:
How to Add Error Bars in Excel? 7 Best Methods
How to Superscript in Excel? (9 Best Methods)
Excel Goal Seek—the Easiest Guide (3 Examples)
Add Leading Zeros Using the TEXT Function
What if you want to enjoy the benefits of both formats? That is, keep your numbers for calculations but display them as a text for ease of use.
Fortunately, this is possible in Excel, thanks to the TEXT function.
To do this, just follow these steps:
- Let us say you have the numbers arranged in the column D2:D10. Select the range E2:E10 and enter the formula : TEXT(D2, “0000”), where you can change the number of zeroes according to your preferences.
- Double click on the Fill Handle or simply use the Ctrl+Enter shortcut to enter this formula in your selection.
This will immediately convert all your numbers with the relevant leading zeros in the E2:E10 column, while still retaining the numbers in the D2:D10 column.
You can keep using the original column for calculations and the new one for viewing or presentations.
Add Leading Zeros in Excel Using an Apostrophe
Don’t have time for all this? Thankfully, there is an easy way to instantly force Excel to add leading zeroes.
All you need to do is append an apostrophe (‘) at the beginning of every number. This will force Excel to display the number as it is.
But keep in mind that this actually converts the numbers to text and hence cannot be used in calculations.
Suggested Reads:
How to Shade Every Other Row in Excel? (5 Best Methods)
How to Extract an Excel Substring? – 6 Best Methods
How to Split Cells in Excel? 3 Easy Methods
Closing Thoughts
Leading zeroes can sometimes be very critical to maintaining the formatting uniformity of your database. But, unfortunately, Excel does not directly allow you to keep them in your data.
You can bypass this restriction using any one of the methods discussed above, which suits you best. Whatever you do, always be mindful of the final formatting type of your data.
If you need more high-quality Excel guides, please check out our free Excel resources center.
Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!