Formatting Issues

How to Apply Conditional Formatting for Goal and Result Rows in Excel

Ayan Masood Oct 12, 2026 869 views

Question details

The user needs to set up conditional formatting rules to automatically color result cells green if they meet or exceed a specific goal, and red if they fall below the goal.

How to Highlight Goal and Result Rows with Conditional Formatting in Excel

Product: Excel
Device & OS: not provided
Scenario: Comparing actual results against target goals in a spreadsheet and visually representing performance.
Observed behavior: Result cells are dynamically highlighted in green when greater than the goal cell, and in red when less than the goal cell.

Before you start

Ensure your data is organized logically, with goal and result values in adjacent cells or consistently repeating rows, before applying any formula-based formatting rules.

Solution 1Recommended

Use Simple Formula Rules for Goal and Result Cells

The most straightforward way to format a result based on a goal is by creating standard greater-than and less-than conditional formatting formulas.

This method works best when your goals and results are side-by-side or stacked in a single pair of cells. You will apply the rule directly to the result cell and use a formula to compare it to the goal cell.

Select the result cell

Highlight the specific cell or range of cells containing your results (for example, B2).

Open Conditional Formatting

Navigate to the Home tab, click on 'Conditional Formatting' in the Styles group, and select 'New Rule'.

Choose formula option

Click on 'Use a formula to determine which cells to format' from the rule type list.

Create the green rule

In the formula box, type =B2>B1 (assuming B2 is the result and B1 is the goal). Click 'Format', choose a green fill color, and click OK.

Create the red rule

Repeat the process by adding another New Rule. Type =B2<B1, select a red fill color under Format, and apply the rule.

Use Simple Formula Rules for Goal and Result Cells

Relative References: Make sure your cell references (like B1 and B2) do not have dollar signs ($) if you plan to drag or apply this formatting across multiple columns or rows. This ensures the rule updates for each new pair.

Solution 2

Apply Conditional Formatting to Alternating Goal and Result Rows

If your spreadsheet alternates between goal rows and result rows continuously, use a MOD function to ensure the formatting only applies to the result rows.

WPS Spreadsheet Solution

Track Your Goals with Conditional Formatting in WPS Spreadsheet

You can easily set up advanced comparison formulas and conditional formatting in WPS Spreadsheet. It provides a familiar, robust environment for organizing and visually managing your data.

1. Open your document: Launch WPS Spreadsheet and select the result cells you want to format.
2. Access Conditional Formatting: Navigate to the Home tab and click on 'Conditional Formatting' > 'New Rule'.
3. Set the formula: Select 'Use a formula to determine which cells to format', input your comparison formula (e.g., =B2>B1), set the color to green, and save.

Fully compatible with Microsoft Excel formulas and conditional formatting rules.Lightweight, fast, and completely free to use.Intuitive interface that makes setting up complex data validation rules simple.Seamlessly open, edit, and save .xlsx files without losing existing formatting.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting applying to the goal row instead of the result row?

This usually happens when the selected range includes both goal and result rows, but the rule lacks a specific condition to differentiate them. Ensure you either select only the result cells before applying the rule, or use the MOD and ROW functions to target specific alternating rows.

How do I highlight the cell yellow if the result exactly matches the goal?

You can create a third conditional formatting rule using the equals operator. Go to New Rule, use the formula =B2=B1 (assuming B2 is result and B1 is goal), and set the formatting fill color to yellow.

Will my conditional formatting update automatically when goal numbers change?

Yes, as long as you use cell references (like B1) in your conditional formatting formula rather than static numbers, the cell colors will dynamically update whenever the goal or result values are modified in the spreadsheet.

Document & File Issues