Tech Tips Home
The Best Tech Tips And Daily Deals
Newsletter On The Internet!

WorldStart Tech Tip And Store Search
Email: Password: Login Remember Me

Like what you see here? Subscribe to the Tech Tips newsletter!   Email: Subscribe

MS Excel 2007: Top and Bottom Rules

Tuesday, July 20th, 2010 by | Filed Under: MS Excel

To extend our previous discussion about Data Bars to show how a piece of data in a list stacks up against the rest I thought that we might take a look at the conditional formatting options within the Top/Bottom rules.

Basically, they’re rules you can use to visually point out the top (top values, top percent of items, above average, etc.) and/or the bottom items in a data set.

A common use for me, as a teacher, is to take my student’s grades and then use the conditional formatting that points out all values below average.

Maybe you’ve got sales figures and need to know who were the top ten sellers.

Whatever you’re looking to do concerning the top or bottom portion of a data set is probably covered here:


Home tab of the Ribbon, Conditional Formatting button, Top/Bottom Rules choice.

As you can see you’ve got three choices for the top data (by number of items, by percent of items and above average) and the same three choices for the bottom portion of the data.

To use them start with your data set selected navigate to the Top/Bottom Rules on the Home tab.

Then choose a rule – for the sake of my example I’ll choose Bottom 10 Items.

The following dialog box will open:


The first thing to note is that on the left you can change the number of items to which the formatting will actually be applied.

The second thing to note is that on the right you have a drop-down list of formatting choices.


These options allow for multiple rules to be applied to the same set of data.

By choosing one format for the bottom 10 items and another for the top 10 items you’ll have both ends of the spectrum clearly marked.

You’ll find that you can adjust the top or bottom percent of items to format. (Maybe you need the top 20% – it works just like the item count – you can change the value once the dialog box opens.)

And, as you may expect, you can change the cell formatting choice for any of these rules so you really need to be careful or you could end up with way too much formatting on the cells… to the point where it no longer makes any sense at all.

So… while we’re on that thought… how do we clear the conditional formatting out of these cells?

It wouldn’t be a big surprise to find that you’ve formatted either too much or have changed your mind and need to clear it out of there.

No problem.

At the end of the Conditional Formatting button‘s list you’ll find an entry to Clear Rules.


Choose from where you want the rules cleared away. (If you choose Selected Cells then you’ll need them selected prior to starting this process.)

Why use this in place of some other way of removing cell formatting?

Well… by using this to clear the Conditional Formatting Rules out it will leave the other formatting applied to the cell and it’s contents. It only clears out changes made by the rules themselves.

And there you have it – just one more of the many ways Excel 2007 gives you to easily do some basic analysis of your data.


Leave a Reply

Like these tips? Get them for FREE in your email!

WorldStart's Tech Tips Newsletter

  • Tech Tips Daily - Become a tech pro! Get the very best tech and computer help sent directly to your email every weekday!

  • Tech Tips Weekly - If you don't want our Tech Tips newsletter every day, then sign up for this weekly newsletter to get the best information of the week. Sent on Fridays.

Other Newsletters

  • WorldStart's Daily Deals - Every week, we send out great deals in our Daily Deals newsletter. Many of these deals are exclusively for our Daily Deals newsletter subscribers and can't be found with our regular specials.

  • Just For Grins - Each issue includes a couple clean jokes, some funny quotes, and a hilarious reader's story. Newsletter is sent five days a week.

Enter Email Address:


Your e-mail address is safe with us!
We only use it to send you the newsletters you request. It is NEVER disclosed to a third party for any reason, ever! Plus, if you decided you don't like our newsletters (don't worry, you'll love them), unsubscribing is fast and easy.

Free Newsletter Signup

Tech Tips Daily

Become a tech pro! Get the very best tech and computer help sent directly to your email every weekday!

Tech Tips Weekly

The week's best in tech and computer help. Get your issue sent to your email every Friday!

WorldStart's Daily Deals

The very best deals on the Internet! Get a new set of incredible sales every day of the week!

Just For Grins

Clean jokes, funny quotes, and hilarious comics. Sent 5 times a week straight to your email.


Love Worldstart? Refer A Friend!

WorldStart's Premium Membership

Tip Archive