How to write leading zeros in Excel

In Excel, it is not possible to write zeros before a sequence of numbers. But in practice with some work, writing a zero at the beginning of the sequence is required. The following article will guide how to write leading zeros.

Method 1: Enter apostrophes before the number sequence to enter.

Excel automatically removes the quotes, but there is a blue triangle warning above.

How to write leading zeros in Excel Picture 1

Method 2: Choose a column format of type Text.

Step 1: Select the column to create numbers 0 -> Right-click -> Select Format Cells .

How to write leading zeros in Excel Picture 2

Step 2: The Format Cells dialog box appears. At Category -> select Text -> OK .

How to write leading zeros in Excel Picture 3

The result when you enter the leading zero does not disappear.

How to write leading zeros in Excel Picture 4

Method 3: Use Custom.

For example: The rule has 10 digits if the value is only 5 digits then the first 5 digits enter is 0. If there are 9 digits, the first digit enters 0 -> Please follow this way:

Step 1: Select the column to write 0 -> Right-click -> Format Cells .

How to write leading zeros in Excel Picture 5

Step 2: A dialog box appears, select Custom -> Item Type enter the number of digits 0 you want to display (assuming choose 12) -> OK .

How to write leading zeros in Excel Picture 6

Result:

How to write leading zeros in Excel Picture 7

If you enter 3 digits, Excel automatically inserts 12-3 = 9 leading zeros in front of it. Enter the number with 11 digits => with 1 leading zeros. If you enter 12 digits, no leading zeros are displayed.

Good luck!

4 ★ | 1 Vote

May be interested

  • Some experience in Excel spreadsheetsSome experience in Excel spreadsheets
    in excel there are many good tips that only those who have ever discovered, please introduce to you some of the experience in excel.
  • How to write exponential numbers in ExcelHow to write exponential numbers in Excel
    how to write exponential numbers in excel are you entering data in excel that includes exponents that you do not know how to write exponents in excel? so you are looking for ways to write exponential numbers in excel to continue the process
  • How to write arrow notes in ExcelHow to write arrow notes in Excel
    many readers have asked tipsmake how to write arrow notes in excel. below, we will guide you on how to do this in a simple and effective way.
  • How to display 0 in front of a number in ExcelHow to display 0 in front of a number in Excel
    usually when we enter a sequence of numbers in excel that has a leading zero, it will disappear. so how to display 0 in the series in excel?
  • Write italic, vertical, and diagonal lines in ExcelWrite italic, vertical, and diagonal lines in Excel
    write italic, vertical, and diagonal lines in excel - sometimes the data sheet you enter must write italics for titles to facilitate tracking.
  • How to Add Rows in Excel Using FormulasHow to Add Rows in Excel Using Formulas
    if you're looking for a quick way to add blank rows to an excel spreadsheet, you'll be surprised to learn that there's no excel function or formula that can do this. however, you can write a macro (a command that executes a certain job script) to instantly add a certain number of rows to the specified location. today's tipsmake will show you how to write a simple macro to add new rows to an excel spreadsheet. besides, the article also shows how to add existing formulas to new rows in the spreadsheet.
  • How to write fractional values ​​in ExcelHow to write fractional values ​​in Excel
    sometimes you enter fractions into excel data cells but it shows the time format. you do not know how to handle the input data is displayed as a fraction.
  • How to Write a Simple Macro in Microsoft ExcelHow to Write a Simple Macro in Microsoft Excel
    how to write a simple macro in microsoft excel in this article, tipsmake will show you how to create a simple macro for an excel sheet.
  • How to use notes in Microsoft ExcelHow to use notes in Microsoft Excel
    comments in microsoft excel are simple text messages associated with individual cells. they are often used to add context to the cell's contents, explaining a few small details about the cell.
  • Other diacritics in Excel, different diacritics in ExcelOther diacritics in Excel, different diacritics in Excel
    other diacritics in excel, different diacritics in excel. other accents in excel are used quite a lot in terms of function formulas. if you need to write a function with different conditions and you do not know how to write other accents in excel