Animate a single series in a chart

Last week I posted a simple video showing a couple of graphs that had a single (or couple) of series animated for emphasis.  I thought this week I would post the how to!  It’s actually very simple.  You can download my PowerPoint here.

Adding an animation to a PowerPoint Chart is the same as adding an animation to any other object.  Click on the chart, then click on “Animation” on the ribbon and choose the animation you want to add.  (I chose “Wipe”).  You can then adjust the options for the animation using the “Effects Options” button.  Unfortunately when you add an animation it adds the animation to the whole chart – including the axes etc.  To see this, while the chart is selected click on the “Animation Pane” button in the animations toolbar.

animation pane

To be able to adjust the animation to the different elements of the chart, right click on the animation, and then select effect options > Chart animation.

chart animation

To be able to animate the series individually, click on the down arrow on the right hand side of the text “As one object” and select “By Element in Series” or “By Series” depending on the configuration of the chart.   I also deselected the “Start animation by drawing the chart background” check box.  Then press the OK button.

A double down arrow will now appear in the animation pane underneath the animated chart.

double down arrow

Click the double down arrow to see the individually animated series.

4 series animated

My chart has 4 series, so there are four elements that have been animated.  Since in my chart I want to emphasize series 3 and 4 only, I simply click on the ones I don’t want to see and press the delete key.  You can then adjust the options of the 2 animated series so that they start together or individually as you prefer.

I adjusted my charts so that the animation starts as soon as the viewer navigates to the page (using the start with previous option) and so that both lines start together.  You can do this using the down arrows next to each animation in the animation pane.  Click on the down arrow and then select “Start with previous”.

And that’s it – hope you find it useful.

+Alesandra Blakeston

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

Create a series of traffic light charts

datapoint-3-add-new-connectionI’m always on the lookout for new and interesting ways to present data in Excel and PowerPoint. So when I saw this DataPoint add in for Powerpoint I was definitely interested. As I looked through the site and of course their blogs, I stumbled across this post: “Traffic Lights as Dynamic Semaphores” and was immediately inspired. Don’t misunderstand me, DataPoint is a very sophisticated add in for PowerPoint and does a lot more than just this one chart, but it got me wondering… Do I need the software to create this effect? Could I recreate this manually and still have the traffic lights update when I adjust the data?

If you’ve read any of the posts on this blog, you’ll already know that I love a challenge, and so off I went. The idea was to create a series of traffic lights that show a warning light (orange) when the stock level is the same as the reorder level, a danger light (red) when the stock is lower than the reorder level and of course a full steam ahead light (green) when the stock levels are healthy and therefore above the order level for that product.  As usual I started in Excel and created a data table.

table 1The figures above are just an example and if you want to recreate this using your own data, you can download my worksheet here.

Then I needed to create some calculations to plot the different graphs.  So I added three columns labelled “Red”, “Orange” and “Green”.  Using IF statements, I then got excel to calculate whether the traffic light should be red, orange or green by putting a 1 in the appropriate column. The three calculations I used can be seen below:

Assuming your titles are in Row 1 and your stock column is in Column D and your reorder column is column E:

  • Cell F2 =IF(D2<E2,1,””)
  • Cell G2 =IF(D2=E2,1,””)
  • Cell H2 =IF(D2>E2,1,””)

Copy and paste the calculations down the table and then your table should look like the one below:

table 2

As you can see, the stock for product ax234 (row 2) is higher than than the reorder level so there is a 1 in the Green column for that row.  Product ss213 on the other hand has less stock than the reorder level, so there is a 1 in the red column.  And so on.

As it stands though, all we have is a nice table.  We could use conditional formating and create a table like the one below, but it doesn’t really look like traffic lights.  In theory, we could change the formatting of the cells etc., change the direction of the table so that it works, but I wanted something a little more flash!

table 3

What I did was create a series of 3 pie-charts for each line.  Two would be blank as there is no number in their cells, the third would be filled and create the “light”.  Then behind the three pie charts, I create the illusion of the traffic light. Again, if you want to see more, you can download the worksheet here.

How is it done?

Step 1 – create a basic pie chart

With cell H2 highlighted, click on Insert > Pie > 2-D Pie > Pie

insert chartYou may get a blank pie chart.

original pie

If this happens, right click on the chart and choose “Select Data”.

select data

Click on the “Edit” button and then adjust the fields as shown below and press the OK button:

edit seriesYou should then have a filled pie chart. Delete the legend and title. Then adjust the fill of the chart and the background to “No fill” by pressing Ctrl+1 to bring up the formatting menu box and adjusting the parameters. Finally remove any border on the chart by selecting “No line” on the border color. Your chart should now look like this:

new pie chart

You can then adjust the fill of the pie chart so that it has a green radial gradient as shown below:

green gradientI’ve chosen a vivid green that changes gradually to a darker green with a radial gradient radiating from the top left corner.

Finally adjust the position and size of the pie chart so that it is perfectly centered inside your rectangular chart background, taking up as much of the space as possible.  You do this by dragging the corners of the pie chart to the edge of the background.  Once done, you can adjust the size as you wish, just keep the aspect ratio squared i.e. length and width the same to ensure you have a perfect circle.

