Formatting Issues

How to Use Relative Goal References in Excel Conditional Formatting

Rana Garcia Oct 12, 2026 869 views

Question details

The user needs to apply conditional formatting to compare actual values against goal values, ensuring that the goal reference adjusts dynamically when the data group is copied.

How to Set Up Conditional Formatting with Relative Goal References in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Copying and pasting grouped data blocks that rely on conditional formatting tied to a specific goal cell.
Observed behavior: When using standard formatting rules, absolute references cause the copied groups to remain locked to the original goal cell. The user wants the reference to shift proportionally to the new group.

Before you start

Ensure your data is organized in consistent groups or blocks so that the relative row or column distance between your actual values and goal values remains exactly the same when copied.

Solution 1Recommended

Apply Conditional Formatting with Mixed References

The most straightforward method to ensure your goal references adjust when copied is to use mixed references (e.g., $B2 or B$2) instead of fully absolute references ($B$2) in your formatting formula.

By default, Excel often applies absolute references when selecting cells for conditional formatting. Removing the dollar sign ($) from the row or column allows the reference to shift relative to where the formula is copied.

Select the target data

Highlight the range of actual values in your first data group that you want to format.

Create a new formatting rule

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

Enter a formula with mixed references

Choose 'Use a formula to determine which cells to format'. Enter a formula comparing your first actual value to the goal, removing the absolute lock where needed. For example, use =A2<$B2 to highlight values below the goal, allowing the row to change when copied down.

Set the format and apply

Click the 'Format' button, choose your desired fill color (e.g., red for below goal), click OK, and apply the rule. You can now copy the data group, and the goal reference will shift accordingly.

Apply Conditional Formatting with Mixed References

Understanding Reference Types: Using a $ locks either the row or column. Adjust the placement of the $ sign based on whether you are copying your data groups horizontally (lock the row, free the column) or vertically (lock the column, free the row).

Solution 2

Use Dynamic Functions (OFFSET or XLOOKUP) for Complex Grouping

If your data groups are structured in a way where standard mixed references will not shift correctly, you can use dynamic functions to look up the goal value for each block.

Advanced Formatting Made Easy

Easily Manage Conditional Formatting Rules in WPS Spreadsheet

WPS Spreadsheet provides a highly intuitive interface for setting up and managing complex conditional formatting rules. It perfectly supports all Excel formulas, including mixed references, XLOOKUP, and OFFSET, ensuring your data groups can be copied seamlessly without breaking goal references.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your grouped data.
2. Select the data range: Highlight the specific actual values you wish to format against your goal.
3. Access conditional formatting: Navigate to the Home tab on the top ribbon, click on Conditional Formatting, and select New Rule.
4. Enter the relative formula: Choose 'Use a formula to determine which cells to format' and input your formula using mixed references (e.g., =A2>B$2).
5. Apply and copy safely: Set your desired highlight color, click OK, and safely copy your data group to a new location. The reference will update automatically.

Free and lightweight alternative to Microsoft ExcelFully compatible with Microsoft Excel conditional formatting and formulas (.xlsx)User-friendly visual interface for managing and testing complex formatting rulesSupports modern functions like XLOOKUP for dynamic data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why does my conditional formatting refer to the old cell when I copy it?

This happens because the formatting formula uses absolute references, indicated by dollar signs (e.g., $B$2). The dollar signs lock the reference to that specific cell. To allow the reference to shift when copied, you must remove the appropriate dollar signs to make it a relative or mixed reference.

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

An absolute reference (like $A$1) never changes when copied to a new location. A relative reference (like A1) changes based on the new relative position of rows and columns. A mixed reference (like $A1 or A$1) locks only the column or only the row, giving you precise control over how the formula adjusts.

Can I use XLOOKUP inside conditional formatting rules?

Yes. In Microsoft 365, Excel 2021, and modern versions of WPS Spreadsheet, you can use XLOOKUP within conditional formatting formulas to dynamically fetch target values based on matching criteria in your data groups.

Document & File Issues