Software & Apps MS Office Conditionally Formatting Above/Below Average Values by Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. our editorial process Ted French Updated on May 05, 2020 MS Office Excel Word Powerpoint Outlook Tweet Share Email Excel's conditional formatting options allow you to change formatting features, such as background color, borders, or font formatting when data 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. 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 Highlight cells A1 to A7. Select Conditional Formatting in the Styles group of the Home tab. 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 Select 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 Select 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 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 the same steps.