Formatting Issues

How to Display Up to Two Decimal Places Without Trailing Zeros in Excel

WPS Editor Sep 27, 2026 873 views

Question details

The user wants to format numbers in Excel to display a maximum of two decimal places, omitting any unnecessary trailing zeros (e.g., displaying 0.30 as 0.3), without modifying the existing formulas or underlying values.

How to Display Up to Two Decimal Places Without Trailing Zeros in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Cleaning up a spreadsheet with numerical or financial data where exact precision is required, but rigid decimal formats cause visual clutter with redundant trailing zeros.
Observed behavior: The numbers visually adjust to show up to two decimal places and drop trailing zeros, all while the formula and exact numerical values remain perfectly intact in the background.

Before you start

Highlight the specific cells or columns you want to format before applying custom settings to ensure your visual changes apply properly without disrupting your existing formulas.

Solution 1Recommended

Use Custom Number Formatting (Recommended)

Apply the '0.##' custom format to instruct Excel to display up to two decimal places while automatically hiding any redundant trailing zeros.

This is the most efficient and safest method because it strictly alters the visual representation of the cell. The '#' symbol in Excel's custom formatting rules acts as an optional digit placeholder, meaning digits will only display if they are significant.

Select the Target Cells

Click and drag to highlight the cells, or click the column header (e.g., Column C) to select the entire column you want to format.

Open Format Cells Menu

Right-click on the highlighted area and select 'Format Cells' from the context menu, or simply press the Ctrl + 1 keyboard shortcut.

Navigate to Custom Category

In the Format Cells dialog box, make sure you are on the 'Number' tab. In the Category list on the left side, click on 'Custom'.

Apply the Format Code

In the 'Type' text box on the right, erase the existing text and enter '0.##'. Click the 'OK' button to apply the changes to your worksheet.

Use Custom Number Formatting (Recommended)

Data Integrity Preserved: This method changes only how the numbers look. Your underlying values and any existing formulas in the cells remain completely unchanged.

Solution 2

Use the LET Function (For Excel 365)

For Microsoft 365 users, integrate the LET function into your existing formulas to calculate and round values dynamically without relying on static cell formats.

Solution 3

Use a VBA User-Defined Function

Create a custom VBA macro to round values strictly based on significant figures, ideal for complex scientific data sets.

Format Data Easily

Use WPS Office to Format Decimals Effortlessly

WPS Spreadsheet provides a highly compatible, user-friendly interface to apply custom number formats, perfectly matching standard spreadsheet behaviors without altering your underlying data or complex formulas.

1. Open Your Spreadsheet: Launch WPS Office and open your existing spreadsheet document.
2. Select the Data: Highlight the columns or cells containing the numbers you wish to format.
3. Access Custom Formatting: Right-click the selection, click 'Format Cells', choose 'Custom', and type '0.##' in the input box.
4. Apply Changes: Click 'OK' to instantly remove trailing zeros while preserving up to two decimal places.

Fully compatible with Microsoft Excel file formats, including .xlsx, .xls, and .csv.Easily apply custom number formats like '0.##' to massive data sets in just a few clicks.Lightweight, fast, and free to download for Windows, Mac, and Linux users.Familiar user interface ensures a seamless transition with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show 0.30 instead of 0.3 when I use the default 'Number' format?

Excel's default 'Number' format forces a fixed number of decimal places (usually two by default). This means it pads shorter decimals with trailing zeros to maintain alignment. Using the custom format '0.##' overrides this behavior and drops the padding.

Will applying the '0.##' custom format affect my formulas?

No. Custom number formatting only changes the visual presentation of the data on your screen. The actual underlying values and formulas remain completely unchanged, ensuring accurate calculations later.

Can I apply this format to currency values?

Yes. You can incorporate the same logic into a custom currency format. For example, entering '$0.##' in the custom format type will display '$0.45' but drop the trailing zero for a value like '$0.3'.

Does this solution work if I need more than two decimal places?

Absolutely. You can easily adjust the format by adding more '#' symbols. For instance, entering '0.####' will display up to four decimal places while still hiding any redundant trailing zeros.

Document & File Issues