December 4, 2020

How to keep leading zeros in Excel

For example we have a set of numbers for which we need to keep the leading zeros as it is. Excel automatically truncates the leading zeros from numbers but that only happens if the cell format is General or Number.

For the cells with text format the leading zeros will be kept as excel will consider that as text character. So if we need to keep the leading zeros, the first option can be to change the format of the cells as text as follows.

But you cannot do any number operations like sum,average or any kind of calculations for this set of numbers as now it became texts.

If you want to keep the number format with leading zeros we have one more option left as follows.

  • Select the cells
  • Open Format cell from the Home tab Number group in the tool bar
  • Now choose Custom format
  • In Type box enter how many leading zeros you need
  • Click OK

Now the all kind of number operations you can perform on this data set with leading zeros.

Leave a Reply

Your email address will not be published. Required fields are marked *