20 Excel charts for your dashboards

Effectively displaying data is always a challenge. Making sure that the right data is being displayed is one thing, but making sure that the way it is displayed visually matches the message you want to send is quite another. I’m not going to go into what charts you should use and when, instead I am simply going to give you 20 charts to choose from. You can download the Excel file here.  Visually appealing, these charts should be great to use in dashboards or even infographics.

Enjoy

+Alesandra Blakeston

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

cityexpensegfdollar

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%.

data tableStep 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).

insert chart

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.

chart 1

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:

chart 2

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.

Usdollar100front

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:

data table 2

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).

chart 4

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.

plot area

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.

formatting menu box

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

dollar chart 1

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).

dollar chart 2

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).

And that’s it.  You can download the finished chart here.

dollar chart 3

Enjoy!

+Alesandra Blakeston

Creating a score meter in Excel

As most people who know me will attest, I am a big fan of using gauge and bullet charts and / or altering the standard excel charts to make them more visually appealing.  So when I was asked by a colleague if I could create a gauge style graph, with an arrow as an indicator of the result (kind of like a linear speedometer), my exact words were, challenge accepted.

To make sure I fully understood what they wanted, I asked them to send me a picture.  This is what I received:

example

After further discussions, we realised that she wanted a percentage scale going from 1 – 100%.  The blue arrow would move based on the indicator for that month.  There are actually several ways that this chart could be made.  I chose in this instance to make a bar chart.  It could also be done with a column chart, though!  You can download my chart and worksheet here.

Step 1: Set up the data

In this instance, my colleague had only 1 data point she wanted to display, a YTD indicator.

indicatorHowever, to set up the coloured scale behind the indicator arrow, we would need more data to be plotted.

For this reason, I set up two more columns:

extra columnsThe first will be used to created the coloured scale.  In this case, the scale is even, but it doesn’t need to be, it is dependent on the visual aspect the user wishes to show.

The second column is a set of two calculations to create the width of the arrow.

  • E2 = B2 – 0.025 (this will find the value of the indicator minus half of the width of the arrow)
  • E3 = 0.05 (the width of the arrow)

Step 2: Create the basic stacked column graph

First highlight the data in column D, then click on Insert > Bar > Stacked Bar

stacked bar

The chart will probably look something like the one below:

1st chart

We need to adjust the way the chart is displaying the data.  Right-click on the graph and then choose “Select Data”.  Click on the “Switch Row / Column” button and press the OK button

switch

You should now have 5 series as shown in the chart below.  Adjust the colours of the series and the chart itself as you wish.  I added a grey gradient to the chart fill, removed the X axis, horizontal lines and legend and then adjusted each series so that it matched the traffic light idea with a white border.

second chart                                3rd chart

Step 3: Add the indicator arrow

For this we need to add 2 more series.  First click on cell E2 and press Ctrl+C.  Then click on the graph and press Ctrl+V.  The graph will automatically adjust to show the new data.

new data

Click on the new series, and press Ctrl+1 to bring up the formatting menu box.

Choose to plot the series on the secondary axis in the “Series Options” tab and give it a gap width of “0”

secondary axisBefore pressing the close button, go to the fill tab and choose no fill.  Then you can close the formatting menu box.  The graph should now look like the one below:

4th chart

Repeat the process, this time selecting cell E3 and copying and pasting it into the chart.  Your graph should look something like this now:

5th chart

The next step is to adjust both of the vertical axes so that the maximum scale is 100% for both.  To do this double click an axis.  In the format axis menu box ensure that the Fixed Maximum = “1”

axes

Repeat for the other axis.

6th chart

You can now delete the top axis.

We now need to add the arrow.  To do this first we insert the arrow shape.  Click on Insert > Shapes > Down Arrow.  Then draw the arrow on the page and adjust it to have the shape you want.  You can see mine in the picture below.  I’ve given it a black outline and an 80% transparent white fill.

down arrow

Then click on your arrow and press Ctrl+C.  Click on your seventh series (which you want to replace with the arrow), and press Ctrl+V.  And voila, the chart is complete:

finished chart

Obviously this is a large chart for just one data point, but you can add other points to it as well…

alternative

Have fun!

+Alesandra Blakeston

Creating a picture axis

If like me, you like to create interesting graphs in Excel, you may have wondered at one point or another whether it is possible to have pictures on your axes instead of text.  In actual fact it is really very easy to do this.  You just need to install some dingbat fonts (e.g. wingdings and wingdings 2) on your machine.  Then simply adjust the font of the axes to match the font (and icons) you want to use.

For example, in the graph below I have used a font called “Social font face” which is free to use for personal use.

social media graph

Personally, I think the logos of the different types of social media are a lot easier to read and more recognisable than text.

Another example using a font called “Face“:

faceI’ve enclosed the two excel spreadsheets here for you to download and use: social media & face.

What do you think?  Is it useful?  I think if you are wanting to grab attention and or present data in an interesting way, it can be great, however, it can also be overdone and look unprofessional if you choose the wrong icons, so be careful.

Enjoy

+Alesandra Blakeston

 

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):

original chart

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

ordered column chart

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:

dollar chart

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!

table

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

insert chart

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

inkscape

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

dollar symbol

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)

image in excel

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:

dollar chart

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

people chart

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

Excel milestone timeline – version 2!!!

Some of you may remember that I created a post a while back on how to make a milestone timeline in Excel.  It has proven to be quite popular to the extent that I’ve been asked for modifications!

