Excel Leading Zeros Setting (Beta) – with pictures + video tutorial

We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)

If using Leading Zeros in Excel feels like the bane of your existence AND you have joined the Office 365 Insiders program, you may have access to a Leading Zeros Beta.

NOTE: Because this is a beta, there is no guarantee that it will be rolled out. Just something to keep in mind.

What is a Leading Zero in Excel?

The meaning of Leading Zero in Excel is a zero (or zeros) placed in a cell before any other character.

For example, 00123Y contains two leading zeros.

What makes Leading Zeros so difficult to work with?

The issue with leading zeros in Excel is that, unless you apply special formatting, they simply disappear from the cell.

Why do they disappear? Technically, no numbers actually begin with zero. Therefore, they get deleted.

This can be a problem if you’re working with values that need a zero in front. This could be ID numbers, some zip codes, product codes, etc.

NEXT: Learn ways to keep Leading Zeros from disappearing in Excel

Leading Zeros Beta in Excel

WATCH: Leading Zeros Beta Setting in Excel (video tutorial)

As of summer 2022, Excel is beta testing a setting that, if turned on, will keep the leading zeros in the cell from disappearing.

This leading zeros beta setting works by detecting if a cell you are typing in contains a leading zero. If it detects any leading zeros, it’ll convert the cell to Text formatting, thus keeping the leading zero.

NOTE: The cell will be changed to Text formatting in order to keep the Leading Zero. Click here to learn other ways to keep leading zeros from disappearing in Excel.

Turning on the Leading Zeros Beta

Because this is a beta, not everyone may have access to it. To see if you have the Leading Zeros Beta and to activate it:

1. Go to the File tab in your Excel ribbon and click “Options”
2. Then, click on “Advanced,” under the Categories
3. Scroll until you see the “Automatic Data Conversion (Beta)” section
4. If you have access to the “Automatic Data Conversion (Beta),” uncheck the option that reads “Remove leading zeros and convert to a number”

Unchecking this option will allow the leading zeros to remain in a cell but will also convert the cell to Text formatting.

When this option is checked, Excel will behave as it traditionally has and remove any leading zeros from cells.

TIP: Toggling this setting on and off will NOT affect prior entries that have leading zeros. In other words, you can uncheck it and enter data with leading zeros. Then, if you need to, check it off again to work without them. Any cells already entered that contain leading zeros will not be affected.

NEXT: Learn ways to keep Leading Zeros from disappearing in Excel

FYI, this was created using the desktop version of Excel in Microsoft 365.

---