Fix Excel Conditional Formatting Formulas with Table References
Question details
The user needs to use a formula with table data in an Excel conditional formatting rule.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A conditional formatting formula uses structured references to a table column and matching criteria from another table.
- Observed behavior
- The formula works in a worksheet cell but is reported as invalid when entered in a conditional formatting rule.
Identify the worksheet ranges covered by each table column in the formula and the first cell in the range you want to format. Check whether any referenced data is on a different worksheet.
Replace structured references with A1-style ranges
Use standard cell ranges when Excel rejects structured table references in a conditional formatting formula.
Conditional formatting may reject structured references such as TBLTPVSheet[BillValue] even when the same formula works in a worksheet cell. Replace each structured reference with the corresponding range while preserving the formula's logic.
Select the BillValue data cells in TBLTPVSheet and note their A1 addresses. For example, if they occupy A2 through A4633, use $A$2:$A$4633 in place of TBLTPVSheet[BillValue]. Identify the ranges for the matching criteria as well.
Select the cells that should receive the formatting. Note the top-left cell of the selection so any relative references in the formula are written for that cell.
Go to Home > Conditional Formatting > Manage Rules, select the rule, and choose Edit Rule. Replace structured references with their A1-style equivalents, such as $A$2:$A$4633, and retain the original matching conditions.
Use dollar signs to lock lookup ranges and adjust relative references for the top-left cell of the Applies to range. Select OK, check the Applies to range in Rules Manager, and test whether the expected cells are formatted.

Use named ranges for data on another worksheet
Use workbook-level names when a conditional formatting formula needs ranges from another sheet.
Work with spreadsheet formatting in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar interface for working with spreadsheets. Its Excel format compatibility makes it easier to move existing workbooks to WPS; use Excel to resolve an Excel-specific rule error.

Frequently Asked Questions
Why does my formula work in a cell but not in conditional formatting?
Excel's conditional formatting formula parser may reject structured table references that work in worksheet cells. In Home > Conditional Formatting > Manage Rules, replace references such as TBLTPVSheet[BillValue] with the corresponding A1-style range.
How should I use dollar signs in the rule?
Lock lookup ranges with references such as $A$2:$A$4633. Write relative references for the top-left cell in the Applies to range so Excel adjusts them for the other formatted cells.
Can the rule use data from another worksheet?
You can define a workbook-level name for the other sheet's range through Formulas > Name Manager, then use that name in the conditional formatting formula.
Will a fixed range include new table rows?
Not if the new rows fall outside the range. Open Home > Conditional Formatting > Manage Rules and extend the formula's ranges when needed.




