Keep Excel Conditional Formatting Dynamic in Tables and Named Ranges
Question details
Keep conditional formatting applied as an Excel table or named range expands.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A conditional formatting rule needs to cover new rows added to a table or dynamic named range.
- Observed behavior
- Excel converts a named range in the Applies To field to an absolute cell reference, which may not expand and can become fragmented.
Identify the first data row and the column or range that needs formatting. Open Home > Conditional Formatting > Manage Rules and check the existing formula and Applies To range.
Apply the conditional formatting rule to an Excel table column
Use a table when new records are regularly added as rows; table formatting normally extends to new rows.
Select the data and choose Insert > Table. Confirm the range and whether the table has headers, then select OK.
Select the table column's data cells, excluding the header, and choose Home > Conditional Formatting > New Rule.
Choose a rule type and set the condition and format. For a formula rule, use references relative to the first selected data row; for example, use =$B2>0 if the first data row is 2 and column B contains the values to test.
Enter a record in the row immediately below the table and check that the table expands and the new row receives the expected formatting.
Choose Home > Conditional Formatting > Manage Rules and verify that Applies To covers the intended table data and that the formula references are correct.

Maintain the rule for a dynamic named range
Use this approach when the data cannot be converted to a table, but expect to check the rule as the range changes.
Work with Formatted Spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open and edit compatible Excel files as you move your spreadsheet work to WPS Office; it does not fix Excel's named-range behavior.

Frequently Asked Questions
Why does Excel replace my named range in Applies To?
Excel can resolve the name to explicit cell addresses when the conditional formatting rule is created or edited. Check Home > Conditional Formatting > Manage Rules to see the current Applies To range.
Will conditional formatting automatically extend to new table rows?
Table-based formatting normally extends when the table expands. Add a row immediately below the table, then check the result and the rule's Applies To range in Conditional Formatting > Manage Rules.
Can a conditional formatting rule automatically follow a spilled range?
Excel does not provide a straightforward, reliable way to keep Applies To synchronized with a spilled range. Review the rule's coverage and formula whenever the spill size changes.
How do I fix fragmented conditional formatting ranges?
Open Home > Conditional Formatting > Manage Rules and inspect the Applies To fields for overlapping or duplicate rules. Remove unwanted duplicates and adjust the remaining ranges, then verify their formulas.




