Bored of seeing huge data contain numbers and text? Here is the conditional formatting that helps the user to present the huge data in a quick read-able manner. Top/Bottom Rules are part of the Conditional Formatting tool in MS Excel. Conditional formatting is the most used feature in the spreadsheet that allows the user to apply specific formatting to cells that meet certain criteria. This is often used as color-based formatting to highlight, emphasize, or differentiate among data. It helps identify variances in cell values with a quick glance.
Đang xem: Top/bottom rules
Conditional formatting available from the Excel 2007 version and exists till now. Conditional formatting options are available under HOME>>Conditional formatting.
Before stepping into the process, here are a few essential points that everyone should know while using conditional formatting.
The conditional formatting does not work in an empty cell. It just skips that cell.All the conditional formatting is based on (IF -then logic: if X criteria are true, then Y formatting will be applied).It can apply multiple conditions to a single cell or an entire array.
TOP/Bottom rules is used to highlight the cell which has the highest values in the selected cell and similar for the bottom. Below figure 1 represents the selling price of each product. With the given data below, we are going to use conditional formatting to identify the TOP 10 items which have the highest selling prices. Show inFigure 1.
STEP-1In the below Figure2, selected the entire cell or array, for which the user wants to apply the conditional formatting.
Step-3 Once the above steps are completed. A dialogue box will pop-up on your screen as shown in figure 4
In the left-field, there is a tiny arrow used to change the number of items from the top.In the right field, a drop-down is used to change which formatting that is applied to the top 10. We can customize the formatting color according to user needs.
To present the data in a more attractive manner, here is a tip:
Just click the drop-down icon, you see an option Custom format shown above in Figure 5.Once we click to the custom format, we will be landed in another dialogue box (font cell) as shown below Figure 5.1
Here the user can display his presentation skills by changing the font style, border, and fill the cell with a different color.
Step-4 After completing the above steps the results we are as shown in below Figure 6. That’s how you make a conditional formatting rule for the Top 10 items in your data. Here, the cell is in pink color and the text is highlighted with red color. Note: The results are acquired on the basis of the default style.
NOTE: All the above steps can be used to get the bottom 10 items.
Bonus stepIn case if the user wants to remove conditional formatting (bring back to the old style). Just Select the cell/array. Go to HOME >> Conditional Formatting >>Clear Rules > two options will be displayed as shown in Figure 7 below.
Figure 7 Clear rules from selected cells -The option is used clear all conditional formatting giving to the selected cells.
Clear rules from the Entire sheet – The option is used to clear all conditional formatting giving in the active spreadsheet.
You can view our videos available on our Youtube channel to enhance and refresh your Excel knowledge.