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.
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.