Excel Tutorials: How to Find Above Average Values with Conditional Formatting

Finding Above Average Values with Conditional Formatting

Adding conditional formatting to a cell in Excel allows you to apply different formatting options, such as color, to a cell or cells that meet certain conditions.
Excel 2007 allows you to easily add formatting so that in a table or list of data, the numbers that are above the average value can easily be spotted.
Example: Finding Above Average Values in Excel
Note: For help with this example, see the image above.

1. Enter the following data into cells A1 to A7:
   
   8, 12, 16, 13, 17, 15, 24


2. Drag select cells A1 to A7 on the spreadsheet to highlight them.


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. Click on the down arrow of the format options drop down menu (right side) to open the list.


7. Select Light Red Fill with Dark Red Text from the list.


8. Click OK to accept the changes and close the dialog box.


9. Since the average for the numbers in cells A1 to A7 is 15, cells A3, A5, and A7 should be formatted with a light red background color and dark red text since the numbers in these cells are higher than 15.


10. Note that the formatting in cell A6 has not changed since the number is equal to the average value not higher than it.