Formatting Issues

How to Fix Excel Conditional Formatting Relative and Absolute References

John Wilson Oct 19, 2026 868 views

Question details

The user needs to correctly apply a conditional formatting rule across multiple columns to highlight an entire row based on a blank cell in one specific column.

How to Fix Excel Conditional Formatting Relative and Absolute References

Product: Microsoft Excel
Device & OS: not provided
Scenario: Formatting complete patient records spanning multiple columns when the patient number column is left blank.
Observed behavior: Removing dollar signs causes only some cells to respond correctly, and Excel automatically restores the absolute references (dollar signs) in the 'Applies to' range.

Before you start

Determine the exact range of cells you want to highlight and identify the specific column that contains the condition (e.g., the blank patient number).

Solution 1Recommended

Use a Mixed Reference Formula for Conditional Formatting

Use a mixed reference by locking the column with a dollar sign while keeping the row relative. This evaluates the condition in one fixed column but applies it row by row.

When highlighting an entire row based on a single column's value, you must use a mixed reference like =$C9. The dollar sign before the 'C' locks the evaluation to column C, while the lack of a dollar sign before the '9' allows Excel to move down row by row.

It is completely normal for Excel to automatically add absolute references (dollar signs) to the 'Applies to' range. You should not attempt to remove them.

Select the target range

Highlight the entire data range you want to format, such as B9:F17. Do not select the headers.

Create a new formatting rule

Go to the Home tab, click on 'Conditional Formatting', and select 'New Rule'.

Enter the mixed reference formula

Choose 'Use a formula to determine which cells to format'. Enter the formula =$C9="" (assuming your target condition is in column C and your selected range starts at row 9).

Set the desired format

Click the 'Format' button, choose your desired fill color or font style, and click 'OK' twice to apply the rule.

Use a Mixed Reference Formula for Conditional Formatting

Important Row Matching: The row number in your formula must exactly match the topmost row of your selected 'Applies to' range. If your range starts at B9, the formula must reference row 9.

Solution 2

Manage and Remove Conflicting Formatting Rules

Clear out duplicate or overriding conditional formatting rules that might be preventing your new formula from applying correctly.

Free Microsoft Office alternative

Apply Conditional Formatting Easily in WPS Spreadsheet

WPS Spreadsheet provides a highly intuitive and 100% Excel-compatible environment for managing complex conditional formatting rules. You can effortlessly use absolute, relative, and mixed references to highlight your data.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your spreadsheet document.
2. Select your data range: Highlight the entire block of cells you want to apply the formatting to, ensuring you start from the top-left cell.
3. Access Conditional Formatting: Navigate to the Home tab, click on 'Conditional Formatting', and select 'New Rule'.
4. Input the mixed reference formula: Select 'Use a formula to determine which cells to format', enter your formula (e.g., =$C9=""), and customize the cell format.
5. Apply and save: Click 'OK' to apply the formatting rule instantly across your selected patient records.

100% compatible with Microsoft Excel conditional formatting rules and formulasCompletely free to use with a lightweight installationClean, user-friendly interface for managing multiple rules without crashes

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel keep adding dollar signs back to the 'Applies to' range?

Excel mandates that the 'Applies to' range uses absolute references (dollar signs) to firmly define the boundaries of the formatted area. This ensures the range does not shift if you add or remove rows. It does not affect the internal relative logic of the formula you used.

What is the difference between relative, absolute, and mixed references in conditional formatting?

An absolute reference (e.g., $C$9) locks both the column and the row. A relative reference (e.g., C9) allows both to shift as the rule applies to different cells. A mixed reference (e.g., $C9) locks the column but allows the row to change, which is essential for evaluating a condition in one specific column for every row in a larger range.

Why is my conditional formatting applied to the wrong row?

This almost always happens because the row number in your formula does not match the first row of your 'Applies to' range. For example, if your 'Applies to' range is B5:F20, your formula must be written for row 5 (e.g., =$C5=""). If you write =$C2="", the formatting will be offset by 3 rows.

Document & File Issues