When analyzing data in Google Sheets, you may need to highlight groups when their total exceeds a target. This helps quickly identify products, departments, or categories that surpass set thresholds. In this tutorial, you’ll learn how to apply conditional formatting to highlight groups when their total exceeds a target in Google Sheets.
We’ll primarily focus on the single-table format, where both target and actual values are in the same table. However, we’ll also briefly cover how to handle separate target and actual tables using lookup functions. Let’s start!
Generic Formula to Highlight Groups When Their Total Exceeds a Target
=SUMIF(Group_Range, Current_Group, Value_Range) > Target_CellExplanation:
Group_Range: The column containing group names/items/categories. (e.g.,$A$2:$A)Current_Group: The specific group/item being evaluated. (e.g.,$A2)Value_Range: The column with actual values to sum. (e.g.,$C$2:$C)Target_Cell: The reference cell containing the target value. (e.g.,$B2)
Let’s apply this to sample data.
Step 1: Set Up the Sample Data
The sample data consists of products in column A, their target values in column B, and actual sales in column C.
Since column A is sorted by product, you can easily verify group totals and compare them against targets. Sorting is not required for the highlighting to work.
Step 2: Apply Conditional Formatting to Highlight Groups When Their Total Exceeds the Target
Steps to Apply the Conditional Formatting Rule:
- Select the range → Select A2:A10 (or A2:C10 if you want to highlight all columns).
- Go to → Click Format > Conditional formatting.
- Choose Rule Type → Under Format rules, select Custom formula is.
- Enter the Formula →
=SUMIF($A$2:$A, $A2, $C$2:$C) > $B2 - Choose Formatting Style → Pick a highlight color.
- Click Done → The groups where the total exceeds the target will now be highlighted.
Highlight Groups When the Total Meets or Exceeds the Target
If you also want to highlight groups that meet or exceed the target, use this formula instead:
=AND(N($B2), SUMIF($A$2:$A, $A2, $C$2:$C) >= $B2)Handling Separate Target and Actual Tables
In the above example, the target and actual values are in the same table. However, sometimes targets are stored in a separate table. In that case, you can use XLOOKUP to fetch the target value corresponding to each group:
Generic Formula for Separate Target and Actual Tables
=SUMIF(Group_Range, Current_Group, Value_Range) > XLOOKUP(Current_Group, Target_Lookup_Range, Target_Value_Range)Explanation:
Group_Range: The column containing group names (e.g.,$A$2:$A).Current_Group: The specific group being evaluated (e.g.,$A2).Value_Range: The column with actual values to sum (e.g.,$B$2:$B).Target_Lookup_Range: The column in the target table containing group names (e.g.,$D$2:$D).Target_Value_Range: The column in the target table containing the target values (e.g.,$E$2:$E).
Example Formulas:
- Highlight when the total exceeds the target:
=SUMIF($A$2:$A, $A2, $B$2:$B) > XLOOKUP($A2, $D$2:$D, $E$2:$E) - Highlight when the total meets or exceeds the target:
=SUMIF($A$2:$A, $A2, $B$2:$B) >= XLOOKUP($A2, $D$2:$D, $E$2:$E)
Additional Tips & Wrap-up
Ensure the correct data range is selected before applying conditional formatting. If you’re using a separate target table, make sure the group names match exactly, avoiding extra spaces or inconsistent capitalization.
I recommend using XLOOKUP to fetch target values, but you can also use VLOOKUP.
By following this tutorial, you can highlight groups when their total exceeds a target in Google Sheets, making it easier to analyze data trends and spot key performance insights. Whether your target and actual values are in one table or separate tables, you can apply the right formula and formatting rule to get accurate results.
Let me know if you have any questions!
