# How To Add Trailing Zeros in Excel [11 Methods]

Do you need to add leading zeros in Excel like **000203**? Default Excel will not allow you to add trailing zeros and it will remove them. You need to use some techniques or functions to make Excel keep the trailing zeros in your data.

Sometimes you need to keep the leading zeros for your reports and analysis. Eg. You may need to add the leading zeros to the Employee IDs field to make it consistent to your report.

Before learning the techniques, it will be better to understand why you need to add trailing zeros, and the issue in Excel.

You may notice the sorting challenge when sorting your data, which is stored as text in Excel.

**Data 1** in **Column A** stored the numbers as text without trailing zero and **Data 2** in **Column B** stored the numbers as text with trailing zero. The sorted outputs are in column C and Column D. The second value in **Column C** is 10, which is not perfect for further analysis. The data in **Column D** sorted is perfectly sorted and you can use it for further analysis.

In this article, you will learn 9 different methods that can help you to add trailing zeros in Excel.

This blog post explores how to add trailing zeros using the Excel **Format, Format Cells**, adding **Apostrophe**, andExcel functions **TEXT**, **RIGHT**,** BASE**, and **REPT.**

The **Power Query** and **Power Pivot** methods work well for multiple data files and large data sets. Finally, you will learn the **VBA** and **Office Scripts **code methods to add trailing zeros in Excel for your dashboard or report automation projects.

Suppose you have 20 employees, you need to store the **Employee ID** 1, 2, 3, …9 as 01, 02, 03, …09 in two digits.

Get your copy of the example workbook used in this blog post to follow along!

## Add Trailing Zeros in Excel with Format

Excel gives you the format option to change the cell format to text. Once you change the cell format, you can type the number with leading zeros in the cell. Excel will not remove the leading zeros and keep them as you entered the text in the Excel cell.

Steps to change the cell format to **Text**.

- Select the range of cells
**A2:A16**. - Go to the
**Home**tab. - Select
**Text**from the format options in the**Numbers**section.

Now Excel allows you to enter numbers with leading zeros for those cells. These are entered as text values, not a number.

## Add Trailing Zeros in Excel with Custom Format

You can use the Excel **Custom Format** option to format the numbers with trailing zeros. It shows the numbers with trailing zeros. You can notice that the actual cell value is not changed.

Steps to apply a custom format to display trailing zeros in Excel.

- Select the range of cells
**A2:A16** - Press the keyboard shortcut keys
`Ctrl`+`1`or Click the**Right Mouse button**on the selected cell and Choose**Format Cells**. - Go to the
**Number**tab. - Select
**Custom**in the Category options. - Type a new custom format
**00**in the**Type**input. You want the total number of digits including any leading zeros to be 2. - Press the
**OK**button.

## Add Trailing Zeros in Excel with Apostrophe

Start typing with an **Apostrophe **is a quick and easy method to add trailing zeros in Excel. If you type an integer number in a cell. Excel application considers that as a number format and will not allow you to start with zero. To avoid that you can start with an apostrophe when the numbers need to treat it as text format in Excel. It will be invisible and can only see in the Formula bar when selecting the cell.

Go cell **A2**, type the number with the leading zero, start with an apostrophe **’01**, and Press `Enter`.

You can notice that the leading zeros are visible in the worksheet formula bar and will not be in the selected cell **A2**.

## Add Trailing Zeros in Excel with **TEXT** Function

Converting numbers to text, formatting text, extracting specific words from a text string, and much more can be done with the **TEXT** function. In this section, you learn how to use the **TEXT** function to add trailing zeros in Excel.

`TEXT( value, format_text )`

**TEXT** function format the given value and return a text.

Go to cell **B2**, type the formula ** =TEXT( A2, "00" ) )**, and Press

**. Drag the fill handle to the range**

`Enter`**B2:B16**to copy the formula.

**TEXT** function in the above formula formats the given number in cell **A2** as text to the size of two characters.

## Add Trailing Zeros in Excel with **RIGHT** Function

You can use the Excel **RIGHT** function to add leading zeros in Excel. In this method, you will add a few zeros to the value and then extract the last few characters using the **RIGHT** function.

`RIGHT ( Text, [Number])`

The **RIGHT** function extracts the specified number of characters from the last character.

Go to cell **B2**, type the formula ** =RIGHT( “0” & A2, 2 ) )**, and Press

**. Drag the fill handle to the range**

`Enter`**B2:B16**to copy the formula.

First, you concatenate a zero to the number in cell **A2** in the formula, and it returns the last two characters, for the single digit number 1 t0 9, it returns with the trailing zero.

## Add Trailing Zeros in Excel with **BASE** Function

In this section, you explore how to use the **BASE** function to add trailing zeros in Excel. It allows you to convert a number into a text format.

`BASE ( Number, Base, [MinLength])`

**BASE** function converts the given number into text.

Go to cell **B2**, type the formula ** =BASE( A2, 10, 2 ) )**, and Press

**. Drag the fill handle to the range**

`Enter`**B2:B16**to copy the formula.

The above formula converts the number in cell **A2 **into base **10**, and formats it to a text value with **2** characters. The function adds a leading zero to the number 1 to 9. since these numbers are less than 2 characters.

## Add Trailing Zeros in Excel with **REPT** Function

**REPT** function is used to repeat a text string a given number of times and create a custom format. This function can also be used to extend the length of a text string.

`REPT( text, number_times )`

**REPT** function repeats the text a given number of times.

`LEN( text )`

**LEN** returns the number of characters in the text.

