How to Highlight Values Above or Below the Average in Google Sheets

When auditing financial records, student test scores, sales quotas, or web metrics in Google Sheets, quickly identifying figures that outperform or lag behind the group mean is a fundamental data analysis task. While desktop spreadsheet applications like Microsoft Excel feature preset “Top/Bottom Rules” for above and below average values, Google Sheets achieves this dynamic capability through custom conditional formatting formulas.

Using a lightweight custom formula ensures that your conditional formatting remains fully dynamic. As new rows are added or existing numbers are modified, the mathematical average recalculates automatically, instantly updating the highlighted cell colours across your sheet. Here is how to highlight values above or below average in Google Sheets step by step.

Understanding the Custom Formula Logic

To evaluate whether an individual cell value exceeds the dataset mean, conditional formatting tests each cell against a calculated average using an inequality operator:

=B2>AVERAGE($B$2:$B$50)

Understanding the mix of relative and absolute references is critical:

• Relative Reference (B2): Refers to the first active cell in your selected range without dollar signs. As Google Sheets evaluates the rule across rows, this reference dynamically shifts to B3, B4, and so on.
• Absolute Reference ($B$2:$B$50): Fixed with dollar signs to anchor the entire evaluation range. This guarantees that the AVERAGE calculation remains constant for every row evaluated rather than drifting down the sheet.

Step-by-Step: Highlighting Values Above the Average

1. Open your spreadsheet in Google Sheets.
2. Select the numeric range you wish to format (for example, cells B2:B50).
3. Click Format in the top menu and select Conditional formatting from the dropdown.
4. The Conditional format rules panel opens on the right side of the screen. Ensure your selected range is accurately reflected in the Apply to range field.
5. Under the Format rules section, click the Format cells if… dropdown menu and scroll down to select Custom formula is.
6. In the Value or formula box that appears, enter the following formula:

   =B2>AVERAGE($B$2:$B$50)

   (Note: Replace B2 with your top-left cell reference and $B$2:$B$50 with your full absolute range).
7. Under Formatting style, customise your highlight colour (such as a soft green fill with dark green text) to represent above-average performance.
8. Click Done to apply the rule.

Step-by-Step: Highlighting Values Below the Average (Ignoring Blank Cells)

Highlighting below-average values requires one crucial enhancement: preventing empty cells from being treated as zero. If your dataset contains blank rows, standard inequality logic will evaluate blank cells (value 0) as below average and highlight them unnecessarily.

To ensure only populated numeric cells are evaluated, wrap the condition in an AND statement with ISNUMBER:

1. With your target range still selected, click Add another rule in the Conditional format rules side panel.
2. Set the Format cells if… dropdown to Custom formula is.
3. In the formula input box, enter this robust validation formula:

   =AND(ISNUMBER(B2), B2<AVERAGE($B$2:$B$50))

4. Under Formatting style, select a distinct warning colour (such as a light red or amber fill).
5. Click Done.

Now, only genuine numeric entries lower than the average will highlight, leaving blank rows untouched.

Alternative Variations: Equal to Average and Standard Deviations

Depending on your analytical requirements, you can adapt the custom formula to match specific thresholds:

• Greater Than or Equal to Average: To include values that match the mean exactly, use >=:

  =B2>=AVERAGE($B$2:$B$50)

• Significantly Above Average (One Standard Deviation): To highlight top-tier outliers that exceed the mean by at least one standard deviation, use:

  =B2>(AVERAGE($B$2:$B$50)+STDEV($B$2:$B$50))

• Highlighting Entire Rows: If you want the entire row across columns A through D to highlight based on the value in column B, lock the column letter with a dollar sign ($B2) and apply the rule across range A2:D50:

  =$B2>AVERAGE($B$2:$B$50)

How to Manage and Reorder Formatting Rules

When multiple conditional rules are applied to the same range (such as separate rules for above average and below average), Google Sheets evaluates them in order from top to bottom:

• Hover over any rule card in the Conditional format rules panel to reveal the drag handle (three dots).
• Click and drag cards up or down to adjust evaluation priority if rule boundaries ever overlap.
• To remove a rule, hover over the card and click the trash can icon.