Formatting Issues

How to Display Trailing Zeros in Excel Text and Number Cells

Chanuka Geekiyanage Oct 15, 2026 870 views

Question details

The user needs a way to retain and display trailing zeros for decimal values in Excel, both in standard numeric cells and when combined with text strings.

How to Keep and Display Trailing Zeros in Excel Cells

Product: Excel
Device & OS: not provided
Scenario: The user is entering exact decimal measurements or financial figures (like 93.400) and combining these values with descriptive text.
Observed behavior: By default, Excel removes trailing zeros after a decimal point, automatically converting entered values like 93.400 into 93.4.

Before you start

Determine whether you need the trailing zeros for standard visual display in a cell, or if you need to embed the exact formatted number within a text sentence using a formula.

Solution 1Recommended

Use Number Formatting to Display Trailing Zeros

This method is best when you want to visually display a fixed number of decimal places in your spreadsheet without altering the underlying numeric value.

Applying a number format is the most straightforward way to force Excel to show a specific decimal precision across a range of cells.

Select the target cells

Click and drag to highlight the cells containing the numbers you want to format.

Use the Ribbon tool

Go to the Home tab on the top ribbon. In the Number group, click the 'Increase Decimal' button repeatedly until you see the desired number of zeros.

Use the Format Cells dialog

Alternatively, right-click the selected cells and choose 'Format Cells'. Under the Number tab, select 'Number' and set the exact amount of decimal places you need (e.g., 3 decimal places for .000).

Use Number Formatting to Display Trailing Zeros

Custom Format Tip: You can also choose 'Custom' in the Format Cells menu and type '0.000' in the Type field to ensure exactly three decimal places are always displayed.

Solution 2

Use the TEXT Function for Concatenated Strings

Use this method when you need to combine numbers with text while ensuring the trailing zeros are not dropped in the final sentence.

WPS Spreadsheet

Easily Format Numbers and Retain Zeros in WPS Spreadsheet

WPS Spreadsheet provides intuitive formatting tools and full formula support to help you manage precise numeric data without losing trailing zeros. It is a powerful, free alternative to Microsoft Excel.

1. Open your spreadsheet: Launch WPS Office and open your workbook in WPS Spreadsheet.
2. Select the data: Highlight the numeric cells that need trailing zeros displayed.
3. Format the cells: Right-click and select 'Format Cells', then choose 'Number' to specify the exact decimal places.
4. Use functions if needed: Type =TEXT(cell,"0.000") to format numbers within text strings, identically to how it works in Excel.

Quickly adjust decimal places with one click from the Home tab.Fully supports the TEXT, CONCAT, and custom formatting formulas found in Excel.Seamlessly open, edit, and save .xlsx and .xls files without losing exact formatting.Lightweight, fast, and completely free to use.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel automatically remove trailing zeros?

Excel treats numeric entries as purely mathematical values. Since trailing zeros after a decimal point do not change the actual mathematical value (e.g., 93.400 equals 93.4), Excel automatically truncates them by default to standardize the data and save memory space.

How can I type a number with trailing zeros without changing the cell format?

You can force Excel to treat your entry as plain text right as you type it by adding an apostrophe (') before the number. For example, entering '93.400 will cause the cell to display exactly as typed, retaining all zeros without needing formatting menus.

Will changing the number format alter the actual value in the cell?

No, adjusting the decimal places through 'Format Cells' or the 'Increase Decimal' button only changes how the number looks visually on your screen. The underlying exact value remains unchanged for any backend calculations or formulas.

Excel & Data Problems