Excel Top/Bottom Rules

Top/Bottom Rules

Top/Bottom Rules are premade types of conditional formatting in Excel used to change the appearance of cells in a range based on your specifiedconditions.

Here is the Top/Bottom Rules part of the conditional formatting menu:

Appearance Options

The web browser version of Excel offers the following appearance options for conditionally formatted cells:

• 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

The "Top 10 Items..." and "Bottom 10 Items..." rules will highlight cells with one of the appearance options based on the cell value being the top or bottom values in a range.

NameType 1Speed BulbasaurGrass45 IvysaurGrass60 VenusaurGrass80 CharmanderFire65 CharmeleonFire80 CharizardFire100 SquirtleWater43 WartortleWater58 BlastoiseWater78 CaterpieBug45 MetapodBug30 ButterfreeBug70 WeedleBug50 KakunaBug35 BeedrillBug75 PidgeyNormal56 PidgeottoNormal71 PidgeotNormal101 RattataNormal72 RaticateNormal97 SpearowNormal70 FearowNormal100 EkansPoison55 ArbokPoison80 PikachuElectric90 RaichuElectric110 SandshrewGround40 SandslashGround65 Nidoran♀Poison41 NidorinaPoison56

Copy Values

You canchoose any range for where the Highlight Cell Rule should apply. It can be a few cells, a single column, a single row, or a combination of multiple cells, rows and columns.

Let's first apply theTop 10 Items... rule to the Speed values.

"Top 10 Items..." Rule, step by step:

1. Select the rangeC2:C31 for Speed values

2. Click on the Conditional Formatting icon in the ribbon, fromHome menu
3. SelectTop/Bottom Rules from the drop-down menu
4. SelectTop 10 Items... from the menu

This will open a dialog box where you can specify the value and the appearance option.

5. Leave the default value10 in the input field
6. Select the appearance option "Green Fill with Dark Green Text" from the dropdown menu

Now, the 10 cells with the top values will be highlighted in green:

Great! Now, the top 10 Speed values are easily identified.

Let's try the same with the bottom 10 Speed values.

Repeat the steps, but instead chooseBottom 10 Items... in the menu and select the "Light Red Fill with Dark Red Text" appearance option.

Now, the slowest values are also highlighted:

Notice that there are actually 11 items highlighted in red. Why could this be?

Let'ssort the table with descending Speed values for a clue:

We can see that both Pidgey and Nidorina have a Speed of 56. So, they are tied for the 10th bottom value.

Top/Bottom 10% Example

The "Top 10%..." and "Bottom 10%..." rules will highlight cells with one of the appearance options based on the cell value being the top or bottompercent of values in a range.

Let's apply Top and Bottom 10% rules to the same dataset with sorted Speed values:

NameType 1Speed RaichuElectric110 PidgeotNormal101 CharizardFire100 FearowNormal100 RaticateNormal97 PikachuElectric90 VenusaurGrass80 CharmeleonFire80 ArbokPoison80 BlastoiseWater78 BeedrillBug75 RattataNormal72 PidgeottoNormal71 ButterfreeBug70 SpearowNormal70 CharmanderFire65 SandslashGround65 IvysaurGrass60 WartortleWater58 PidgeyNormal56 NidorinaPoison56 EkansPoison55 WeedleBug50 BulbasaurGrass45 CaterpieBug45 SquirtleWater43 Nidoran♀Poison41 SandshrewGround40 KakunaBug35 MetapodBug30

Copy Values

You canchoose any range for where the Highlight Cell Rule should apply. It can be a few cells, a single column, a single row, or a combination of multiple cells, rows and columns.

Let's first apply theBottom 10%... rule to the Speed values.

"Bottom 10%..." Rule, step by step:

1. Select the rangeC2:C31 for Speed values

2. Click on the Conditional Formatting icon in the ribbon, fromHome menu
3. SelectTop/Bottom Rules from the drop-down menu
4. SelectBottom 10%... from the menu

This will open a dialog box where you can specify the value and the appearance option.

5. Leave the default value10 in the input field
6. Select the appearance option "Light Red Fill with Dark Red Text" from the dropdown menu

Now, the bottom 10% of cells will be highlighted in red:

Notice that there are 3 highlighted cells.

There are 30 cells in the range, so 10% of that is 3 cells.

Let's try the same with the top 10% Speed values.

Repeat the steps, but instead chooseTop 10%... in the menu and select the "Green Fill with Dark Green Text" appearance option.

Now, the fastest values are also highlighted:

Notice that there are actually 4 items highlighted in green.

We can see that both Charizard and Fearow have a Speed of 100. So, they are tied for the 3rd top value.

Both are included in the top 10% and are highlighted.