Learn how to highlight top records in Excel.
This tip is able to answer thes top question people has always asked me.
- How do I select top 5 values in Excel?,
- How do I highlight 3 duplicates in Excel?,
- How do I select top 10 values in Excel?,
- How do I create a dynamic top 10 list in Excel?,
Here are the steps outlined in my video.
Highlight Top Records
1) Select dataset
2) Home ~ Styles ~ Conditional Formatting ~ New Rule
3) Use formula to determine which cells to format
4) =$C5＞=LARGE($C$5:$C$24,$C$2)
5) Format
6) Fill
7) Select color
8) OK
9) OK
Lets breakdown this formula.
=$C5>=LARGE($C$5:$C$24,$C$2)
1) =$C5
This part of the formula refers to the cell in the C column and the row number 5. It means it's comparing the value in cell C5 with something else.
2) >=
This is the comparison operator "greater than or equal to." It checks if the value on the left is greater than or equal to the value on the right.
3) LARGE($C$5:$C$30,$C$2)
This part of the formula calculates the C2th largest value in the range of cells C5 to C24. In other words, it finds the C2'nd largest value in that range. For example, if cell C2 contains the value 3, the formula LARGE($C$5:$C$30,$C$2) will return the third largest value in the range C5 to C24.
So, when you put it all together, the formula is comparing the value in cell C5 with the C2nd largest value in the range C5 to C24 and checking if it's greater than or equal to that value. The result will be either TRUE or FALSE based on whether the condition is met or not.
How do I select top 5 values in Excel?,How do I highlight 3 duplicates in Excel?,How do I select top 10 values in Excel?,How do I create a dynamic top 10 list in Excel?,
