Highlight Top Records In Excel - Excel Tips and Tricks - video Dailymotion

ExcelTips247

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?,

Transcript

00:00I recently published a video on how you can highlight the top two, maybe three salesmen

00:05from your data set simply by entering a number on your cell. However, someone has inquired if I can

00:10do the same feature on an Excel. Well, this is how you do it. First, you're going to highlight

00:16your data set, excluding the header, only the salesperson and the sales value. Then you go

00:21home, styles, conditional formatting. Over here, you're going to say new rule. In this new rule,

00:27you're going to select use a formula to determine which cell to format. Over here, you're going

00:32to say equal. First of all, you're going to select the very first cell on your sales row,

00:39and then you're going to press F4 twice like this, one, two, which makes the column fixed

00:45while the rows are not fixed. Then after that, you're going to say greater than or equal to

00:51because we're looking for the top two or three or whatever you enter here. Greater than or

00:56equal to, and we're going to use a function called large. The first argument on the function

01:02large would be the entire sales column like this, and then you're going to press comma,

01:08and the second argument on large would be whatever the user entered in this cell here, and you're

01:14going to close parenthesis. The function large will return the two largest value in the range

01:20C5 to C24 for this example. If I were to change this two to three, you'll find the largest three

01:26values in cell ranging from C5 to C24. Once that's done, you're going to click on format.

01:33After that, you're going to go to fill tab here and select any color that you want to highlight.

01:38See, I'm going to highlight with yellow and click OK, and on this new formatting rule, you're going to click

01:44OK. Now you can see that you have the two highest values being highlighted by this new conditional formatting

01:51feature that you've added. Now if you were to change this to three, you can see top three values

01:56are highlighted, and if you want only the top one value, you just put one here, hit enter,

02:02the topest value on your whole data set is highlighted.

Highlight Top Records In Excel - Excel Tips and Tricks

Category

Transcript

Comments

Recommended