Highlight Require Data Using Conditional Formatting
Conditional Formatting Rules Usage
In the MS Excel highlight Selected Data as per multiple conditions using Conditional formatting.
First Option available for Highlight Cells rules for Numbers Greater then, Less than, Equal to and for Text option text that contains, for date Next week, Last week Today and more option. Click here to learn Highlight Cells Rules.
In this post we are going to learn Top / Bottom rules. Selected data can highlight by Numbers top 10 numbers, Bottom 10 numbers, top 10 percent, Bottom 10 %, Above Average & bellow Average.
Apply 6 Top / Bottom Rules
1. Top 10 Items
2. Top 10 %
3. Bottom 10 Item
4. Bottom 10%
5. Above Average
6. Bellow Average
Using this 6 rule you can highlight top & bottom Item by Value calculation. In the function name top 10/ Bottom 10 numbers given by default. But you can change preference as per your requirement. Means Top 30, Bottom 3 like this rules apply. Lets Start how to apply!
1. Top 10 Item –
Top 10 items means in the selected range higher 10 value.
For Highlight Top 10 Items Rules Select your Data
Here we are selecting Data B4:B23. Then go to Home tab Click on conditional formatting then click Top bottom rules Then Top 10 Items….
Home >> Conditional Formatting >> Top/Bottom Rules >> Top 10 Items….
Following Image new window is opened. By default 10 Value is showing.
and the selected range is highlighted in red text higher 10 values.
You can change top numbers.You can choose less any numbers as per requirement.
By default format is light red with dark red text can change other format. 5 format available. you can create your own custom format.
You can change value after applying formula. cells format will be live change according Value. Need not time to time apply again formula. In the one cell you can apply multiple conditional formatting rules.
Clear Rules –
For the clear rules from the data Select data. In the home tab >> conditional formatting >> clear rules >> clear rules from selected cell.
Remove the rules from only selected cells.
For Clear entire sheet rules Home >> Conditional formatting >> Clear rules >> Clear rule from entire sheet.
Remove all rules from Sheet.
2. Top 10 %
Highlight cells that value includes in top 10 percentage. Top 10 %. Percentage calculate according the data.
For apply this function same process as we are learn in top 10 Item.
Home >> conditional formatting >> Top/Bottom Rules >> Top 10 %
You can change percentage rate & format.
3. Bottom 10 Item
Highlight bottom require numbers of Item.
Home >> conditional formatting >> Top/Bottom Rules >> Bottom 10 item.
Change number of items & format as per requirement.
4. Bottom 10%
Highlight cells that value includes in Bottom 10 percentage. Bottom 10 %. Percentage calculate according the data.
For apply this function same process as we are learn in top 10 Item.
Home >> conditional formatting >> Top/Bottom Rules >> Bottom 10 %
You can change percentage rate & format.
5. Above Average
In this function excel calculate selected data average. Highlight Cell that value will above average.
In the following Image Column J data average is 10.5. Highlights only that cell value is above Average
Home >> conditional formatting >> Top/Bottom Rules >> Above Average
6. Bellow Average
Reverse function of Above average is Bellow.
Home >> conditional formatting >> Top/Bottom Rules >> Bellow Average