You will hopefully remember that the original looked like this (you can also click on the image to go back to the original post):

chart 8

The modification I was asked for was to make the timeline into a series of bumps and / or steps!  Well, challenge accepted!  Here is the finished result:

Excel-timeline 2

You can find the finished Excel version here: excel-timeline 2

Alesandra Blakeston

Some really creative XY Scatter charts in Excel

For those of you who are really mathematical, and for those of you like myself who like to see really creative graphs and charts, you might get a kick out of this post by Frankens team:

Creative and advanced chart design in Excel

There are some really great examples on here that I think you will like!  I did anyway.  For each example, you can download the Excel sheet and take a look on how it is done!

My favourites?

barre1 Gomitoli percentage racetrack Spring1

 

Hope you find them as inspirational as I did!  Their final point?

Epilogue

Please do not regard these charts as a best practice or recommendation. These are just ideas – as in haute couture. You can use if it gives a little “salt” to your work, but always respect the data and your target audience – never forget the basic rules: data derives chart choice.

As it was told by the famous French fashion designer, Coco Chanel:
“Simplicity is the keynote of all true elegance.”

Alesandra Blakeston

Create a column chart using a background picture

Once again, I am posting a challenge I was sent while on holiday last week.  This one was sent from a colleague, who like myself is a bug fan of www.chandoo.org.  Apparently they had seen this on the chandoo site and wondered if I could do it, (without cheating!)…

Here’s the picture I was sent:

background photo chart

It’s a lovely photo of Boston, being used as background fill for the chart series.  Since I had just come back from there, it seemed appropriate and a fun challenge, so off I went!  I have to admit, my first attempt was rather feeble, but then I thought about it some more and managed to work it out.  See what you think.  You can download the example chart here.

First I set up some dummy data and created a normal column chart:

dummy data

Then I added a background fill to the chart by clicking on the white background and pressing Ctrl+1.  Then I chose picture fill, and added my picture as the chart background.

chart background

Imagine if you will a stacked bar chart, the current blue series changed to a transparent fill (so you can see the background) and a series above the blue one (using a second set of data) with  a white fill to mask the background picture.

First then, I needed to calculate the size of the top series.  My chart currently has a maximum value of 200 on the Y axis, so I needed to calculate the difference between the current value and 200 for each row of the table.  So in cell C2, I wrote the calculation “= 200 – B2.”  Then I copied and pasted this to fill each row of the table

second series

Then I needed to change the chart type to a stacked column chart.

stacked column

Next I needed to add the white series to the chart and adjust the Y axis to a fixed maximum of 200.  By default, Excel made it red, but we will change this later.

2 series

Almost there!  The rest really is just formatting.  First I clicked on series 1 (blue) and pressed Ctrl+1 again.  I adjusted the gap width to 0.  Then I added a white 6pt border to the series.  You’ll notice you can no longer see the background photo (at the moment) in the picture below.   Eventually the white border will create the illusion of space between the columns.

white border

Then I changed the fill to “No fill”.

no fill

Finally, I changed the fill of the red series to white.  This then, became my initial draft…

initial draft

However, I wasn’t quite done!  I added data labels, got rid of the axes and here’s the finished product below.  What do you think?

final draft

You can of course download the sample chart and data here.  Beware though, adding pictures that are lively could detract from your data!  You can see in the case above that sometimes the data is difficult to read!

Enjoy!

Alesandra Blakeston

How to make a graphic equaliser chart in Excel

This seems to have been a week of Excel challenges for me.  First there was the Metro UI style Excel dashboard challenge from a close friend of mine.  Then yesterday I was asked if I could make a chart that looked like a graphic equaliser to represent some sound readings in a very visual way.  Challenge accepted!

We all know what a graphic equaliser looks like:

images

My first thought was this should be relatively simple.  All I need is to draw a bar like shape (small thin rectangle) on to the page.  Then create a normal bar chart.  Then copy the bar, press Ctrl+1 and use the contents of the clipboard as the fill, stacked and scaled with 1 bar per unit of data.  Much like what I did to create the arrow chart earlier in the year.

What I actually got was not so pretty:

first attempt

Without an outline, each bar merged into the next, making the graph look exactly like a normal bar chart.  So I put my thinking cap back on and realised that I needed two bars, a white one behind the coloured one to space out the lines.  This time was better, but still quite boring:

second attempt

Then I started formatting.  I removed the Y axis and increased the size of the X axis font.  That’s when I realised that it was completely impossible to read the values without the Y axis.  So I decided to add coloured graduations, by creating a group of 5 coloured bars (four grey bars and 1 blue bar to mark the 5th graduation), again with a white background.  Of course then I had to stack and scale the picture fill by 5 units for the five coloured bars:

third attempt

Now we were starting to cook with gas.  I went back to the internet at this stage and started looking for some great pictures of a graphic equaliser.  I quickly realised that I needed a black background, and perhaps a rainbow gradient with my coloured bars, going from green to red.  This meant having 70 coloured bars with a black background, and again changing the fill to stack and scale to 70 units – a lot of formatting, copying and pasting!  Also, since most of the pictures I saw had a reflection of some sort, I needed to use the camera tool (see this post for more details) to get a realistic image, which will of course update automatically:

final attempt

Just in case, I also created a bar chart version as well, with different values to see how it would look.   What do you think? The full excel file can be found here.  Enjoy!

Alesandra Blakeston