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

Broken column and bar charts

It’s been some time since I’ve written a post on Excel graphs and charts, mainly due to a lack of inspiration on my part.  I have to be visually inspired with my charts and the ones that I usually use are all on my blog.  That being said, since hosting a webinar on Excel for the women’s network of the company I work for, I have been inundated with requests and as a result I am inspired again!

One of the requests boiled down to a mismatch of data needing to be displayed in the same chart.  I.E. one of the series displayed in the chart would be very large compared to the others.  Here is my sample data table:

data tableAs you can see most of the data is below 1,000 units.  The last series, (series 4) is just above 35,000 units.  If you plot that data as a normal column chart you will get something like this:

normal columnIt’s almost impossible to read the values for Series 1-3, 4 & 5.  What we need is a broken column chart.  By using a broken column, you can see both the detail at the bottom of the scale and at the top.  How is it done?  You can download the worksheet I used here.

Step 1 – work out what you want the chart to look like

The first thing we need to do is work out where the different groups of data lie.  We have one group of data that ranges from 100 to 900.  Series 4 on the other hand is just over 35,000.  So how could we display that data to get clarity?  We need to have a lot of room on the graph between 0 and 1,000 to be able to view the different heights of the columns.

Looking at the data, if we had a chart that had 0, 250, 500, 750, 100, a break, then 35000, 35250 and 35500 as the scale we would be able to see most of the detail clearly.  Something like the graph below:

broken column

Step 2 – organise the data

To do this, we need to do is make some calculations to work out how to draw the chart.  We want the break to occur at 1,000 units and restart at 35,000 units.  The minimum is zero units and the maximum 35,500 units.

scale break

Once this data is entered into excel, we can use the Name Manager to assign these values to formulae.  In my worksheet, the value for the break is entered into cell B15.  By clicking on this cell, and then clicking on the Name box, we can enter a name.  I’ve named this value SPLIT_BREAK_COL.

name boxI’ve entered similar names for the other values: SPLIT_MIN_COL, SPLIT RESTART_COL, and SPLIT_MAX_COL

Then we can do some calculations.  In your worksheet create three new columns: “Before”, “Break”, and “After”.  Assuming that your first row of data is in Row three, the “Before” column is column C, and that the “2013” column (the original data) is column B, your calculation in cell C3 should look like this:

=IF(B3>SPLIT_BREAK_COL,SPLIT_BREAK_COL,B3)

This means if the value in cell B3 is greater than the value of the break, then the cell should display the value of the break, if not, the original value.  If you copy the formula down the rows, every cell should show the original value except for series 4 which should read 1,000.

In the Break column, the calculation in cell D3 should read something like this:

=IF(B3>SPLIT_BREAK_COL,50,NA())

This means if the value in cell B3 is greater than the value of the break, then the cell should show the value 50, otherwise enter a #N/A value.  This value fo “50” will create the “Break” once graphed.

In the After column, your calculation in cell E3 should look like this:

=IF(B3>SPLIT_BREAK_COL,B3-SPLIT_RESTART_COL-1,NA())

