How to Conditionally Format Above/Below Average Values GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office

Conditionally Formatting Above/Below Average Values

By Ted French Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. lifewire's editorial guidelines Updated on March 7, 2021 Tweet Share Email Tweet Share Email MS Office Excel Word Powerpoint Outlook

What to Know

Select a range of cells containing numerical data. Choose Conditional Formatting in the Styles group of the Home tab.Choose Top/Bottom Rules > Above Average to open the conditional formatting dialog box.Select the down arrow and select a formatting option, such as Light Red Fill with Dark Red Text. Select OK. This article explains how to conditionally format above and below average values in Excel.

Finding Above Average Values With Conditional Formatting

Excel's conditional formatting options allow you to change formatting features, such as background color, borders, or font formatting when data meets certain conditions. When the data in those cells meet the condition or conditions specified, the chosen formats are applied. 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 preset 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 preset 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 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, select the Below Average option for step 4 of the above example and then follow the same steps. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Use the ISBLANK Function in Excel How to Change the Number of Decimal Places in Excel How to Use the Google Spreadsheets AVERAGE Function Use Excel's EOMONTH Function to Add or Subtract Months How to Highlight and Find Duplicates in Google Sheets How to Round Numbers Down in Excel With the ROUNDDOWN Function Using Formulas for Conditional Formatting in Excel Ignore Error Values When Finding the Average in Excel How to Make a Schedule in Excel How to Create a Histogram in Excel for Windows or Mac Formatting Numbers in Excel Using Shortcut Keys How to Sort Data in Excel How to Use the Excel TRUNC Function How to Use AVERAGEIF Function in Excel Learn How to Remove Extra Spaces From Excel Using TRIM Round up Numbers in Excel With the ROUNDUP Function Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies