Excel’s Conditional Formatting
In the work my team does at Microsoft, we often demonstrate the “big three” client programs (Word, Excel, and Outlook) to people who haven’t worked with them before. One feature that most people immediately understand and appreciate is conditional formatting. It’s easy to use, the results look great, and it can make complex data patterns easy to comprehend quickly.
To demonstrate how easy conditional formatting is to work with, I usually just enter some data into a blank worksheet. Consider a column of labels next to a column of numbers, like the example shown here.
This is a simple example, so it’s fairly easy to see some patterns in the data. The numbers build to a peak in April, fall to a low in July, then finish the year strong.
One way to demonstrate these trends is with a graph. And, of course, Excel 2007 has all sorts of cool graphing features.
This graph here, for example, was created from the sample data above in a couple of clicks. It shows the the trend of the numbers nicely. It also takes up a lot of space on the screen, and the precise numbers aren’t displayed.
Conditional formatting is an alternative to creating graphs. You can show similar types of trends, without using up precious screen real estate on a graph, and you can leave the numbers right where they are in the spreadsheet. Each cell is conditionally formatted, based on the value that’s in it. And you can decide the rules for formatting. Better yet, you can let Excel guess what rules might make your data easier to understand, and it usually guesses rules that work great.
To use conditional formatting, you just highlight some cells and then click the conditional formatting button on the ribbon. A dropdown menu shows the many options, and they’re mostly self-explanatory.
Some of the options include the cool new “live preview” concept, where hovering the mouse over a selection makes it temporarily take effect, so you see exactly what you’re asking for if you click on it. The Data Bars menu is selected in the example here, so hovering over any of the gallery images will cause the corresponding data bars to appear in the selected cells. It’s fast and intutitive, and once you use live preview for a while you start to notice places where it would be nice to have it in other programs. “What, I have to click on this just to see what it does? How old-fashioned!”
Here are some samples of various conditional formatting options (below). No explanation required, they’re that simple — you can tell what’s being communicated very intuitively, the numbers are all right there to see, and each of these examples was created with a few clicks of the mouse.
I predict that conditional formatting will become so popular by the end of 2007 that people will start to get tired of it being over-used, like when people used 10 different fonts in word-processing documents back in the 80s and 90s, just because they could. “Those spreadsheets with conditional formatting all over the place .. you know the type.” 
This entry was posted on Thursday, April 20th, 2006 at 12:06 am. You can subscribe to comments on this post through its RSS feed.
on April 20, 2006 at 7:25 am Tom wrote:
Ah, the next “verbing” of a noun (or in this case, an adjective) is upon us. I can hear it now: “Can you conditional this?”
You know, kind of like, “could you PowerPoint that for me and then PDF it? Then Outlook it to me, would you?”
Or “blogging,” for that matter, though I guess a made-up word can be a noun, verb, AND gerund. I will, however, kill the first person who calls me “bloggy” or says I do things “bloggishly.”
But back to the topic at hand, that’s very neat. In fact, when I watched the video on the MS site a few weeks ago it’s the first thing I told Lizzie (my wife) about when I went running in to gush about the new features. She spends a LOT of time in Excel (”Excelling” things, no doubt) and would love to have something like this.
‘Course, I’ll bet the inkjet cartridge manufacturers aren’t exactly outraged over it, either!
on April 20, 2006 at 5:04 pm Dave wrote:
Greetings Doug,
My current version of Excel does not have “Data Bars” but when I upgrade I will immediately use it to visually display if individual numbers is a series are greater than or less than their predecessor.
Just the ticket as I teach my daughter about numbers.
As an aside, I used PowerPoint and put each letter on a slide (using a very large font) and use it to teach her the alphabet. She loves the slide shows but after a few runs through the alphabet she gets board and starts mashing the keyboard on my laptop.
Take care.
on April 20, 2006 at 5:06 pm Dave wrote:
I meant “bored”, not “board”. Geez, maybe I need a grammar refresher course.
on April 20, 2006 at 6:17 pm Doug wrote:
That’s great, using Powerpoint to teach the alphabet. And you could put your own photos or other content on each page, to reinforce the concepts.
Using Data Bars to teach kids about numbers is a cool concept, too.
Sounds fun. Megan, can we get a kid? Maybe we could rent one for a while or something.