Conditionally Formatting Above/Below Average Values

Finding Above Average Numbers with Conditional Formatting

Ted French

Excel's conditional formatting options allow you to apply different formatting options, such as background color, borders, or font formatting to data that 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. Starting with Excel 2007, 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.

Tutorial Steps

  1. Enter the following data into cells A1 to A7:
    1. 8, 12, 16, 13, 17, 15, 24
  2. Highlight cells A1 to A7
  3. Click on the Home tab
  4. Click on the Conditional Formatting icon on the ribbon to open the drop down menu
  5. Choose Top / Bottom Rules > Above Average... to open the conditional formatting dialog box
  6. The dialog box contains a drop-down list of pre-set formatting options that can be applied to the selected cells
  7. Click on the down arrow on the right side of the drop-down list to open it
  8. Choose a formatting option for the data - this example uses Light Red Fill with Dark Red Text
  9. 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
  10. Once you have selected a formatting option, click OK to accept the changes and return to the worksheet
  11. Cells A3, A5, and A7 in the worksheet should now be formatted with the chosen formatting options
  12. The average value for the data is 15, therefore, only the number in these three cells contain numbers that are above the average

    Note 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 steps 6 through 10.