Using animation with charts

Recently I posted a blog on how to simplify charts so that the key message came across simply by looking at the chart.  I got a few messages after the post from colleagues saying that while the overall message of “Less is more” is great, what about adding emphasis to really sell your message?  Can you animate charts?

Well the simple answer is yes, of course you can animate charts.  That being said, I would immediately qualify it with – less is still more.  You don’t want to emphasize the entire chart and every single data point.  The short video below shows 2 charts that have been animated.  The first is a column chart.  The last series has been animated to emphasize the improvement.  The second is a line chart where the progress of the last 2 series have been animated.

Let me know what you think!  I’ll be posting the how to do this later next week, but until then you can download the PowerPoint presentation here.

Hope this helps

+Alesandra Blakeston

Less is more

In the business world I come across some really awful charts.  Take for example the one below (I’ve changed the categories and title to protect the innocent!):

awful chart

What exactly is the message behind this chart?  What data is important to know?

It turned out that the “bird” data was important.  The category had increased by 60% in the last month.  Looking at that chart can you tell that the “bird” category has improved?  Can you even tell that the “bird” category is important?

If you want to show an improvement, plot the before and after.  This can be done very simply using a line chart with the before and after being plotted, or even as a stacked bar chart.  Either of those alternatives would have presented the data in a better way.  Leaving aside the incorrect chart choice, and the improvement for now though, how could you simplify the above graph so that the important data is being shown?  Have a look at the video below to learn more.

 

I leave it to you to decide which is the better version!

awful chart     better chart

Hope this helps!

+Alesandra Blakeston

Broken column chart with a stacked bar chart

A long time ago now, I wrote a “how to” guide for creating a broken column / bar chart.  Since then I have been inundated with requests for variations on this chart.  The one I get the most often is “How can I create a broken column when I want to show more than one data set (and therefore already have a stacked bar chart before I add in the break and after columns).  Note: If this doesn’t make sense – allow me to explain!

The original post creates a broken column chart using a standard stacked column chart to create the before, break and after series combined with an XY scatter chart to create the custom axis as shown in the picture below.

broken column

However, if you want to have multiple data sets and already have a stacked column, it can make altering the template file I gave difficult.  The request I liked the most showed land area and water area for each US state.

US states stacked bar chart

As you can see there is a large gap between Alaska and the other states in terms of total land mass.  What we need is a chart more like this:

us states broken column

It makes it a little easier to see some of the detail than the earlier chart!  Of course, you could have the break at any point, or have multiple breaks, so long as you can do the calculations.

The answer to how to do it is all in your preparation.  Work out where you want the break to be and plot your basic data first – including your multiple series. Then once you have your stacked column data you can work out your custom axis using the data labels.  I’ve attached my work to create the graph above here, so hopefully those of you who want to use it can alter it accordingly.  If you need the basic how to, don’t hesitate to go back to the original post!

Hope it helps!

+Alesandra Blakeston

A to Z of awesome Excel

IMG_0143.PNGI’ve been posting for a while now, and it has to be said, my most popular posts are usually the ones I do on Excel. Since I have now got over 300 posts on this blog and 50+ on Excel alone, I thought I would compile an A to Z of all things Excel from my blog. Hope you like it!

A is for area intersect line charts
B is for bands as in confidence bands
C is for combination stacked clustered charts
D is for dollar charts
E is for Europe
F is for formatting – conditional of course!
G is for graphic equalizers
H is for hidden excel sheets
I is for interactivity
J is for jaws
K is for KPIs shown perfectly in these bullet charts
L is for linear bubble charts
M is for milestone timelines
N is for negative waterfall charts
O is for overlap in bubble charts
P is for picture axes
Q is for quartiles seen in box plot charts
R is for rising stars in this BCG matrix
S is for square pie charts
T is for total spend
U is for ui as in metro ui dashboard
V is for visual spreadsheets
W is for world map bubble pie charts
X is for XY scatter charts
Y is for YOY charts
IMG_0144.PNGZ is for z charts – coming soon!

