How to add leading zeros to numbers in Excel

To display numeric data in Excel documents with different purposes, sometimes you need to have a leading zero. Come to our tutorial, TipsMake.com will guide you how to add the number 9 to the beginning of the sequence.

How to add leading zeros to numbers in Excel Picture 1

Parameter cells in Excel are classified into different data cells, for example, the parameter cell contains a range of numbers, when you finish filling that parameter cell, they automatically switch to the numeric data cell type. .

When you enter a leading sequence of zeros, Excel automatically cleans up with the most accurate number for that range, which means eliminating the zero.

How to add leading zeros to numbers in Excel Picture 2

So adding zeros to the beginning of the sequence can be done in many ways, but you need to note that you want to keep the numeric data cell for that parameter cell or not, otherwise we can turn it. Becoming a text cell, Excel will not automatically shorten your number range.

1. Add zeros at the beginning of the sequence with the resulting parameter data text

To convert a parameter cell to a text data type, you can do the following:

Method 1: Convert the parameter cell type before filling the number range

To convert the parameter cell type into a text data box, click on the data cell or you can select an entire data array.

Next, open the Home ribbon on the toolbar, and then click Number Format in the Number section .

Then you select Text in the list just displayed.

How to add leading zeros to numbers in Excel Picture 3

Then you enter the sequence of zeros at the input in the converted data cell, you will see that your zero has not been omitted as before but there is a blue triangle in Excel. at the corner.

How to add leading zeros to numbers in Excel Picture 4

This is an Excel reminder that the data you enter is a continuous number, and it asks if you entered the wrong cell and want to return the current cell to the numeric data cell.

Of course we intentionally so, you need to click on the triangle and select Ignore Error to ignore the error that Excel mentioned, the triangle will disappear.

How to add leading zeros to numbers in Excel Picture 5

Method 2: Format the parameter cell immediately while entering a number range

It is not necessary to select a parameter cell and then convert before you enter the number range, you can choose how to insert apostrophes in front of the required number range.

That means you have defaulted this parameter cell to be a text field.

How to add leading zeros to numbers in Excel Picture 6

After you press Enter you will also see Excel error message, you follow the same way above, ignoring this error message Ignore Error .

How to add leading zeros to numbers in Excel Picture 7

For both of the above, you can highlight all the data boxes with error messages and then choose to remove this message at once by selecting Ignore Error .

How to add leading zeros to numbers in Excel Picture 8

2. Add zeros at the beginning of the sequence but keep the numeric data cell intact

To add zeros to the beginning of the sequence but keep the numeric cell intact, do the following.

First, right-click on the parameter cell you need to enter the number sequence at the beginning.

Next, select Format Cells from the displayed list of options.

How to add leading zeros to numbers in Excel Picture 9

You open the Number tab in the Format Cells window box that appears.

Continue to change to Special in Category .

You choose Zip Code in the Type section .

How to add leading zeros to numbers in Excel Picture 10

Then you enter the sequence of numbers with 0 at the beginning as usual.

How to add leading zeros to numbers in Excel Picture 11

As you can see, the two numbers in Zip Code form can still perform calculations as usual.

How to add leading zeros to numbers in Excel Picture 12

Thank you for following our article TipsMake.com on how to add leading zeros to numbers in Excel. Wish you can successfully perform the operation of this trick.

5 ★ | 2 Vote

May be interested

  • How to extract numbers or text from ExcelHow to extract numbers or text from Excel
    microsoft excel is great for working with both numbers and text, but if you use both in the same cell, you may have some difficulties. fortunately, you can extract numbers or text from cells to work with your data more efficiently.
  • How to format numbers in ExcelHow to format numbers in Excel
    how to format numbers in excel in the process of working, processing data in excel sometimes you need to format numbers in excel so that data can display the number format in accordance with the requirements to be processed. you do not know how to format numbers in ex
  • 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 convert negative numbers to positive numbers ExcelHow to convert negative numbers to positive numbers Excel
    to convert negative numbers to positive numbers and vice versa on excel, you only need to perform a very simple operation.
  • How to convert Apple Numbers documents to Microsoft ExcelHow to convert Apple Numbers documents to Microsoft Excel
    numbers is spreadsheet software specifically for apple devices both on phones and on computers. basically, apple numbers works similar to microsoft excel, so you can completely convert documents between these two toolkits to serve each specific working situation.
  • Instructions for converting numbers to words in ExcelInstructions for converting numbers to words in Excel
    to convert numbers to words in excel, you will use a support tool, instead of typing by hand, which is not accurate and takes a lot of time to do. below are some ways to convert numbers to words in excel.
  • How to convert numbers into words in Excel?How to convert numbers into words in Excel?
    in excel, entering and typing amounts is very tiring, sometimes confusing. so why do this work faster but still have to ensure accuracy? tipsmake.com will introduce you to a support tool, or more accurately, an additional add-in for microsoft excel, very convenient for accounting, finance, people who have to work with big amounts ...
  • How to round whole numbers in ExcelHow to round whole numbers in Excel
    how to round whole numbers in excel in excel there are many mathematical functions used to round numbers. today software tips will guide you some functions that can be used to round whole numbers in excel.
  • Do not round numbers in Excel, turn off rounding in ExcelDo not round numbers in Excel, turn off rounding in Excel
    rounding numbers in excel is a handy function for calculating data, but sometimes it also makes you feel unnecessary troubles. to turn off the function of rounding in excel you can follow the following instructions
  • Convert numbers to text in ExcelConvert numbers to text in Excel
    convert numbers into words in excel - regularly process and calculate data with excel spreadsheets, if the data is related to currency, the number of words you want to convert will be many, vntools software to help you change numbers into words quickly and efficiently