Recently, a healthcare company employee contacted me about a chart he wanted to create. The company that he works for is trying to save trees by reducing their usage of paper in copiers and printers. So he wanted to create a goal chart similar to the beer mug chart but that highlighted a tree as the goal like you see here:
When you reach 100% of the goal, the entire tree is visible.
You can check out that post here: Beer Mug Goal Chart
The Breakdown
1) Create Chart Area and Formulas
2) Insert Stacked Column Chart
3) Copy and Paste Custom Fill Image
4) Delete Gridlines
5) Switch Row/Column
6) Move Goal Remaining and Goal Reached to Secondary Axis
7) Adjust Vertical Axes
8) Set Chart Series to No Gap
9) Set Chart Series Fill and Transparency
10) Delete Legend
Step-by-Step
1) Create Chart Area and Formulas
This chart will have 3 data series. One that will be used for the tree image, one that will be used to mask the tree (Goal Remaining % series) and one that will be used to show the tree (Goal Reached % series).
We will put our series in the range of A1:B4 as you see here:
| A | B | |
|---|---|---|
| 1 | Paper Goal | |
| 2 | 100% Tree | 100% |
| 3 | Goal Reached % | 60% |
| 4 | Goal Remaining % | 40% |
Worksheet Formulas
|
Cell B2 will only have a static value of 100% as it represents the value for our tree image.
Cell B3 is a user entered value. It will be a value between 0% and 100%. This will be used to mask or hide the upper portion of the tree image for the amount below our goal.
Cell B4 has our formula of =IF(B2-B3>=0,B2-B3,0%). This formula limits the value to 0 in case a user goes above 100%. This will be used for the series that will show us the part of the tree that represents our current level toward the overall goal.
2) Insert Stacked Column Chart
Now that we have our formulas for our chart series, we can create the chart. Do that by highlighting cells A1:B4 and then select the Insert Ribbon and choose the 2-D Stacked Clustered chart type from the Columns button.
Your chart will now look like this:
3) Copy and Paste Custom Fill Image
This is a good time to copy and paste the tree image into the chart. To do this, you first need to insert an image of a tree from the Insert Ribbon or copy/paste an image from a clip art. You should consider getting an image that has a transparent or white background if possible. Then Crop the image from the Picture Tools>Format Ribbon to make sure the tree image you have is close to the bottom so that it matches up with the bottom of the chart axis. This is explained further in the video, so you should check it out.
After you have your image (in our case a tree) in the worksheet, copy it by selecting it and pressing CTRL+C. Then select your chart and then select the 100% Tree series column on the left one time and then do it a second time so that the data point (not the entire series) is selected as you see here:
Then press CTRL+V to paste the image into that data point.
Your chart will now look like this:
4) Delete Gridlines
If you don’t want the horizontal gridlines, now would be a good time to get rid of them. Select the chart, then select the horizontal gridlines and press your delete key.
Your chart will now look like this:
5) Switch Row/Column
Excel makes a choice on how to show your data based on the data. In our case, it guessed wrong so we need to switch the rows/columns of our chart. To fix it, select your chart, then go to the Design Ribbon and choose the Switch Row/Column button.
You can learn more about this from this post:Why Does Excel Switch Rows/Columns in My Chart?
Your chart will now look like this:
6) Move Goal Remaining and Goal Reached to Secondary Axis
For this trick to work, we need to cover over the tree series with the other two series. And for that to happen, we need to move the Goal Remaining % and Goal Reached % chart series to the secondary axis. To do this, select either one of those series and press CTRL+1 and them move it to the secondary axis from the Format Series dialog box options.
To repeat it for the other series, it may be easier to increase the gap width of the series just moved to the 2nd axis so that you can see the remaining series that you need move to the secondary axis.
Make sure you leave the 100% Tree data series (the one with the tree image) on the Primary Axis.
Your chart should look like this when you are done:
7) Adjust Vertical Axes
To make our chart look better, we should adjust the vertical axes. First, lets delete the secondary vertical axis. Do that by selecting the chart, then select the secondary vertical axis. Next press the delete key.
Now we need to set the minimum and the maximum values for the primary vertical axis. To do this, double click on the primary vertical axis. Then adjust the minimum value to 0 (zero) and the maximum to 100 as you see here:
Your chart should look like this:
8) Set Chart Series to No Gap
The tree image I chose was rather wide, so it is best if the Chart Series has No Gap. To do this, select the Goal Reached % chart column and press CRTL+1 and increase the gap so that you can see the 100% Tree chart series (as we did in a previous step). After you can see it, select the 100% Tree series and press CTRL+1 to bring up the Format Data Series dialog box.
Then repeat this step for the other 2 remaining data series so that all of the data series have No Gap
Your chart should now look like this:
9) Set Chart Series Fill and Transparency
The final major step is to change the Series Fill settings so that we can see the tree on the bottom as the goal is reached and see a shadow of the tree to see how close we are to the 100% goal. To do this, first select the Goal Reached % series on the bottom. Then press CTRL+1 to bring up the Format Data Series Dialog box. Then go to the Fill options and select No Fill.
Next, select the other series (Goal Remaining %) and change the Fill settings to Solid Fill and chose a medium grey and change the transparency percentage to 20% as you see here:
10) Delete Legend
Finally, as we have a few series that don’t make sense you may want to delete the 100% Tree legend entry or just delete the entire legend. To do this, select the chart, then select the Legend and press your delete key.
Your final chart should look like this:
Video Demonstration
Free File Download
Sample-Excel-Single-Image-Goal-Chart.xlsx
Let me know how you could use this technique in your Excel Dashboards in the comments below.
Steve=True
