Total spend chart

A while back I posted a blog on how to make a dollar graph.  While this is a fun chart, what if you could actually use a dollar as a backdrop?  Something like the picture below taken from coj.net

Can this be done in Excel?  Yes, definitely.  You can download the sample worksheet here:

Step 1: Set up your data table

For simplicity, I am going to use the same figures as the picture above, but of course you can use your own!  Incidentally, when I added the percentages up from the picture above, they actually totally 100.1%, so I reduced the first category by 0.1% to make 100%.

Step 2: Create a stacked bar chart

Highlight the data table and click on the Insert tab of the ribbon.  Then click on the “Bar” button in the “Charts” section and select the “100% Stacked Bar chart”, (3rd icon on the top).

Your chart should look like the one below, if not right click on the chart and choose “select data” to edit the chart.  If you have only 2 series and several categories, it’s because data has been plotted as rows instead of as columns.  Click on the Switch row / column button to correct it.  You can also remove any series that have been added by mistake.  Once you are done, close the menu box.

You can then do some simple formatting.  Delete the legend, and the grid lines.  Double click on one of the series to open up the formatting menu box, and then adjust the chart so that there is “No Gap”.  I also made each of the series have the same fill, with white borders. You don’t need to do the fill or the lines, by the way, but it may help you identify the “real” sections from the added ones in step 3.  Finally delete the two axes, so that all you have is a block of colour with white lines to separate the series’. Your chart should now look something like this:

Step 3: Add the picture fill.

First you need to find a high resolution picture of a dollar bill (or similar).  I used this one from wikipedia.

To make the separations easier to see, we are going to create some additional series.  I’ve added a gap of 3 in this picture, but you can use any number:

Once you’ve added these to the chart and put them in the right order, you’ll have a chart that looks like this: (Note, I’ve coloured the series that I want to show as part of the dollar bill in blue, the others in white).

Then, with the chart selected, in the “Format” section of the ribbon, Choose “Plot Area” from the drop down and then press Ctrl+1 to bring up the formatting menu box again.

Choose the “Fill” tab, and then “Picture or texture fill”.  Click on the “Insert from File” button and navigate to your dollar picture.  Click the close button to finish.

For the next step, change all of the blue filled series to no fill.  Your graph should now look like this:

It’s difficult to see the smaller sections on the left, so I’m going to change the fill of the chart and the separating series to black. (I’ve also removed any white borders).

Step 4 add the data labels

Click on the chart once more and on the “Layout” tab of the “Chart Tools” section of the ribbon, click on the “Data labels” button and then “More data label options”.  Choose to show the Series Name and the Value and put the label in the centre (we’re going to move them once they’ve been added). Click on the close button to add the labels.

Then click on one of the data labels.  Change its font colour to white, and position it above or below the dollar.  Repeat for the other labels that you need to show the sections of the dollar.  Delete any separation series data labels as these are not needed.  If necessary (for the smaller sections), add a white line to connect the label to the section of the dollar.  (Note, I’ve left the labels quite small, but you can adjust them as necessary).