Jump to content


Top 10 in a range

conditional formatting sorting filter

  • Please log in to reply
2 replies to this topic

#1 Gardener



  • Members
  • Pip
  • 1 posts

Posted 23 September 2016 - 01:04 AM

I have a range of data, which includes rows and columns. Want to show top 10 and bottom 10, by cell or text color.

#2 Jenny M

Jenny M

    Advanced Member

  • Members
  • PipPipPip
  • 32 posts

Posted 23 September 2016 - 01:10 PM

When I do this, I highlight the range, go to Conditional Formatting in the Styles group on the Home tab, choose Top/Bottom Rules, choose a format for the top 10, then do the same thing for the bottom.


There may be a better way; I'll look forward to seeing if someone has a better answer.


Best wishes, Gardener!

#3 Jonathan


    Forums Administrator

  • Administrators
  • 887 posts

Posted 23 September 2016 - 02:39 PM

Hi Gardener,


The simplest option might be to simply use Excel's sorting options to sort your data in the desired order and then look at the top 10 entries that appear.


You can see how to sort your data in the video lesson: Expert Skills Lesson 1-14 Sort a range or table by rows.


As Jenny said, you could also use conditional formatting to color the top and bottom entries. You can see how to do this in the video lesson: Essential Skills Lesson 4-16 Manage multiple conditional formats using the Rules Manager.


Finally, you can also use Excel's filtering options to filter your data to just show the top or bottom entries. You can see an example of this in the video tutorial: Expert Skills Lesson 1-4 Apply a top 10 and custom filter to a range.


I hope one of these options is what you were looking for, but please feel free to reply if you need any more help with this.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users