Formatting Issues

How to Fix Excel Table Not Copying Percentage Formatting to New Rows

Olivia Miller Oct 19, 2026 868 views

Question details

The user needs a reliable way to ensure that a calculated percentage column in a complex Excel table automatically retains its percentage formatting when new rows are added, preventing it from reverting to a Custom format.

How to Fix Excel Table Not Copying Percentage Formatting to New Rows

Product: Microsoft Excel
Device & OS: not provided
Scenario: Expanding a complex Excel table that features irregular data entry and columns utilizing formulas for percentage calculations.
Observed behavior: While most columns correctly inherit their formatting from the previous row when a new row is added, a calculated percentage column unexpectedly displays as Custom instead of Percentage.

Before you start

Before modifying your formulas, verify that your data range is actually formatted as an official Excel Table (using Ctrl+T), as this feature is required for formulas to automatically copy down to new rows.

Solution 1Recommended

Use the TEXT Function in Your Formula

This is the most reliable method to force a calculated column to display as a percentage, regardless of the cell's underlying automatic format setting.

By wrapping your existing calculation in the TEXT function, you dictate exactly how the result should appear. This prevents Excel's automatic formatting inheritance from guessing incorrectly or losing the format during table expansion.

Select the target column

Click on the first cell containing the formula in the affected percentage column of your Excel table.

Edit the formula

Modify your existing calculation by wrapping it inside the TEXT function. For example, change your formula from =(AA47-AB47)/AB47 to =TEXT((AA47-AB47)/AB47, "0%").

Apply the update

Press Enter to save the updated formula. The Excel table will automatically propagate this new formula down the entire column.

Test table expansion

Add data to a new row at the bottom of your table. The calculated result will now consistently display as a percentage without requiring any manual format adjustments.

Data Type Change: Using the TEXT function converts the numeric result into a text string. If you need to perform further calculations on these percentage values elsewhere in your workbook, you may need to reference the original unformatted calculation instead.

Solution 2

Manually Re-apply Formatting to the Entire Column

A quick fix to re-establish the correct format for the entire column if the inheritance breaks due to empty rows or irregular data entry.

Manage Complex Tables and Formulas Seamlessly with WPS Spreadsheet

WPS Spreadsheet offers complete compatibility with Excel formulas, including the TEXT function, allowing you to manage data tables and formatting inheritance smoothly without encountering frustrating formatting drops.

1. Open your workbook: Launch WPS Office and open your .xlsx file containing the problematic data table.
2. Locate the formula cell: Find the percentage column that is losing its format and select the topmost formula cell.
3. Apply the TEXT function: Update the calculation in the formula bar to use the TEXT format, for instance: =TEXT(A2/B2, "0%").
4. Verify automatic formatting: Add a new row at the bottom of your table to confirm that WPS Spreadsheet flawlessly applies the percentage format to the new entry.

Fully compatible with Microsoft Excel file formats (.xlsx) and functions.Advanced table features that accurately inherit formatting and auto-fill formulas for expanding data.Free, lightweight, and features an intuitive interface that makes transitioning easy.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel table stop copying formatting to new rows?

Excel attempts to automatically inherit formatting and formulas from adjacent cells in a table. However, if there are large gaps, empty rows, or highly irregular data entry patterns within the table, this inheritance pattern can break, causing new cells to default to General or Custom formats.

Does the TEXT function change the underlying data type in Excel?

Yes. The TEXT function explicitly converts a numeric value into a text string that is formatted to look like a specific number (such as a percentage). While it ensures the display is visually correct, you cannot use this text output directly in other standard mathematical functions without converting it back.

How do I force an Excel table column to keep its format without altering my formulas?

You can try explicitly setting the column's number format by selecting the entire data body range of the column (clicking the top edge of the column header to get the black down arrow) and choosing 'Percentage' from the Home tab. If the table is complex, this may still fail occasionally, which is why wrapping formulas in the TEXT function is the most resilient workaround.

Document & File Issues