Highlight Top 10 Values using Excel Conditional Formatting

Posted on March 17th, 2009 in Excel Howtos , Learn Excel - 44 comments

Highlight Top Values in Range using ExcelIn excel conditional formatting basics article, we have learned the basics of excel conditional formatting. In this and the next 4 posts, we will learn some more nifty uses of excel conditional formatting.

Let us see how we can highlight top 5 or 10 values in a list using excel as shown aside:

To do this, you need to learn the excel formula – LARGE (more on large formula)

Large formula is used to fetch the nth largest value from a range of numbers. Refer to the above link for easy to understand help on large (and SMALL too)

To highlight the top 10 values,

1. Select the range of values and launch conditional formatting dialog.

2. Assuming you have cells in the range c5: c30, In the formula we need to specify a condition that would be true only if a value is more than or equal to the top 10th value in the range c5:c30 – LARGE($C$5:$C$30,10), thus our formula will be, C5>=LARGE($C$5:$C$30,10)

3. Finally specify the formatting you want to apply. When you are done, press ok.
Conditional Formatting Rules for Highlighting Top 10 Values
That is all.

If you want to highlight the entire row instead of a cell, you should use $C5 instead of C5. Why so? That is your home work. Here is a little tip on using relative vs. absolute cell references in excel.

To highlight bottom 10 in a list, all you need to do is change the formula from LARGE to SMALL.

Download the example workbook and learn how to highlight top 10 values in a range.

In the next article

We will learn how you can search a spreadsheet full of data using conditional formatting. So stay tuned and if you havent already, join our newsletter.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

