NEW! Enter zeros before a number in Excel and keep the leading zeros #shorts

Microsoft Excel 365 is getting a new feature that allows you to keep leading zeros. There is no need for workarounds anymore. Just unchecking a simple checkmark in the options of Excel. This way you can enter zero values before a number and they remain in the cell. No more disappearing zeros in Excel!

Availability: This feature is currently in Office 365 Insider Beta.

🚩Let’s connect on social:

👉 This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#Excel #office365

[LeilaGharani]

This feature has moved from "Advanced" to the "Data" Section.

[johann.g.olafsson]

You can always force text by typing ' before the number. 0020 becomes 20 but '0020 becomes 0020.

[Teddypally]

When I want leading zeros, I change the cell's format. I select the cells, right click, go to format cells, Number tab, select custom, then replace General with "0#" if I want 01, 02 etc, else "00#" if I want 001, 002 etc.

[AdrianWolf_in_TO]

Problem is that this applies globally / all the time. Most of the time I only want a single column to show the leading zeroes. I think it's still easier to just set only the desired column to show leading zeros using formatting rules.

[rubenwelten]

Looks like the format changed to text so cell formatting (00#) seems far superior and doesn't have any compatibility issues.

[andrewcharlesmoss]

This will certainly be a welcome addition. It is annoying to see your leading zeros stripped away.

[BrainyBrunetteBarbie]

Awesome to hear that this new feature is coming soon!!!

[gopalakrishnanmanikandan]

Good idea of MS to give such features in Excel. Will enhance the usage of excel

[exceltech]

I used to do the same by changing cell number format. but by that way we can put a specific number of 0 at a time. The great news is the new feature. It will allow you the number of zeros you want. thanks for sharing with us.

[abdulsaidmkamara]

Yay ! Please check if that will work for CSV files which are used for banking and data uploads

[Excelambda]

Great news, great "short"!!
Usually we need leading 0's to "equalize" largest nr. in an array of numbers in terms of maximum nr. of digits.
If we set in A1:A3
12
12345
123

a single cell formula to solve this:

=TEXT(A1:A3, REPT(0, MAX(LEN(A1:A3))))
or:
=LET(a, A1:A3, REPLACE(a, 1, , REPT(0, MAX(LEN(a))-LEN(a))))

then if you copy the entire array result, we can paste it as values over the initial array, no need to do it manually, and, if we need sum
=SUM(--A1:A3)

[MutazAnani]

It's very helpful when importing data to SF

[janpack]

That'll be a definite game changer for my estimate sheets.

[dalekerr]

you wrote NEW FEATURE COMMING (sic) SOON

[unnikrishnansanthosh]

Thank you Leila for sharing this, i was waiting for this for a long time

[rogue_nomad]

You could always format the numbers to have the zeros. But this feature is still very useful if you are working with account numbers which have 0's preceeding the actual numbers since it wont disappear if you save your file in .csv format. One less problem in uploading payment files in online banking systems.

[PaulRoneClarke]

Oh Microsoft. Give us a direct image lookup already. Without all the jiggery-pokery you need to do now (Which when you have 11, 000 images really slows the system down).
It was requested on your own forms over 20 years ago and has been literally hundreds of times since. It can't be THAT hard. :)

[fursansharafi]

The numbers moved to the right, Meaning excel doesn't recognize the numbers as numbers, But as texts.
Then you have to convert them back to numbers by text to column or clicking convert to number after clicking the error triangle.

[DanielFlores-osfr]

This is great info. Thanks for sharing. Do you know if this fields going to keep as number format? Or they will remain as text?

[viv]

When you are adding zero, the cell is getting left aligned. So is the format text?