Create a dollar graph in Excel

One of my colleagues had a presentation to give this week regarding his program. He wanted a nice graphic / chart, to show his team which parts of the program were costing the most money. He created various charts himself, and then asked me to make one particular chart more interesting.  The original can be seen below (please note I’ve changed the labels and actual data to protect my colleague and his program):

It’s obvious in the case above that the “Jeans” series is costing the most money

My first suggestion was to reorder the categories, so that the most costly were at the bottom (In the same manner as a Pareto chart). Immediately, the information is visually easier to understand

However, the chart still doesn’t look so “sexy”. So my second suggestion was to create an overlay, so that the final chart looked like a multi-coloured dollar sign. You can download the excel file of the final result (shown below) here:

Much more interesting, yes? You’ll see that I also adjusted the number format so that the decimal places are no longer visible and the scale is simpler. It just looks cleaner.

OK, so “How is this done?” you ask…

Step 1: Set up the data

Create a data table in excel showing the data you want to visualise. Note that it is already in the correct order!  I’ve also added a Total Row, which we will need later!

Step 2: Create a stacked column chart

Then plot a standard stacked bar chart (as my colleague did originally). Select the table (excluding the total row and the header row) and click on the Insert tab of Excel. Then, in the chart section, click on Column > Stacked Column

If your chart doesn’t look similar to the second chart above, right click on it and click on Select data. Then click on the Switch Row / Column button. It should update to match the chart shown

Step 3: Create the background shape

For this to work, we need an inverted picture. I.E. the fill of the dollar sign is missing, the background is white. I used Inkscape to create my shape, but you can use any tool, including Excel if you wish. I’ve included the background shape in the downloadable Excel file for you to use directly. You can change its colour using Excel as you wish to have a different fill for the chart

To create a shape like this in Inkscape, first draw the basic shape. Since the dollar sign is already a character. I created a text field and typed in the $ sign. Then I clicked on Path > Object to Path to change the text to a drawing and changed the fill to white

Next create a rectangle behind the dollar drawing

Finally click on both shapes (using the Shift key to select multiple objects) and then click on Path > Difference to create the filler shape:

I left the background red as it is easier to see. Then I saved the Inkscape file as an *.emf file. This is very important if you want to be able to edit the file in Excel!

Step 4: Import the *.emf file into Excel and adjust

In the excel file, click on the Insert tab of the ribbon and then choose Picture. Find the *.emf file and press the insert button

Then right click on the image and choose “Edit Picture”. A warning box will appear saying this is an imported picture, not a group. Simply click on the “Yes” button

Then right click on the image and choose Group > Ungroup. Move the top layer away from the bottom transparent one. Delete the bottom layer. Adjust the top one so that there is no border and the fill is white (or any other colour / format you prefer)

Step 5: Adjust the graph

For this filler to work, we need to add the total series to the graph and plot it on the secondary axis. So, highlight the total row and press Ctrl+C. Then click anywhere on the graph, and press Ctrl+V. The graph will automatically update with the new series on the premier axis

Choose the new series and press Ctrl+1. In the “Series Options” tab of the message box that appears, change the axis to the secondary axis by clicking on the Secondary axis radio check button. Then press the Close button. The graph should update

Adjust the primary and secondary vertical axes so that they have exactly the same scale. You should now only be able to see the total series as it will cover the others on the primary axis.  You should now be able to delete the secondary axis

Click on the dollar shape and press Ctrl+C. Then click on the total series on the graph and press Ctrl+V. Your graph should appear as below:

You can now adjust the graph as you wish!  What do you think?  I used the exact same technique to create this graph:

Instead of clicking on the entire series and pasting.  Click twice where you want the first drawing to be (where the first column of data would appear).  Then repeat for the second column and picture.  I’ve included this chart and the background filler in the uploaded excel file as well.  It can be downloaded here if you wish to use it.  I hope you find it as interesting as I do.  This technique can be used to make your charts look very visual.

Enjoy!

Alesandra Blakeston