Today is the first anniversary of Excel Conditional Formatting post (Don’t worry, I am not going to make anniversary posts for all the 150 odd excel articles here). This is the most popular post on PHD. The post has 100 comments and bookmarked on delicious more than 700 times. It is truly a rock star post on PHD.
To celebrate the 1 year of teaching conditional formatting to you all, we have a series of posts, the first of which is “What is excel conditional formatting & How to use it?”
What is excel conditional formatting ?
Conditional formatting is your way of telling excel to format all the cells that meet a criteria in a certain way. For eg. you can use conditional formatting to change the font color of all cells with negative values or change background color of cells with duplicate values.
Why use conditional formatting?
Of course, you can manually change the formats of cells that meet a criteria. But this a cumbersome and repetitive process. Especially if you have large set of values or your values change often. That is why we use conditional formatting. To automatically change formatting when a cell meets certain criteria.
Few Examples of Conditional Formatting
Here are 3 examples of conditional formatting.
So How do I Apply Conditional Formatting?
This is very simple. First select the cells you want to format conditionally. Click on menu > format > conditional formatting or the big conditional formatting button in Excel 2007.
(we have used excel 2003 in this tutorial, but conditional formatting is similar in excel 2007 with lots of additional features)
You will see a dialog like this:
There are 2 types of conditions:
- Cell value based conditions: These conditions are tested on the cell value itself. So if you select a bunch of cells, and mention the condition as between 15 and 25, all the cells with values between 15 and 25 are formatted as you specify.
- Formula based conditions: Sometimes you need more flexibility than a few simple conditions. That is when formulas come handy. Conditional Formatting Formulas are slightly complicated and can be difficult to learn or use if you are new to excel. But they are very useful and intuitive and if you use them once you get a hang of it.
What are the limitations of Conditional Formatting?
In earlier versions of Excel you can only define max. of 3 conditions. This is no longer true if you are using Excel 2007 (read our review of excel 2007)
However, you can overcome the conditional formatting limitation using VBA macros (again, if you are new to excel, you may want to wait few weeks before plunging in to VBA)
Also, you can only use conditional formatting with cells and not with other objects like charts.
Ok, Enough Theory, Time for your First Conditional Formatting
Go ahead, open a new workbook and try few conditional formats yourself. See how easy and intuitive it is. Use it in your day to day work and impress your colleagues. Learn 5 impressive tricks about conditional formatting.
If you have trouble getting started, download the conditional formatting examples workbook.
Tell us how YOU use Conditional Formatting
Share with us how you use CF in your work. I am sucker for conditional formatting and use it wherever I can. What about you?
This post is part of our Spreadcheats series, a 30 day online excel training program for office goers and spreadsheet users. Join today.
18 Responses to “Best Charts to Compare Actual Values with Targets – What is your take?”
Great post. I can't vote, though, because the answer I want to put down is "it depends". As with all visualisations, you've got to take into account your audience, your purpose, technical skills, where it will be viewed, etc.
I'm with Andy: It depends. Some I would use, some I might use, some I won't touch with a barge pole.
Naturally I have comments 🙂
The dial gauge, though familiar, is less easy to read than a linear type of chart (thermometer or bullet). It's really no better than the traffic lights, because all it can really tell you is which category the point falls in: red, yellow, or green.
By the same token, pie charts are so familiar, people don't know they can't read them. Remember how long it takes kids to learn to read an analog clock?
Bullet charts don't show trends.
With any of the charts that have a filled component and a marker or ine component, it makes more sense to use the filled component (area/ column) for target, and the lines or markers for actual.
[...] Best Charts to Compare Actual values with Targets (or Budgets … [...]
I voted for #6 even though I agree with the other comments that it depends.
The majority of the votes are for the #2, thermometer chart. I still have yet to understand what happens when you are above plan/goal, which was brought up in yesterday's post.
Also, I agree with Jon in that it would be better to flip the series and make the filled part the target or goal and the line or marker the actual.
I am also a fan of using text when appropriate if the data is among other metrics in a type of dashboard. Calling it out by saying actual and % achievement is a good option.
Another "it depends" vote. Are you just looking at one or are you comparing a number of targets with actuals? You didn't include a text box. The problem with sentences is that they can get lost in a page of gray text. A text box can call attention to the numbers and line them up effectively.
I'm with Jon: "Some I would use, some I might use, some I won’t touch with a barge pole" and I'm surprised that some of your readers voted for the last group.
Jon says:
With any of the charts that have a filled component and a marker or line component, it makes more sense to use the filled component (area/ column) for target, and the lines or markers for actual.
Why does this make more sense? I like 6 the way it is, although I would use a heavy dash for the plan/target marker.
"It depends" is also my take. What I usually try to drill into my clients dashboard design is the fu ndamental difference between spot results (am I on target for this month) and long term trends.. I always try to create 3 different set of graphs to represent real perormance:
- spot results vs objectives
- cumulative results vs objectives
- long-term trend (moving average) mostly) to see where we're going
[...] Best Charts to Compare Actual Values with Targets – What is your take? (tags: excel charts) [...]
[...] Related: Charting Principles, How to compare actual values with budgets [...]
[...] Excel Charting Alternatives to compare values [...]
Jon says:
With any of the charts that have a filled component and a marker or line component, it makes more sense to use the filled component (area/ column) for target, and the lines or markers for actual.
Why does this make more sense? I like 6 the way it is, although I would use a heavy dash for the plan/target marker.
I totally agree, Bob. I would normally favour a line for the target and a column for the actual, you can see quite easily then which columns break through the line, then.
[...] best charts to compare actual values with targets — den Status mal anders zeigen, z. B. als Tacho [...]
Thermometer charts: "Not appropriate when actual values exceed targets" - this is easily solved by making the "mercury" portion a different color from the border, then you can clearly see where the expected range ends and the actual values keep going.
People seem to knock gauges quite a bit in dashboarding, but trying to show comparison of realtime data between operating sites and targets for each site can easily be done with a bank of gauges that have the optimal operating points at 12 o'clock.
The human eye is great at pattern stripping, and any deviation of a gauge from the expected 12 position will quickly register with an operator and attract his attention. Using a colour background, or meter edge, will also indicate the sensitivity of a particular site.
[…] Best charts to compare actual with target values […]
[…] Best charts to compare actual with target values […]
[…] work laptop I have a favorites folder just dedicated to Excel charts. Its got things like “Best Charts to Compare Actuals vs Targets” and “Best charts to show progress“. I love me some charts […]
I am wondering how will the plotting work, for some of the targets which may have been achieved before time. E.g. for the month of Jul the target was 226 and the actual was 219. So the chart will show a deficit in meeting the target by 7 points but what if this 7 may have been completed earlier in month of June. So ideally it not a deficit.