Go to cell **B2**, type the formula ** =REPT( 0, 2 - LEN( A2 ) ) & A2 )**, and Press

**. Drag the fill handle to the range**

`Enter`**B2:B16**to copy the formula.

In the above formula, the **LEN** function returns **1**, the number of characters in cell **A2**, and subtracts from the integer value **2**. **REPT** function repeats the character zero **0 **one time and concatenates with the number in cell **A2**

## Add Trailing Zeros in Excel with **Power Pivot**

Excel **Power Pivot** is a data analysis and modeling tool, it enables you to create complex models, add pivot tables, charts, and reports. Power Pivot allows you to create relationships between measures, calculated columns, and datasets.

- Select the range of cells
**A1:A6**and press the command**Add to Data Model**in the**Power Pivot**Tab.

- Rename the table name
**PPData,**and column Header**Emp ID (Size 2)**in the Power Pivot editor, select the second column, type the formula, and press`=FORMAT( PPData[Emp ID], "00" )`

`Enter`.

- Select the
**Flattened PivotTable**option in the PivotTable command. - You will have options to select the New Worksheet or Existing worksheet and select the worksheet location. Once you select the worksheet and press the
**OK**button, Excel adds an empty pivot table in the worksheet.

You can drag the **Emp ID **and **Emp ID (Size 2)** fields in the **Rows** Area and see the numbers in column **A **are converted as text in column **B**.

## Add Trailing Zeros in Excel with** Power Query**

Excel **Power Query** is a useful data handling and processing tool. It allows connection to various data sources. In addition, it allows you to clean, manipulate, process and format the connected data for your reports and analysis. This can save time and prepare your data for analysis.

Steps to convert text to numbers using **Power Query**

- Select the range of cells
**A1:A16**in the**Data**Sheet - Choose
**From Table/Range**option in**Data**Tab – to open the Power Query window.

- Rename the query name
**PowerQuery**, and select**Custom Column**in the**Add Column**Tab – to open the Custom Column dialog box. - Type the name
**Emp ID ( Size 2 )**in the**New column name**box - Type the formula
`=`

**Number.ToText( [Emp ID], "00" )****Custom column formula**input box, and Press the**OK**button.

- Select the
**Close & Load**command and Press**Close & Load To… –**to popup**Import Data**dialog box.

- In the
**Import Data**dialog box, select the**Table**option, and provide the location in the**Existing worksheet**to insert the transformed Power Query table into your worksheet.

**Add Trailing Zeros in Excel with VBA**

**VBA** is a programming language that allows users to automate reports in Microsoft Excel, create custom formulas, and record repetitive tasks. You can add leading zeros in Excel using the following VBA script.

```
Sub AddTrailingZeros()
' Declare the variable dataRange
Dim dataRange As Range
' Assign the range of cells to the respective variable
Set dataRange = ActiveSheet.Range("A2:A16")
' Loop through the range of cells in the variable dataRange
For Each rg In dataRange
' Format and reset the cell value
rg.Value = Format(rg.Value, "00")
Next
End Sub
```

Insert the above code in your VBE module.

The first step, macro declares the variable and assigns the range of cells **A2:A16** to the variable **dataRange**. In each iteration, the script converts the current cell value to text with two digits using the **Format** function, and reset the current cell value.

Steps to Run the **AddTrailingZeros **macro.

- Press
`Alt`+`F8`, the keyboard shortcut to open the Macro dialog box. - Choose the
**AddTrailingZeros**Macro and then Press**Run**.

## Add Trailing Zeros in Excel with **Office Script**

**Office Scripts** is a scripting language that allows you to create automated tasks and macros in both offline and web versions of Excel. You can record actions or write the script, add a button event to the script and edit using Office Scripts. Office scripts also make it easier to collaborate with colleagues and keep spreadsheets up to date.

```
function main(workbook: ExcelScript.Workbook) {
// Assign the range of cells to the variable
let ws = workbook.getWorksheet("Office Scripts");
let rng = ws.getRange("A2:A16");
let rows = rng.getRowCount();
// loop through the range of cells
for (let i = 0; i < rows; i++) {
// get current cell value and store it in the variable cellValue
let cellValue = rng.getCell(i, 0).getValue() as string
// Format the cellValue and reset to the current cell
rng.getCell(i, 0).setValue(cellValue.toString().padStart(2,"00"));
}
}
```

**Office Scripts** assigns the range of cells **A2:A16** to the variable **rng**. The next step script loop through each cell in the range, assign the current cell value to the variable **cellValue** as a string, and Format the cell value with the help of the **padStart** function. Finally, the script resets the current cell value.

Follow the below steps to execute the **Add** **Trailing Zeros **Office script in Excel

- Go to
**Automate**menu. - Select the
**Add****Trailing Zeros**Script – to open the Code editor on the right side of your Excel application. - Press
**Run**

## Conclusions

You have to add leading zeros in your data for consistency, to sort the data, or search. The formatted data will help the user to easily read and compare. You learned 9 different methods to add trailing zeros in Excel.

Adding an **Apostrophe** in front of a number is an easy method to convert numbers to text. You just select the range of cells and use Excel **Format cells**, and the **Custom Format** method to add leading zeros.

You can use Excel functions **TEXT**, **RIGHT**,** BASE**, and **REPT **methods, which refer to the data and convert it. So, it always dynamically updates when the data are changing in the actual columns.

If you work with multiple data source files or large data files, use the** Power Query** or **Power Pivot**. **VBA** or **Office scripts** code is very useful to add trailing zeros for your report automation or dashboards projects.

If you know any other method to **Add Trailing Zeros** in Excel, let us know in the comment section!