Above Average and Below Average: The Top Bottom Rule of Excel Explained
The "Top/Bottom Rules" in Excel conditional formatting let you highlight cells that fall within the top or bottom percentage or rank of values within a given range.
A. Above Average:
Excel will highlight cells with values above the average value in the chosen range if you choose the "Above Average" option under the Top/Bottom Rules. It means that every cell of which above the range's average value will be formatted in accordance with the instructions you select.
As per above image the selected cells average is 24.6. So, you can see that all the above number of 24.6 get highlighted in red colour by this format in selected cells.
To use the "Above Average" conditional formatting rule in Excel, follow these steps:
1. Select the range of cells that you want to apply the conditional formatting to.
2. Go to the "Home" tab on the Excel ribbon.
3. Click on the "Conditional Formatting" button in the Styles group.
4. Choose "Top/Bottom rules" from the drop-down menu.
5. Click on "Above Average" from the sub menu.
B. Below Average:
The "below average" option in the top/bottom rules of Excel conditional formatting lets you highlight cells that go below the range's average value. This implies that any cell that has a value below the range's average will be formatted using the parameters you've chosen, like altering the fill-in or font colors. Any cells with values below the average of the chosen range will be formatted in accordance with the defined rule when Excel conditional formatting is enabled and the "below average" option is selected in the top bottom rule. This could involve using different formatting options, such as altering the background color or font color of the cell, to visually differentiate these below-average results from the rest of the data.
As per above image the selected cells average is 24.6. So, you can see that all the below number of 24.6 get highlighted with green colour by this format in selected cells.
To use the below average conditional formatting rule in Excel, follow these steps:
1. Select the range of cells that you want to apply the formatting to.
2. Go to the Home tab on the Excel Ribbon.
3. Click on the Conditional Formatting option in the Styles group.
4. Choose "Top/Bottom rules" from the drop-down menu.
5. Click on "Below Average: from the sub menu
C. Menu Rules:
This menu rules takes yo through the new Formatting Rule in which you can use each and every conditional format to highlights cells which we were already discuss in previous blogs.
