Conditionally Formatting Above/Below Average Values

A woman works at a laptop

Etienne Boulanger / Unsplash

Excel's conditional formatting options allow you to change formatting features, such as background color, borders, or font formatting when data meets certain conditions. Overdue dates, for example, can be formatted to show up with a red background or a green font color or both.

Conditional formatting is applied to one or more cells and, when the data in those cells meet the condition or conditions specified, the chosen formats are applied. Excel has a number of pre-set conditional formatting options that make it easy to apply commonly used conditions to data. These pre-set options include finding numbers that are above or below the average value for the selected range of data.

Finding Above Average Values with Conditional Formatting

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.

    Screenshot of Conditional Formatting drop-down list in Excel
  4. Choose Top / Bottom Rules > Above Average to open the conditional formatting dialog box.

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

    Screenshot of Top/Bottom Rules list in Excel
  5. Select the down arrow on the right side of the drop-down list to open it.

    Screenshot of Format cells that are ABOVE AVERAGE options
  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 pre-set options, use the Custom Format option at the bottom of the list to choose your own formatting choices

  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 the number in 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, for step 5 of the above example select the Below Average option and then follow the same steps.