Step 2 – create 2 more pie charts

Copy and paste the pie chart you have just created and position the new chart above it.  Repeat so that you have three pie charts on top of each other.  You can use the align commands on the Drawing tools tab to ensure that they are vertically aligned and correctly spaced.

three green lights

We need to adjust each pie so that it is looking at the data from the correct cell.  Click on the topmost pie chart.  The table should now have lines on it indicating which cell the pie chart is taking its data from.

cell reference

Drag the blue square from cell H2 to cell F2. The pie chart should update and appear blank. Repeat for the middle pie chart, dragging its blue square from cell H2 to cell G2. Your three lights should now look like this:

updated lights

We need to adjust the fill of the top and middle lights. It’s easier to do this when there is data in there, so TEMPORARILY, put a 1 in cells F2 and G2. The fill should reappear in the pie charts. Click on the top most pie chart again, press Ctrl+1 to bring up the formatting box and give it a red radial fill.

red radial fill

Then click on the middle pie chart and give it an orange radial fill. Your charts should now look like this:

three lights

Once you’ve adjusted your fill, don’t forget to put the calculations back into cells F2 and G2. The fills will disappear again, but that is normal!

Step 3 – Create the background and highlight

To give the traffic lights a real flair, I added a black rounded rectangle behind the three lights. I also added 2 circles as shown below:

additional shapes

The first circle is black with 40% transparency.  The second is red with a red 12pt outer glow. When put together they look like this:

background red

I copy / pasted the 2 circles twice and adjusted the glowing circle to be orange and green for the other two “lights”.

traffic light background

All that’s left is to position the three pie charts on top of the background and you have your first traffic light.

traffic light 1

You can see that the green light shines brightly, showing that for the product in row 1 we have plenty of stock.

Step 4 – Repeat for the other rows

Before doing anything, click on the home tab of the ribbon and then choose Find & Select > Select Objects.

find and select

This will let you select all of the objects that make up the traffic light by dragging your cursor over them.  Once done you can press Ctrl+C to copy the traffic light and then Ctrl+V to paste another one.  Click on the first “light” and update the cell reference as shown in step 2 above.  It should now be looking at cell F3. Repeat for the other 2 lights, for cells G3 and H3.  You should now have 2 traffic lights for the first 2 rows.

Keep copying and pasting and adjusting the cell references until you have a traffic light for every row:

traffic lights

And that’s it! You can download the worksheet here.

Hope you find it useful!

+Alesandra Blakeston

How to create a double lollipop chart

Visually great charts are always welcome in my inbox, so when a friend forwarded a link to me showing a tutorial on how to create a double lollipop chart, I was intrigued.  A double lollipop chart (not to confused with a cherry chart – although it looks very similar) is used to compare target vs actual performance – it’s another way of comparing one line against another.

lollipop chart WSJ

The picture above is from the post I was sent, which had a great tutorial created by SteveEqualsTrue from exceldashboardtemplates.com and a download which recreated the chart (above) seen in the Wall Street Journal.  Since I didn’t want to recreate the WSJ chart and actually wanted the chart to have conditional formatting, I created my own version.

cool chart

You can download the sample chart here.

 Step 1: Set up your data

To begin with you should have a data table with one column showing the actual performance, and the second column showing the target figures for each of your datasets (time, categories).

actual vs target data table

To add in some conditional formatting, we are going to add 2 new columns titled “< target” and “> target” as shown in the picture below:

CF target columns

In cell C2 of the “< target” column type in the calculation:

=IF(B2<=E2,B2,NA())

In cell D2 of the  “> target” column type in the calculation:

=IF(B2>E2,B2,NA())

These two calculations assume that your actual data is in column B, your target data is in column E and your “< target” and “> target” data will be put into columns C and D respectively. Basically in column C, if the value in the actual column is less than or equal to the value in the corresponding row of the target column, it will return the value in column B, otherwise it will show a #N/A value instead.  The same principle applies for Column D only it looks to see if the value is greater than the target.

Copy and paste the calculations down the rows to fill the table.  It should now look something like this:

calculations complete

To finish the table we are going to create an axis column.  This is because some of our values are less than zero and we will need to create a custom horizontal axis at the bottom of our chart that won’t interfere with our data. The lowest value is -50%, so I’m going to add a column called “Axis” and fill each row with the value -60%.

custom axis

Step 2: Create the chart

Highlight cells C1:F9.  Then with the cells selected, hold down the Ctrl key and then use your mouse to also select cells A1:A9.  Then click on Insert > Charts > Line > 2-D Line > Line with markers.

insert line chart with markers

A chart like the one below should appear:

line chart

You can delete the gridlines now or later.  Double click on the legend and then click on the Axis label and press the delete key.

updated line chart

Step 3: Format the Axis series

Left click on the Axis series.  Then press Ctrl+1 to open up the formatting menu box. Change the series so that it is plotted on the Secondary axis.

