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.

1. Select the range of cells A2:A16.
2. Go to the Home tab.
3. 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.

1. Select the range of cells A2:A16
2. Press the keyboard shortcut keys Ctrl + 1 or Click the Right Mouse button on the selected cell and Choose Format Cells.
3. Go to the Number tab.
4. Select Custom in the Category options.
5. Type a new custom format 00 in the Type input. You want the total number of digits including any leading zeros to be 2.
6. 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 Enter. Drag the fill handle to the range 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 Enter. Drag the fill handle to the range 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 Enter. Drag the fill handle to the range 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 Enter. Drag the fill handle to the range 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.

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

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

3. Select the Flattened PivotTable option in the PivotTable command.
4. 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

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

3. Rename the query name PowerQuery, and select Custom Column in the Add Column Tab – to open the Custom Column dialog box.
4. Type the name Emp ID ( Size 2 ) in the New column name box
5. Type the formula =Number.ToText( [Emp ID], "00" ) in the Custom column formula input box, and Press the OK button.

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

7. 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.

1. Press Alt + F8, the keyboard shortcut to open the Macro dialog box.
2. 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

1. Go to Automate menu.
2. Select the Add Trailing Zeros Script – to open the Code editor on the right side of your Excel application.
3. 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!