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

Finding Below 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 below the average value can easily be spotted.

For more Excel tips and tricks go to my static page:  Excel Tutorials and Tricks Series.

Example: Finding Below 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 > Below 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 A1, A2, and A4 should be formatted with a light red background color and dark red text since the numbers in these cells are less than 15.


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