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.
- Enter the following data into cells A1 to A7:
8, 12, 16, 13, 17, 15, 24
- Drag select cells A1 to A7 on the spreadsheet to highlight them.
- Click on the Home tab.
- Click on the Conditional Formatting icon on the ribbon to open the drop down menu.
- Choose Top / Bottom Rules > Above Average... to open the conditional formatting dialog box.
- Click on the down arrow of the format options drop down menu (right side) to open the list.
- Select Light Red Fill with Dark Red Text from the list.
- Click OK to accept the changes and close the dialog box.
- 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.
- Note that the formatting in cell A6 has not changed since the number is equal to the average value not higher than it.