Enjoy

+Alesandra Blakeston

Create line charts with confidence bands

Creating confidence bars in Excel is relatively easy.  First create your line chart.  Then with the series selected, click on Chart Tools > Layout > Error bars > More error bar options.  In the pop up menu that appears, you can either choose to have positive or negative error bars, or both.  You can choose the style and you can choose the amount you wish the bar to show.  This can be a fixed value, a percentage, a standard deviation or a custom range.

format error bars

Since my data has a custom standard deviation for each point, I chose custom and clicked on the Specify Value button.  Another pop up menu then appears and you can choose the cell range for both the positive and the negative bars.

custom error bars

This will give you a chart that looks something like this:

confidence bars

While that is great, it is not so easy to see the limits of the error bars and if you have a lot of data it will be messy.  So I give you instead, a confidence band chart:

confidence bands

At a glance, the confidence limit is much more obvious IMHO. Unfortunately, Excel doesn’t allow you to do this automatically.  Instead, you need to create a combined line / area chart. You can download my sample chart here.

Step 1: Set up your data

First in addition to your averages, you also need your standard deviation (or error) calculation.  My data looks like this:

averages std error

Then in row 4, you need to calculate the upper limit of your band i.e. for B4 the calculation will be:

=B2+B3

In row 5, you need to calculate the lower limit of your band i.e. for B5 the calculation will be:

=B2-B4

Your table should now look like this:

completed table

Step 2: Create your graph

Highlight rows 1, 2, 4 and 5 of your table and then click on Insert > Chart > Line chart.

insert line chart

Excel will create a line chart with 3 series as shown below:

initial line chart

Delete the legend and the gridlines.  Then right-click on the upper band series and chose Change Series Chart Type…

upper line seriesChange the chart type to an area graph (the first one in the list).  Repeat for the lower series.

Step 3: Format the confidence bands

By now your chart should look something like this:

combination line area chartTo finish the chart, simply format the upper series with a light blue fill (to match the dark blue line) and the lower series to have a white fill.

And that is it!

confidence bands

Both the confidence bar chart and the confidence band chart are in the sample worksheet here.  Let me know what you think and which you prefer!

+Alesandra Blakeston

In Cell Graphic Equaliser Chart

A while back now, I posted two blogs: How to make a graphic equaliser chart in Excel & How to create a dot table using the REPT function.  It occurred to me that in fact the two techniques could be combined to create a graphic equaliser table…

graphic equaliser table

You can download the sample worksheet here.

Step 1: Create your data table

Obviously for this type of chart you can only show one set of data.  You can see my data table below:

data table

You’ll notice that the fill is black and the text white, which is not my usual look for excel tables.  This is deliberate.  Graphic equalisers look better against a dark background that a white one.  If you decide to leave the worksheet white, then turn off the gridlines by deselecting the checkbox View > Gridlines

gridlines

Step 2: Add the calculations

My data table is in rows 1 – 14.  So I’ve left a blank row and then rows 16 – 20 will be my table.  The data table is in columns A and B, so I’ve left a blank column and then my table will be in columns D to P.  I’ve altered the width of these columns to 21 pixels wide.  To do this highlight the columns and hover the mouse over the trailing edge.  Then hold down the mouse and drag the trailing edge until the pop up window shows 21 pixels.

change column width

Set up X axis labels

In cell D20, type in the calculation:
=A2

In cell E20, type in the calculation:
= A3

Repeat until all of the X axis is showing.

Set up green bars

In cell D18 (I left a row to separate the labels from the bars), put the following calculation:
=IF($B2>6,REPT("¢",6),REPT("¢",$B2))

This calculation looks at the data in the first row of the table. If the value is 6 or less, it will duplicate the ¢ symbol the same number of times as the value. If the number is above 6 it will reproduce the ¢ symbol six times. Copy this calculation across the table, adjusting the cell reference to be the correct cell in the data table.

first calculation