secondary axis

Then with the formatting menu still open, click on the marker options tab and choose to have no markers (none).  Finally click on the Line Color tab and choose to have no line.  You can then press the close button.  Your chart should have updated as follows:

secondary vertical axis

Step 4: Format the axes

Before we format any of the axes, we need to add a secondary horizontal axis.  To do this, click on the chart, and then click on Chart tools > Layout > Axes > Axes > Secondary horizontal axis > Show left to right axis.

secondary horizontal axis

Your chart should update as shown:

all axes

Left click on the primary horizontal axis and press Ctrl+1. Remove the tick marks (major and minor) and the axis labels.

format horizontal axis

Then click on the Line color and Line style tabs and adjust the formatting as you wish.  Your chart should update as you make the changes:

formatted horizontal axis

With the formatting menu box still open, left click on the secondary vertical axis. Remove the tick marks (major and minor) and the axis labels. Then check the “Values in reverse order” check box.

values in reverse order

Finally, click on the Line color tab and choose No line.

Your chart should update as shown below:

finished axes

You can now press the Close button.

Step 5: Create the double lollipops

Left click on the “Target” series.  Then click on Chart tools > Layout > Analysis > Lines > High-Low lines.

high low linesYour chart should update as shown below:

high-low lines

Reselect the target series and press Ctrl+1.  Click on the Line color tab and choose No line.  Before closing the formatting menu box, repeat  the process for the “< target” series and the “> target” series.  Your chart should update as shown below:

basic lollipop chart

To finish, create shapes for your bullets.  Copy the first shape using Ctrl+C.  Then click on the series you want to use the chape for and press Ctrl+V.  I’ve used circles as shown below.  Green means we’ve achieved more than the target, red means below target.  I’ve also formatted the high-low lines.

cool chart

However you can format the lollipops anyway you choose, including using triangles (to look like up and down arrows).

alternative lollipop chart

You can also plot the data without conditional formatting:

double lollipop chartAnd that’s it.  I hope you find it useful!  You can download the sample chart here.

+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

If you have to use a pie chart – be cool!

So recently I posted a PowerPoint template Flat Graphs & Charts which featured a rather cool Pie Chart.  I was asked if I could explain how it was done.  Honestly, I thought this had already been posted, but after a quick review I realized it hadn’t and so, here it is…

Background

There’s been a lot of discussion on the internet about pie charts. The general consensus seems to be that pie charts are bad:

Pie Charts Are The Worst – Business Insider

screen shot 2013-06-17 at 10.34.08 am

“Take a look at these three pie charts.

Let’s say that they represent the polling from a local election with five candidates at three different points A, B, an C during an election.  So, what can we learn from this information?  Since these are the shares of the votes that each candidate will get, it should be easy for the reader to figure out what, exactly is going on in this race.  But it really isn’t.  In the first race, is candidate 5 doing better than candidate 3?  Who did better between time A and time B, candidate 2 or candidate 4?  Who has the most momentum in the race?  If the point of a chart is to make information more easily understandable, how is this chart working for you?”

However, that being said, if your data is simple pie charts can show the data effectively – and therefore be good:

Why Tufte is Flat-Out Wrong about Pie Charts : Speaking PowerPoint

65-percent-market-company-b-and-c

“But what if, instead, the only point you want to make is that the 2 largest distributors control 65% of the market. Which graph demonstrates that more clearly?”

So I thought I would show you all a really cool trick to make pie charts look different.  I’m not going to talk about understanding / analyzing them, only changing the visual aspect.  I prepared a presentation for you, so if you don’t want to follow the instructions, you don’t have to.  Instead you can download the sample “pie_charts_seen_differently” file attached.

How is it done?

Prepare your pie chart as normal, and format it with the fill colours etc you want for each series.

To do the rest you will need editing software such as inkscape, or adobe illustrator. Basically, though you take a circular shapes and cut out of the centre the parts of the pie chart that you want to show. Save your shape in the *.emf format and import it into Excel (or PowerPoint).  You can download my inkscape file *.svg and the *.emf files as well by clicking on the hyperlinks.

If however, you don’t have or don’t know how to use image editing software, you can use one of the shapes provided in the PowerPoint presentation above. Take the shape and copy it using Ctrl+C.  Click inside the chart (but not on the chart) and press Ctrl+V.  Re-size the shape so that it covers the edges of the pie chart.  Re-colour the shape to match the background of your chart.  Since my background is white, the fill of the shape is white.

And that’s it!  You can also paste the shape on top of the graph by clicking on the slide in PowerPoint or an adjacent cell in Excel.  If the picture is pasted inside the graph, it will re-size with the graph when you change the graph’s size.  If not, you will need to manually resize it yourself

cool pie charts

The pie charts in the presentation are all data driven and so can be edited to suit your needs.  The *.emf files are editable in PowerPoint / Excel (once you ungroup them), so you can also change the fill, etc as you need to suit your design.  Again, you can download the PowerPoint pie_charts_seen_differently.

Have fun!

+Alesandra Blakeston