44 Responses to “Highlight Top 10 Values using Excel Conditional Formatting”

  1. azmat says:

    This is cool trick…..as cool as my do the dew can!!

  2. andreas says:

    In case of duplicates in sales values Excel highlights more than 10 values. Is there a way to only get exacly 10?

    i know it might not be useful in this example, but i need the same kind of formula for another sheet.

  3. Chandoo says:

    @Andreas: Beautiful question. You can make sure only 10 items are highlighted no matter what by adding a very small running fraction to the numbers. That way you make them unique and then highlight them. You can use a helper column to do this so that your original data stays in tact.

    You can checkout more on this technique in our management dashboards posts : http://chandoo.org/wp/management-dashboards-excel

  4. andreas says:

    @Chandoo
    I was hoping there would be a way. Altough, I looked through these pages but couldn’t find a good example of this method. I am fairly new to Excel, so could you point me in the right direction?

    • Chandoo says:

      Hi Andreas… You can check this particular post http://chandoo.org/wp/2008/08/27/excel-kpi-dashboard-sort-2/ which is also linked on the earlier page I have told.

      We use the technique of adding a very small fraction to numbers to make sure they are always sorted properly.

      Also, Oscar, one of our readers sent me this through email: Andreas, try this conditional formatting formula in Chandoos example workbook:

      =AND($C5>=LARGE($C$5:$C$34,10),NOT(AND(IF(COUNT(IF($C$5:$C5=LARGE($C$5:$C$34,10),1,”"))>1,1,0),$C5=LARGE($C$5:$C$34,10))))

      Try this and let me know how it works…

  5. Lia says:

    I have been able to get this to work, but is there a way to list the highlighted items in another location in the spreadsheet in value order? Like a “top ten list” so I don’t have to sort through the whole list to find them?

  6. Wouter says:

    I have the same question as Lia.

    I want to make a Top 5 from SHEETa in a new sheet.

    The numbers I want to use are from F11 to F55. But there is also a matching decription

    (for example Brand A, Brand B ect.) from N11 to N55.

    The problem is that the numbers are NOT unique. Many are/could be the same.

    I used this formula to get the top 5 Numbers. I have put these in cells A1 to A5:

    =SMALL(SHEETa!F$11:F$55;1) Result:4,0
    =SMALL(SHEETa!F$11:F$55;2) Result:4,6
    =SMALL(SHEETa!F$11:F$55;3) Result:4,7
    =SMALL(SHEETa!F$11:F$55;4) Result:4,7
    =SMALL(SHEETa!F$11:F$55;5) Result:4,7

    This is correct.

    But the trouble starts when I want to show the matching Brand names to these numbers.
    I used this formula from B1 to B5

    =VLOOKUP(SMALL(SHEETa!F$11:F$55;1);SHEETa!$F$11:$N$55;9;0)

    As a result B3 to B5 all show Brand G but is should be 3 different ones.

    Is there a sollution to this?

  7. Chandoo says:

    @Lia: you can use the SMALL and LARGE functions to get this work. I am sorry, but I didnt see your comment until Wouter made his entry. You can learn more about these functions here:

    http://chandoo.org/excel-formulas/small.html
    http://chandoo.org/excel-formulas/large.html
    http://chandoo.org/wp/2008/08/13/15-microsoft-excel-formulas/

    @Wouter: You can solve this by making the numbers unique. This can be achieved by adding a small unique fraction (like 0.00001 for the first number, 0.00002 for the second etc.) to each of the numbers before sending them to the SMALL(). This can be done with a helper column so that your original values remain intact.

    I am sure you can follow this hint and solve the problem. But if you need some more help, feel free to ask.

  8. Glenn says:

    Changing to small does not give me the bottom 10. What am I doing wrong?

  9. Chandoo says:

    @Glenn… Have you changed the condition from >= to <= ?

  10. Glenn says:

    I did try <= and it wont highlight the bottom 10.

  11. Chandoo says:

    @Glenn.. strange.. can you share more about your data. May be you can upload the workbook somewhere and link it in a comment. That way one of us can take a look and see if we can help you .

    Also, did you check if there is any other conditional formatting on that range of cells with stop-if-true checked ? May be it is preventing you to set the formatting? You can clear conditional formatting by selecting the entire range and pressing clear > formats from ribbon.

  12. Glenn says:

    Got it… Thanks Guys, it was another conditional formatting preventing this to work.

  13. Les says:

    Hello Chandoo and others.
    I have a conditional formatting problem related to SMALL.
    I have a column of figures formatted as accounting numbers and includes zero’s. The column is $D$1:$D$24. I want to fomatt the smallest 3 numbers excluding zero’s. I also have the zero’s formatted white to be invisable but I have it ticked off ‘Stop if true’. The formula that works when there are no zeros is
    =IF($D10,$D1<=SMALL($B$1:$D$9,3)) but if there are zero’s it fails. Does anyone have the answer. Thankyou

  14. Chandoo says:

    @Les… Welcome to PHD and thanks for asking a question.

    I am not sure why you are using IF() in the conditional formatting. You can try the below formula to determine if a given cell is small 3 value excluding zeros…

    $D1<=small($D$1:$D$24,3+countif($D$1:$D$24,”0″))

    along with that if you set white font color or ;;; custom format code for the zero values, you can hide zeros and highlight bottom 3 values.

    Let me know if you face some difficulties.

  15. Les says:

    Hi Chandoo,
    It worked beautifully thank you. The IF() was a leftover part of a formula which should have been removed after an attempt at IF(0) trying to eliminate the inclusion of zero’s which failed miserably. You are a very kind person by helping people like me and I love how you do things differently particularly with Charts and think outside the square. Good luck to you sir.

  16. Bryan says:

    Chandoo,

    I am attempting to do the same thing as Les, but the formula is not working as intended.

    I have column J2 through J248 that requires conditional formatting, Bottom 10 values not including 0.

    When I attempted to edit the formula that you posted for Les to use, I edited it for my column range.

    =$J2<=SMALL($J$2:$J$248,10+COUNTIF($J$2:$J$248,”0?))

    When I use this, it only highlights the 0′s, in which case I have 53 items highlighted. Instead of highlighting the bottom 10 without the 0′s.

    Please help me figure out what I’m doing wrong.

    Thank you,

    Bryan

  17. Michelle says:

    Great tip on conditional formatting for top 10 figures!

    Question, is there a way of duplicating the conditional formatting for 10 years (or more) worth of data without highlighting each column and reapplying that cond formatting formula for each?

    That is, I want the top 10 figures for each year to automatically highlight but right now it seems the only way to do that will be to reapply conditional formatting to each column. Is there a quicker way?

    Thanks.

    • Chandoo says:

      You can use format painter to copy and paste conditional formats from one range to another. It would not more than few clicks to do this. Also, make sure any references in the formulas in the conditional formatting are relative.

  18. AWD says:

    I’m using the below conditional formatting formulas to select the Top 3 and Bottom 3 values in a range (excluding Zero). Some of the fields contain percentages (positive/negative) and is conditionally formatting the bottom 4-5 percentage values intead of the bottom 3 percentage values as specified in my formulas.

    Conditional Formatting Formulas:
    Condition 1: Cell Value Is equal to 0 (No Format Set)
    Condition 2: Formula Is =G81>=LARGE(G$79:G$88,3) (Green Font)
    Condition 3: Formula Is =G79<=SMALL(G$79:G$88,3+COUNTIF(G$79:G$88,"0")) (Red Font)

    The range of values is as follows: (G78:G88)
    % MOM
    -50.00% (Red Font)
    0.00% (Normal Black Font)
    -61.54% (Red Font)
    -44.44% (Red Font)
    -25.00% (Normal Black Font)
    -66.67% (Red Font)
    -75.00% (Red Font)
    -42.86% (Normal Black Font)
    66.67% (Green Font)
    0.00% (Normal Black Font)

    Any idea why this is happening?

    Thanks.

  19. AWD says:

    Correction to Conditional Formatting Formula:
    Condition 2: Formula Is =G79>=LARGE(G$79:G$88,3) (Green Font)

  20. Chandoo says:

    @AWD… you need not add the number of zeros to the small parameter, remember, zeros are greater than negative values. But if your data can often not have any negative values, then you need count of zeros. The condition will depend on how many negative values are there in bottom 3. A simpler alternative could be to just make a helper column and replace zeros with a really large or really small (totally negative) value. This way you can use the count formula as it is.

  21. ABDUL NISAR says:

    Dear

    i have 20 sheet detial in same format amount is differ…..i have pick 3 smallest on master one sheet.

    what is the right formula

  22. Chandoo says:

    @Abdul… use formula =small(masterdata,1) for smallest item, =small(masterdata,2), =small(masterdata,3) for other two.

  23. [...] Highlighting top 10 values in Excel | More #es-message {color:#111;background-color: #E4F0D5;border: 1px solid #CBE1AB;padding:5px;font-size:9pt;line-height:200%;font-family:verdana;} #es-msg-header {font-size:12pt;font-weight:lighter;color:#a90000;letter-spacing:-0.25pt;margin-bottom:5px;} #es-msg-cfa {background:#CBE1AB;padding:4px;border:#666 1px solid;display:block;margin-top:5px;text-align:center;width:100px;color:#111;} #es-msg-cfa:hover {background:#3e8613;color:#fff;} Do you want to Learn Excel Online? You are going to love my Excel School Program. It is a structured, in-depth online training program to make you even more awesome in Excel. I have opened the registrations for new batch of students on June 14th. Consider joining it if you want to become a star at office in a short period of time. Learn More » [...]

  24. vaasjoe says:

    I have 3 or more columns of server names, each for 1 month.
    I need to highlight, the unique servers added or deleted each month from 3 or 4 columns.
    some servers are common in all 4 months ( columns also contains their disk size ) but this is adjacent to names.

  25. Hui... says:

    @Vassajoe
    Have a read of http://chandoo.org/wp/2010/07/01/compare-lists-excel-tip/
    You may need to work through the columns a pair at a time

  26. V.SIDHU says:

    Dear Sir

    I have one store stock data ,That data contain MRP,System Qty,Phy Qty & Diff Qty.
    i need top fifty value in this data. how to get the top fifty value this data

    Regards.
    V.Sidhu

  27. Hui... says:

    V.Sidhu
    Quickest way is to convert to a Data Table (Insert, Table)
    And use selection criteria on the pulldown of the fields you want

  28. Rishi says:

    Hey Chandoo,

    Thanks for the tips on here – they live up to your tag word ‘awesome’! I was wondering if you knew a slick way to highlight the top x% of a range by value rather than number please? E.g we have a range of numbers that total 100 – we want to highlight the top 70% by value, i.e. all the largest numbers that together make up at least 70 (this could be made up by any number of values depending on the range). Does that make sense? The only way I’ve got to do this so far is by using LARGE to effectively sort the list into a seperate one, work out a cumulative total on each value, find the ‘threshold’ point at which that cumulative total crosses 70% and then conditionally format all the numbers that are above the threshold! But there must be an easier way?

  29. Muhammad Saleem says:

    I am working on a large array of data. I would like to list top 10 number in column 13 and list down with the values in column 1,2,3,4. in 10 seperate lines. What formula should I use.

  30. Hui... says:

    @Muhammad
    Add a column and use a Rank Formula
    arrange as appropriate on Column 13
    Then you can use and Index/Match combo to retrieve the data from Column (1 to 4) lookup the highest 10 values from the new Rank Column
    You can email me and I can show you on your data if you want.

  31. Daryl Hurst says:

    Chandoo, a fantastic tip on the ‘n’ largest values under conditional formatting but is there a way i can highlight values 6-10 in a different colour? Working with xls 2003 where there are only 3 allowed conditions. I can do them seperate and get values 6 & 7 – but after that i’m stumped!!! Cheers

  32. Ashish pandey says:

    Thats Superb it helped me a lot :) Thanks Chandoo

  33. ep says:

    I agree with the above chandoo explanation using conditional formatting. I found another example of using top and bottom values for dashboards without conditional formatting.

    http://excelprofessor.blogspot.com/2011/10/dashboard-top-5-values-bottom-5-values.html

  34. Matthew says:

    I have a set of data that I am looking to do Pareto Analysis on.
    The sheet has various colums but the key ones are the reason for failure and then the three data inputs which are marked between 1-10. Then there is a final section which adds up the three data entry points to give a risk factor.

    I then want to set a table to look for the top 5 (which i know how to do) but then get a fomula which looks at the risk factor matches it to the data table and then looks for the failure mode. Can this be done?

  35. Gordon says:

    suppose if i have a table with columns on months e.g jan, feb mar etc and under each column is data, is it possible to use conditional formatting and highlight all date related to current month.

  36. Siow says:

    Chandoo

    How about top 80%?

  37. Lindsey says:

    If I have 13 cells with different data, how do I highlight the top 5 green, bottom 4 red, and the middle 4 yellow??

  38. dockhem says:

    Hallo,
    Can you, please, tell me how can i high light excel rows and column of selected cell in excel 2010 and 2013.
    Thanks

Leave a Reply