WPS Office

Free All-in-One Office Suite with PDF Editor


Edit Word, Excel, and PPT for FREE.


Read, edit, and convert PDFs with the powerful PDF toolkit.


Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android


How to add a leading zero in excel

July 27, 2022

Do you want to learn how to add a leading zero in excel? Excel is a versatile, feature-rich tool that allows you to calculate numbers and keep your records organized. But, when you try to write leading zeros in the cells, Excel will remove the leading zeros automatically.

When you want to add phone numbers or postal codes in Excel, it can be a bit of a hassle, but luckily there are different methods you can use to write a leading zero in your worksheets. Here are the steps on how to add a leading zero in excel without being deleted.

How to add a leading zero in excel online?

If you want to keep zeros in your spreadsheets, you first need to know that there are different options you can use to keep them. One of the options is to change the format of a range to text; this will treat any data you enter as text values. Follow these steps:

1. Change the format from General to Text. Then you will need to select the range of cells in which you want to enter leading zeros. Go to Home, and in the Numbers section, you will need to click on the Format Dropdown section. Choose Text from the formatting options.

By following these steps, when you try to enter numbers with leading zeros, Excel will not delete them because you will be entering them as text values instead of numbers.

How to add a leading zero in excel with a custom format?

You will also be able to add a custom format to write numbers with leading zeros. To learn this process of how to add a leading zero in excel 2016, you will need to follow the below steps:

1. The first thing you need to do is add a custom format to display leading zeros. Select the cell range to which you want to add leading zeros, and then open the Format Cells dialog box. To continue, you will need to click right and select Format Cells, use the keyboard shortcut Ctrl + 1.

Then go to the Number tab and select Custom from the category options. You must add a new custom format in the Type input. If you want the total number of digits to be 6, including leading zeros, you will need to add 000000 as a custom format. Press the OK button.

2. After applying the custom format, you can see that the data has not been changed. To continue, you will need to select a formatted cell that will continue to display the original number in the formula bar. Note that they will only appear in the spreadsheet in the leading zero formats.

Note: If you copy and paste the data as values, you will lose the leading zeros.

How to add a leading zero in excel using the leading apostrophe?

If you want to learn how to add a leading zero in excel 2019, you should know that there are different easy options that you can use. You can use the option to enter a number as text using a leading apostrophe by following these steps:

1. You can keep leading zeros while entering your data. It is a quick and easy method when entering data.

2. type an apostrophe ' before any number. This will tell Excel that the data should be text, not a number. When you press Enter, the leading zeros will not be removed and remain visible in your spreadsheet. The apostrophe will not be visible in the worksheet, but it will be there, and you can see it in the formula bar when the active cell cursor is in the cell.

As you can see, the process of how to add a leading zero in excel mac is quick and easy. You will be able to preserve your leading zeros in Excel and improve your work experience.

Have you learned how to add a leading zero in excel? Your leading zeros will no longer be removed from your worksheets, and add any number you want in your cells.

Also, it will let you download WPS Office to create and edit PowerPoint, Word, and Excel documents for free.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.