How to keep leading zeros in Excel values

Ever been irritated by how Excel treats your phone numbers, credit card numbers, postal codes, ZIP codes, product codes, or account numbers?

There's one thing these all have in common: they 𝘮𝘢𝘺 start with 0.

By default, if you punch in any number beginning with 0, Excel automatically removes the 0 (or successive 0s) to convert the value to a proper number.

Sometimes, this is desirable, but all too often, it's not. Thankfully, Excel received an update a while ago to address this long-standing issue and put you in control.

To turn off this feature, do the following:

1️⃣ Go to File ＞ Options ＞ Data.
2️⃣ In the Automatic Data Conversion section, uncheck the 'Remove leading zeros and convert to a number' box.
3️⃣ Select OK.

Excel will now respect any 0-leading values you input and affix a preceding single quotation mark (') to signify text.

Do you find Excel's default behaviour for this useful or a sheer annoyance?

#exceleration #excel #globalexcelsummit

---

The Global Excel Summit is the world's largest gathering of Microsoft Excel users and experts.

Find us on: