Chart Axis Labels – Conditional Formatting
Monday, April 27, 2009 8:51- Contributed by Gunjan Aggarwal
We all like conditional formatting for the fact that data can be better projected that way. It makes data easy to read and conclude. It can be used in creating the Interactive dashboard, Routine reports, Adhoc reports and many more places where the Graphs play’s a vital role.
Imagine how conditional formatting can make your chart look attractive and interesting. In this article we will learn how to do that.
- Make a simple table with the required data
- In the subsequent Column insert the required formula, In our example we have set the standard to be 500 Visits, Accordingly the formula is written as:
=IF(Visits<500,0,NA()) - RED {Less than 500 Visits}
=IF(Visits>=500,0,NA()) - GREEN {More than or equal to 500 Visits}
- Now create column chart (A1 :D11)
- Click on Chart. Go to “layout” Tab. Click on drop down and select “Red” series
- Click on “Data Labels” -> more data label options
- Select “Category Name” and “below” option
- Change the data label font on the Axis to ‘Red’ or ‘Green’ Color as required
Finally you will arrive at the chart with conditionally formatted axis that adds up glamour to your reports and dashboards.
Note: The logic is applicable not only to Vertical Bar Graph but also to other chart types like Line graph, horizontal bar graph etc.,
If you need to know more about any of the dashboard or chart tricks, please leave your email id in a comment. And we will do our best to reply to your queries.
Avinash Kaushik says:
April 27th, 2009 at 7:29 pm
When looking at data the thing in everyone’s mind is: What am I supposed to do? This gets them a bit closer to the answer.
This is a very simple yet very effective tip Gunjan.
-Avinash.
Jon Peltier says:
May 6th, 2009 at 5:35 am
You left out a step or two.
You didn’t say what to do with the two extra columns of data. Your initial column chart would have three column series. To get the capability to label below the axis (to get the “below” option for the data labels) the red and green series must be converted to line chart type. Then the series have to be hidden by formatting without lines or markers.
To make it easy to change your cutoff (the value 500), place it into another cell, and link your formulas to this cell. Now you only need to change the value in the cell, not all of your formulas.
freemobile says:
June 23rd, 2009 at 4:19 pm
just seen this on twitter cheers for the info.
Aidan says:
July 17th, 2009 at 9:28 am
This is much easier to follow than Peltier’s rather obscure instructions!
John says:
August 7th, 2009 at 6:38 am
Your blog doesn’t seem to be complete without Peltier’s comments and direction to use the excel chart application.
Gunjan says:
September 23rd, 2009 at 8:25 am
@Peltier - Delete “0″ from columns C and D. Repeat steps 4 to 7 for Series “Green”. You will get chart as shown in last pic. Try it out. It works
@John - See my reply to Peltier. Let me know if you face any problem in creating this chart