This means if the value in cell B3 is greater than the value of the break, then the cell should make a calculation (the original value subtracted by the restart value, otherwise enter a #N/A value.  If you copy the formula down the rows, this will create the “remainder of the column.  Every cell should show #N/A except for series 4.

The table should now look like this:

New data table

Step 3 – Plot the table as a stacked column

First, highlight columns C, D and E and then click on the Insert tab > Column > 2D stacked column.

stacked columnExcel will create a chart as shown below:

graph 1Right click on the chart and then click on “Select Data” and click on the “Edit” button to adjust the Horizontal (Category) Axis labels.  Use column A as the range.

Delete the legend and gridlines by clicking on them and pressing the delete key.

We now need to format the Before and After series so that they are the same.  Click on one of the series and press Ctrl+1 to open the dialog box.  You can then change the fill color so that they match.

graph 2Make sure that the plot area fill is white and that the graph has no line (outline).

To create the break, simply insert a white rectangle by clicking on Insert > Shapes > Rectangle.  Then create the teeth by adding triangles filled with the same colour as the Before and After series.  Group the shapes together:

shape

Then once it is grouped, click on the group to select it and then press Ctrl+C.  Click on the break series (red in the chart above) and press Ctrl+V.  Your chart should now look like this:

graph 3

Step 4 – calculate the new Y axis and scale

To show the break Y axis, we will need to create it.  This is done by adding an XY scatter line to the graph.  First create three new columns: “Labels”, “Xpos” and “Ypos”.  Write in the labels you want (see step 1).  Leave a blank where the break should be.

Then to create the arrow on the Y Axis where the break will be, we need to fill the Xpos column.  I have put zeroes for every label except the break.  This will help to create a vertical line with an arrow.  I want the arrow to be around a quarter of the size of one of the six series, so I entered a value of 0.25 (assuming my scale will be 0 to 6).

The Ypos values will determine where the labels will lie vertically.  In the calculations in Step 2, we determined that the break would be 50 units in value.  To begin with the Ypos and the labels will have the same value. This will change when we hit the break.  To centralize the arrow we will put the “blank” label as the previous value + 25.  The first label after the break will be the blank label value + the other 25.  The rest is steps of 250 to match the earlier scale.

I’ve also added calculations to work out the minimum and maximum of the Y scale:

New scale

Step 5 – add an XY scatter line to the chart

Right click on the chart and choose “Select Data”.  Then click on the “Add” button.

Enter “Yaxis” as the Series Name and the Ypos data as the values.  In my worksheet that is:

=column!$E$13:$E$21

Press the OK button and then the OK button again.  The chart should update as follows:

graph 4Right click on the new series then press Ctrl+1.

In the new menu box that appears, choose to plot the new series on the secondary axis and press the close button.  The graph should update again and you should be able to see the new axis on the right hand side of the chart:

secondary axisRight click again on the new series and this time choose “Change Series Chart Type”.  In the menu box that appears, choose a XY Scatter chart with Straight Lines.

XY scatterThe chart will again update:

XY scatter chartRight click once more on the chart and choose “Select data”.  Choose the Y axis series and press the “edit” button.  Add the X axis values (xpos column).  In my worksheet this is:

=column!$D$13:$D$21

Then press the OK button and then the OK button again.  You will probably be no longer able to see the XY line on your chart.  This is because of the scale.

Click on the “Layout” tab in the “Chart Tools” section of the ribbon. Then click on Axes > Secondary Horizontal Axis > Show Default Axis.  Your XY line and a new Axis at the top of the screen will appear.

graph 5

Step 6 – Formatting

First if you haven’t already done so, make the primary vertical (left Y axis) and secondary vertical (right Y) axis match in terms of scale.  As you can see in the image above, I’ve given both of mine a minimum of 0 and a maximum of 1550 to match my calculations in step 4.  Once this is done you can delete the right hand axis.

Then format the (top) secondary horizontal axis so that the arrow is to one side only.  I’ve given mine a scale of 0 to 6.

Then double click the (left hand) primary vertical axis.  in the pop up menu that appears, choose to have no major tick marks, no minor tick marks and no axis labels.  Click on the line color tab and choose no line and press the close button.

tick marks

Repeat for the (top) secondary horizontal axis.  Your graph should now look like this:

graph 6

Double click on the XY line and format it to match the colour scheme you have chosen for the X axis.  In my case grey with a 1px line.  You can do this on the Line Color and Line Style tabs.

Step 7 – add the XY labels

Click on the XY line.  Then click on the Layout tab of the Chart Tools section of the ribbon.  Then in the labels section, click on Data Labels > Left.  You may need to re-position the chart so that the labels can be read clearly:

data labels chart

Unfortunately these are not the labels we want.  There are addins that you can buy to give you custom labels.  However, it can also be done manually.

Double click on the first data label.  Then in the function (fx) bar, type in the cell reference for the label you want to appear.  So if I am starting at the top most label (1550), I double click it and then in the function (fx) bar I type:

= C21

Repeat this for each label, including the one in the centre of the arrow, which should update to show a blank label.

Re-position those that are difficult to read by double clicking on them and then with the mouse button held down, dragging them into their new position.

Your graph should now be finished!  You can use the same technique with a bar chart as well:

broken columnbar chart

You can download the sample worksheet here.  Any questions, please don’t hesitate to post a comment!

+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

Linear bubble charts for comparison

A colleague of mine uses a lot of bubble charts and wanted a simpler way to compare the data in each chart.   I have to say I am not a big fan of bubble charts as I find them difficult to analyse and compare, however I accepted the challenge!  This is what I proposed:

The chart is actually quite simple and I’ve uploaded it here if you wish to see how it was done.  Each line is set up on a different tab, (data1, data2, etc) but in essence, each line of bubbles in the chart has a different number in the Y axis column.  For the first line, the value is 22, for the second line, the value is 17 and so on until the last line which has a value of 2.  The value column gives the size of the bubble and the date column is uniform to allow the positioning of the centre of each bubble.

excel data

What do you think?

+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

 

How to create a JAWS chart

As a big movie fan, I can tell you that no horror or thriller has ever had as much effect on me as watching JAWS as a child. I remember begging my parents to let me watch it, and I remember vowing that I wouldn’t be scared. Guess what? I was terrified. There’s something about that those two bass notes repeated over and over faster and faster that is just chilling to the soul. I even made my mum check under the bed twice before I would go to sleep. (Even though I was old enough to know that sharks needed water to breathe, and of course there was no water under my bed.)

JAWS

That being said, a JAWS chart is not so scary.  In fact once you know how, it is actually very very easy!  What is a Jaws chart? A Jaws chart allows you to compare two sets of data.  Both data sets are plotted as bar charts, but one is placed on the bottom x axis, the other on the secondary top axis.

JAWS chart

Step 1: Set up the data

So first you need 2 sets of data.  This could be the sales of 2 products over time; it could be sales of the same product over two different years.  But in the end, you need two columns of data with a third column to make the labels for the horizontal axis

data table

Step 2: Create a normal column chart

Highlight your three columns of data and click on Insert > Column > 2D Clustered Column chart

2d clustered

You should have a chart like the one below:

first draft

Step 3: Move the second series onto the secondary axis

Left click on the second series and press Ctrl+1 to bring up the formatting window.

format data series

Move the chart onto the secondary axis and reduce the gap width to 50%.  Before closing the window, left click on the first series and reduce its gap to 50% as well.  Then close the window.

The chart should now look like this:

second draft

Step 4: Add a secondary vertical axis and adjust

With the chart selected, click on the layout tab.  Then click on Axes > Secondary horizontal axis > Show left to right axis. The chart should update as shown below:

third draft

Double click on the secondary VERTICAL axis.  In the menu box that appears, adjust the axis options so that the “values are in reverse order” and the Horizontal axis crosses at axis value “0”.

adjust axis

Then adjust the maximum scale value to a value that allows you to see the up and down columns without them overlapping.  Note: You will also have to adjust the left primary vertical axis to the same amount.  In my case, I changed both values to “100”.  The graph should then look like the one below:

fourth draftStep 5: Format the chart

Click once on the secondary horizontal axis and then delete it.  Repeat the procedure for the secondary vertical axis and the primary vertical axis.  Adjust the fill colours of each series as you wish.

Right click on the first series and choose add data labels.  Repeat for the second series.  Then left click on the first set of data labels and press Ctrl+1.  Change the label position to inside end.  Then click on the home tab and adjust the font colour and size.  Repeat for the second series and then press the close button.

data labels

Your chart should now look like the one below.  What do you think?  Can you compare the two series easily?  You can download the excel file here, or view it on my Sky Drive below.

+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

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