Pimp your chart in Excel
Before you read on, this is not a heavy post about the merits / demerits of data visualisation and how to extract the most meaning from data – it’s about sexing up your graphs in time for open days, transition evenings and for the annual “create a good message" fest that is the preparation of the prospectus.
So, in the spirit of shameless marketing and eye candy, we are going to create this chart:
This chart has the following “features":
- The bars are filled with an image related to the content of the chart
- There are no axes – instead the category data (2004, 2005 etc is added inside the bars as a label) and the y-axis being replaced with a data label inside each bar.
Whilst not terribly complex, the chart does need some prior planning.
Create data in Excel:
- It’s important that you don’t label the Year column – leave it blank,
- The “Difference" column is the difference from 100 for each of the data points. So 100-44 = 56 etc.
- The Dummy value is a repeat of the actual data (more on that later).
Create a “stacked bar chart":
Highlight everything in the table above and Insert a stacked bar chart (NOT a 100% stacked). Use all the defaults. You will end up with:
Remove the LEGEND and CHART TITLE
Insert Image into Chart Plot Area:
Not all images work – and some just make everything confusing. Choose carefully.
- Select the 2 A*- C Series and reduce the Gap Width to 0% (this will push all the bars together
- Select the 2 A*- C Series and set the series fill to “No Fill" (this will let the image show through)
- Select the Difference Series and set the series fill to “Solid" and 50% Grey
- Select the Dummy Series and change the Series Options to plot on a “Secondary Axis"
- Select the Dummy Series and set the series fill to “No Fill"
- Select the Primary Axis and set the Maximum to 100. Delete the axis
- Select the Secondary Axis and set the Maximum to 100. Delete the axis
- Select the Category Axis (the dates) and delete it
You should have something that looks like this:
- Select the Dummy Series and add Data Labels. Set the Data Label Position to “Inside Edge" (This will put the data label inside the bars at the TOP of the bar
- Select the 2 A*- C Series and add Data Labels. Set the Label Options to Label Contains Category Name and Positon to Inside Base (This will put the category name at the bottom of the bar)
- Add a Solid 3pt border to the 2 A* – C Series
- Remove the “Major Gridlines" on the Y-Axis
- Add a Solid 2pt border to the Plot Area of the Chart
- Adjust the size and coloration of the fonts for the data labels as needed
- Add chart Title (if needed)
You will end up with: