Top Bottom Rules
Top/Bottom Rules
Top/Bottom Excel rules are pre-made conditional formatting types that you can apply to modify a range’s cell look according to your own criteria.
This is the conditional formatting menu’s Top/Bottom Rules section:
Appearance Options
The Excel web browser add-in provides the following appearance options for cells that are conditionally formatted:
- Light Red Fill with Dark Red Text
- Yellow Fill with Dark Yellow Text
- Green Fill with Dark Green Text
- Light Red Fill
- Red Text
- Red Border
Here is how the options look in a spreadsheet:
Top/Bottom 10 Items Example
Cells with one of the appearance options will be highlighted by the “Top 10 Items…” and “Bottom 10 Items…” rules if the cell value falls within the top or bottom range.
Note: The number of items that are highlighted at the top and bottom can be any whole number between 10 and 1000.
Any range can be selected as the one to which the Highlight Cell Rule should be applied. It could consist of a few cells, one row, one column, or a mix of several rows, columns, and cells.
First, let’s apply the Speed values to the Top 10 Items… rule.
“Top 10 Items…” Rule, step by step:
- Select the range C2:C31 for Speed values
2. Click on the Conditional Formatting icon
in the ribbon, from Home menu
3. Select Top/Bottom Rules from the drop-down menu
4. Select Top 10 Items… from the menu
This will cause a dialog box to popup, allowing you to choose the appearance option and value.
5. In the input field, leave the default value set to 10.
6. From the dropdown menu, choose “Green Fill with Dark Green Text” as the appearance choice.
The top 10 cells will now be highlighted in green, as follows:
Fantastic! The top 10 Speed values can now be quickly found.
Let’s give the bottom 10 Speed values a similar try.
Note: The same range might have many rules applied to it.
Instead, choose Bottom 10 Items… from the menu and pick the “Light Red Fill with Dark Red Text” appearance option. Then, repeat the steps.
The slowest values are now additionally emphasized:
Notice that there are actually 11 items highlighted in red. Why could this be?
Let’s sort the table with descending Speed values for a clue:
It’s evident that Pidgey and Nidorina have identical speeds of 56. They are therefore tied for the tenth lowest value.
Note: All ties will be noted when you use the Top/Bottom 10… conditional formatting.
Top/Bottom 10% Example
Cells that have one of the appearance options highlighted will be those whose cell value is in the top or bottom percent of values inside a range, according to the “Top 10%…” and “Bottom 10%…” rules.
Note: The Top/Bottom Percent to be highlighted can be set to any whole number up to 100. The default percent of items is 10.
Let’s use the same dataset with sorted Speed values and apply the Top and Bottom 10% rules to it:
Any range can be selected as the one to which the Highlight Cell Rule should be applied. It could consist of a few cells, one row, one column, or a mix of several rows, columns, and cells.
First, let’s apply the Speed values to the Bottom 10%… rule.
“Bottom 10%…” Step-by-step guideline:
- Choose the C2–C31 range to get the speed numbers.
2. Click on the Conditional Formatting icon in the ribbon, from Home menu
3. Select Top/Bottom Rules from the drop-down menu
4. Select Bottom 10%… from the menu
This will cause a dialog box to popup, allowing you to choose the appearance option and value.
5. In the input field, leave the default value set to 10.
6. From the dropdown menu, choose “Light Red Fill with Dark Red Text” as your appearance option.
Now, the bottom 10% of cells will be highlighted in red:
Three cells are indicated; this should be noted.
Ten percent of the 30 cells in the range are 3 cells.
Note: Depending on the %, the number of cells will be rounded to the nearest whole number.
As an illustration:
- 1.8, or 6% of 30, will be rounded to one cell.
- 2.1, or 7% of 30, will be rounded to two cells.
Let’s give the top 10% of Speed values a shot.
Instead, choose Top 10%… from the menu and pick the “Green Fill with Dark Green Text” appearance option. Then, repeat the steps.
The fastest values are now also highlighted:
As you can see, there are actually four objects that are green-highlighted.
It is evident that Fearow and Charizard both have a speed of 100. They are therefore tied for third place.
Both are highlighted and rank in the top 10%.
Note: Manage Rules allows you to remove the Highlight Cell Rules.
