Conditionally Formatting Above/Below Average Values

What to Know

  • Select a range of cells containing numerical data. Choose Conditional Formatting in the Styles group of the Home tab.
  • Choose Top/Bottom Rules > Above Average to open the conditional formatting dialog box.
  • Select the down arrow and select a formatting option, such as Light Red Fill with Dark Red Text. Select OK.

This article explains how to conditionally format above and below average values in Excel.

Finding Above Average Values With Conditional Formatting

Excel's conditional formatting options allow you to change formatting features, such as background color, borders, or font formatting when data meets certain conditions. When the data in those cells meet the condition or conditions specified, the chosen formats are applied.

This example covers the steps to follow to find numbers that are above average for the selected range. These same steps can be used to find below-average values.

  1. Enter the following data into cells A1 to A7:

    8, 12, 16, 13, 17, 15, 24

  2. Highlight cells A1 to A7.

    Screenshot of selected cells in Excel
  3. Select Conditional Formatting in the Styles group of the Home tab.

    A screenshot of Excel with the Conditional Formatting heading highlighted
  4. Choose Top/Bottom Rules > Above Average to open the conditional formatting dialog box.

    The dialog box contains a drop-down list of preset formatting options that can be applied to the selected cells

    A screenshot of Excel with the Conditional Formatting options highlighted
  5. Select the down arrow on the right side of the drop-down list to open it.

    A screenshot of Excel with the Conditional Formatting dropdown menu highlighted
  6. Choose a formatting option for the data. This example uses Light Red Fill with Dark Red Text.

    If you do not like any of the preset options, use the Custom Format option at the bottom of the list to choose your own formatting choices

    A screenshot of Excel with the Conditional Formatting options highlighted
  7. Select OK to accept the changes and return to the worksheet. Cells A3, A5, and A7 in the worksheet should now be formatted with the chosen formatting options. The average value for the data is 15; therefore, only these three cells contain numbers that are above the average

Formatting was not applied to cell A6 since the number in the cell is equal to the average value and not above it.

Finding Below Average Values With Conditional Formatting

To find below-average numbers, select the Below Average option for step 4 of the above example and then follow the same steps.

Was this page helpful?