Then highlight the whole and change the font to “wingdings 2” and the font colour to green.  Adjust the row height so that you can see all six squares comfortably.  Your table should now look like this:

green bars

Set up yellow bars

In cell D17 (the cell above the first set of green squares), put the following calculation:
=IF(B$2<7,"",IF(B$2-6>4,REPT("¢",4),REPT("¢",B$2-6)))

Again, this calculation looks at the data in the first row of the table. This time it subtracts six from that value and looks to see if the new value is 4 or less.  If so, it will duplicate the ¢ symbol the same number of times as the value. If the number is above 4 it will reproduce the ¢ symbol four times. Copy this calculation across the table, adjusting the cell reference to be the correct cell in the data table. Then highlight the whole and change the font to “wingdings 2” and the font colour to yellow.  Adjust the row height so that you can see all four squares comfortably.

Set up red bars

In cell D16 (the cell above the first set of yellow squares), put the following calculation:
=IF(B$2<11,"",IF(B$2-11>1,REPT("¢",2),REPT("¢",B$2-10)))

Again, this calculation looks at the data in the first row of the table. This time it subtracts ten (six+four) from that value and then duplicates the ¢ symbol the same number of times as the remaining value. Copy this calculation across the table, adjusting the cell reference to be the correct cell in the data table. Then highlight the whole and change the font to “wingdings 2” and the font colour to red.  Adjust the row height so that you can see all two squares comfortably.

Add a legend

In the column next to the table put a legend showing the scale:

graphic equaliser tableYou can then adjust the row height etc as required to finish the table.

What do you think?  Can you use this type of in-cell chart?

+Alesandra Blakeston

 

WordPress Stats PowerPoint Template

Have you ever found yourself needing to show your WordPress statistics in a presentation?  Well the easiest option is of course simply to print screen the statistics page.  however, if you are looking for something along those lines, well here’s today’s Friday’s Freebie!

Wordpress stats

Feel free to use this dashboard for anything you wish.  The word cloud unfortunately isn’t editable, but you can easily make your own using your most popular tags and wordle.com.

Hope it’s useful!

+Alesandra Blakeston

10 reasons to take a fresh look at lists

I just love Monty Python!  And since I blog, I’m always looking for new ways to improve and enrich my work.  Loved this slideshare by Nick Kellet:

What do you think?

+Alesandra Blakeston

Stacked comparison charts

When looking at data, what do you prefer?  Lots of bright colours?  As much data as possible so you can draw a conclusion?  If you are like me, you want JUST ENOUGH information to make your decision and clear concise visuals.  What do I mean, well lets take this example (the names have been changed to protect the innocent).  The common barchart:

the common bar chart

Let’s assume for the moment that all of the information is necessary.  Is it easy to read?  In my opinion, it’s kind of average as in this is what you would probably get from most people wanting to sway you with data.  If you work at it you can see which colour, A to F performed best and worst for each data set.  However, let’s turn that same information into a line chart (without the line) and use flat lines as bullets and we get:

stacked thermometers

In this chart it’s easier to see which colour is “winning” and “losing”.  However, as you can see with the first set of data, red and light blue have scored the same, so we’ve had to play with the formatting a little bit or we wouldn’t be able to see the red line at all.  It’s still not easy to see the trend for each colour.  Did red go up or down across the five different sets.  So, what else could we do?  This is where I get creative.  I give you stacked comparison barcharts:

stacked comparison chart

Here you can easily see the progression of each colour from left to right and you can see how close they are to the target (25), while still being able to compare the series.  I’ve removed the Y axis and added major grid lines to show the target of 25, and added data labels which I’ve repositioned to create the first mini-axis.  Finally I got rid of the legend and again used data labels positioned over the first column of data to show which colour is which.  What do you think?  Would you use this type of graph?

Of course you could go really crazy and add minor grid lines as well to make it very easy to read the exact levels for each colour, but that’s up to you!  I prefer it without…

mini-gridlines

You can download the sample excel sheet here Stacked comparison chart.

Enjoy

+Alesandra Blakeston