Conditional Formatting Overview
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.
- Enter the following data into cells A1 to A7:
8, 12, 16, 13, 17, 15, 24
- Drag select cells A1 to A7 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
- The dialog box contains a drop down list of pre-set formatting options that can be applied to the selected cells
- Click on the down arrow on the right side of the drop down list to open it
- 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
- Once you have selected a formatting option, click 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
- Note Formatting was not applied to cell A6 since the number in the cell is equal to the average value not higher than 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 though 10.