How-to Make an Excel Single Image Goal Chart

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:

Single Image Tree Goal Chart
Single Image Tree Goal Chart

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 Formula
B4 =IF(B2-B3>=0,B2-B3,0%)

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.

Insert Stacked Column Chart for Single Image Tree Goal Graph
Insert Stacked Column Chart for Single Image Tree Goal Graph

Your chart will now look like this:

Tree Goal Chart Image 1
Tree Goal Chart Image 1

 

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.

Tree Image in Worksheet
Tree Image in Worksheet

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:

Tree Goal Chart Image Select 100 percent column
Tree Goal Chart Image Select 100 percent column

Then press CTRL+V to paste the image into that data point.

Your chart will now look like this:

Tree Goal Chart Image inserted tree picture
Tree Goal Chart Image inserted tree picture

 

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:

Tree Goal Chart Image No Gridlines
Tree Goal Chart Image No Gridlines

 

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.

Switch Row Columns 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:

Tree Goal Chart Image after Switch Row Column
Tree Goal Chart Image after Switch Row Column

 

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.

Tree Goal Chart Image after moving series to 2nd axis
Tree Goal Chart Image after moving series to 2nd 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:

Tree Goal Chart Image both goal series to 2nd axis
Tree Goal Chart Image both goal series to 2nd axis

 

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:

Set Vertical Axis Minimum to zero and Maximum to 100
Set Vertical Axis Minimum to zero and Maximum to 100

Your chart should look like this:

Tree Goal Chart Image after adjusting vertical axes
Tree Goal Chart Image after adjusting vertical axes

 

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

Tree Goal Chart Image No Gap
Tree Goal Chart Image No Gap

Your chart should now look like this:

Tree Goal Chart Image No Gap 2
Tree Goal Chart Image No Gap 2

 

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.

Tree Goal Chart Image after No Fill Series
Tree Goal Chart Image after No Fill Series

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:

Tree Goal Chart Image after Transparent Fill Series
Tree Goal Chart Image after Transparent Fill Series

 

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:

Tree Goal Chart Image Final
Tree Goal Chart Image Final

 

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