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

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

Network relationship chart

I’m a big fan of anyone who can display data in a visual way.  Well today, rather than displaying my own work or giving a “how to” guide, I thought I’d display work from Chandoo.org – one of the best sites for Excel help.  This network / stakeholder relationship chart is a work of art and needs no explanation from me.  The full tutorial and example template can be found here: http://chandoo.org/wp/2014/08/13/network-relationship-chart/

Hope you find it as inspiring and as useful as I did!

+Alesandra Blakeston

network-relations-chart-demo

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

How to create a BCG matrix in Excel

Having used this type of chart a lot recently, I thought it was time I had a decent version in Excel.  In case you are not aware a BCG matrix, also known as a growth-share matrix is a management planning tool.  It is used to portray a company’s / SBU’s product portfolio on a quadrant showing relative market share (horizontal axis) and speed of market growth (vertical axis).  It basically shows you the potential of your company’s products.

A BCG matrix is basically a bubble chart with a few tweaks.  When complete, it should look something like this:

BCG matrix

You can download my sample chart here.

Step 1: Set up your data

To be able to plot a BCG matrix successfully, you need the following data: market growth,market share, market share of the largest competitor, and relative market share.  You can calculate the last by dividing the market share / market share of the largest competitor for example:

=C2/D2

A sample data table might look like this:

BCG data

Step 2: Create a bubble chart

Highlight the first three columns of your table, in my case cells A1:C6. Then click on Insert > Other Chart > Bubble Chart.

insert bubble chartYour bubble chart will look something like this:

bubble chartYou can then delete the title, legend and horizontal gridlines by clicking on them and pressing the delete key.

Step 3: Convert the chart to a BCG matrix

Right click on the x axis and then choose “Select Data”.  In the pop up menu that appears, click on the “Edit” button.

select data

You have to change the series X values.  Highlight the Relative Market Share values.  In this example we need to change it from column A to column E.

Edit X series values

It should say:

='BCG Matrix'!$E$2:$E$6

Your BCG Matrix chart will update. Now, all of the values should be correct.

Adjusted X axis

Right click on the X axis and then choose format axis.

format X axis

Select “Values in reverse order”.  Then change where the “Vertical axis crosses” to have an Axis value of 2.  This value will change depend upon your data.

Without closing the formatting pop up menu box, click on the Y axis.  You can then adjust the minimum and maximum values to “0” and “4”.  The Axis value this time should be 0.2.  Again, this value will change dependent on the data added.

format Y axis

You can also change the number format using the Number tab to “Number” with 2 decimal places.

number

You can then press the close button.  Your chart should now look like this:
complete BCG matrix

Step 4: Format the BCG Matrix

So in my example, I created some custom pictures to use as fills for the different bubbles: a star, a cow, a dog and a question mark.

custom bubbles

The bubble in the left upper quadrant is the star.  Once you’ve created your star picture, click on it and press Ctrl+C.  Then, click on the bubble twice and press Ctrl+V.  The bubble should then update with the picture.  repeat the process for the other bubbles.  The left lower quadrant is for the cow picture.  The right upper quadrant contains the question mark bubbles, the right lower quadrant contains the dog bubbles.

The final touch is to add data labels.  Ensure all of the bubbles are selected (click off the chart and then click once on the series).  Then click on Chart Tools > Layout > Data labels > Right.  The chart will show the values of the chart.  To change the data labels so that they show the product names, click on one of the data labels twice (slowly). Then click inside the function test entry field and type the cell reference that shows the name of the product.

edit data label

Step 6: Add the coloured background

Click on Insert > Shapes > Rectangle and draw a rectangle over the top of one of the quadrants.  Format the rectangle as you wish. Repeat until you have a different colour for each quadrant.  Select all four, by clicking on them while the shift key is held down.  Then right click and press “Group”.  Then with the group still selected click on Drawing Tools > Format > Send Backward > Send to Back.

Your chart should now look like this:

BCG matrix

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

+Alesandra Blakeston

Flat Shadow Icon PowerPoint Template

It’s Friday again, and here’s another Friday Freebie…  This one was originally for a friend who’s starting a new graphic design company that she hopes will be innovative and creative.  She’s really into primary colours, though and so went for option B which is also flat shadow, but looks very different.  Anyway, long story short, you get option A for free to use as you will.

You can download the freebie here.

Enjoy!  Hope you find it useful!

+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

How to create unique square pie charts

Pie charts are notoriously difficult to read.  That being said, I still see plenty out there.  Since I’m not a fan, I tend to replace them with bullet charts, thermometers etc.  Another way to replace them is with a “Square” pie chart:

square pie chartThese 2 charts only show one data point, but you can show multiple data points as shown below:

social media sqaure pie chart

Obviously, this type of chart isn’t a standard excel chart (at least not in Excel 2010).  There are a number of different ways of manipulating Excel worksheets to do this though.  I’ll show you my way, and you can let me know if you have a better one! You can download my sample worksheet here.

Table 1 – coloured squares with 1 data point to show

Step 1: Set up your worksheet

Imagine that you have a normal worksheet with a table in cells A1:B2.

basic worksheet

Adjust the width of columns D to N to 26 pixels wide.  (You can do this by highlighting the required columns, and moving your cursor over one of the trailing edges of the columns.  The cursor will change to the change width cursor, you can then drag the column width across to the required width. As you drag a small popup will appear showing the current width.  Stop when it shows 26 pixels.)

Repeat for the row height of rows 1 to 12, stopping when the height is also 26 pixels.

Using the ribbon, click on View > Show > Gridlines – or in other words, turn off the gridlines by deselecting the check box in the Show section of the View tab of the ribbon.

gridlines

In cell D11 type in the figure 0. In cell D10, type in the figure 10. In cell D9, type in the figure 20, and so on going up in multiples of 10 until you reach cell D2 which should contain the figure 90.

In cell E11 type in the figure 1. In cell F11, type in the figure 2. In cell G11, type in the figure 3, and so on going up in multiples of 1 until you reach cell N11 which should contain the figure 9.

You should now have a table like the one shown below:
table 1

Highlight these cells and make the font colour white.  Then highlight cells E2 to N11 and change the fill to a light blue (for example). Your table should look like the one below:

table 2

Step 2: Add your calculations

In cell E11 write the following calculation:
=IF((E$12+$D11)<=($B$2*100),1,"")
We are going to use this IF statement will help us to create a coloured brick.  In essence it will calculate the sum 0+1 (cells E12+D11) and if that is less than the value we want to display in the chart (cell B2*100), then it will return a value of 1, if not the cell will be blank.

Once you’ve entered the calculation into cell E11, copy and paste it across the row, ending at column N.  Then copy and paste the entire row up, to fill the table.  Your table should now look like this:

table 3

Step 3: Add the conditional formatting

Highlight cells E2 to N11 and click on the small down arrow of the conditional formatting button on the Home tab of the ribbon.

conditional formatting

Click on New Rule…

new rule

Click on Format only cells that contain.  Change the second drop down box (between) to say equal to and in the final box add the figure 1.

equals toClick on the Format button.  Adjust the fill to a dark blue using the fill tab of the pop up menu that appears.  Change the font colour to the exact same blue using the Font tab.  Then press the OK button.

format cells

Press the second OK button and then the Apply button to add the formatting.  Your table should now look like this:

table 1 finished

Table 2 – coloured symbols with 1 data point to show

Step 1: Set up your worksheet

Set up your worksheet exactly as per table 1.  Please note, to show both tables in the sample worksheet, I’ve put my second table in cells P2:Z11, but the principle is exactly the same.

Step 2: Add your calculations

This table doesn’t actually need calculations!  Instead, simply highlight the whole table and change the font to wingdings. Then type in the figure « into each cell of the table. (Or type it once and then copy paste the value across the rows and columns).

Your table should now look like this:

star table

Step 3: Add the conditional formatting

Repeat the same process as table 1 to add another conditional rule.  This time, instead of choosing to format only cells that contain, choose to Use a formula to determine which cells to format.

formula

In the rule description type in the following calculation:

=(E$12+$D2)<=($B$2*100)

Note: My calculation is =(Q$12+$P2)<=($B$2*100) since both tables are in my worksheet!

Click on the Format button and adjust the font colour to white using the font tab of the format cells pop up menu box.  Then press the OK button and the Apply button to add the conditional formatting.  Your table should now look like this:

star chart finished

Table 3 – coloured symbols with 4 data points to show

Step 1: Set up your worksheet

Set up your worksheet exactly as per table 1, except that you will have four rows to your data table.

social media data table

Please note, to show all three tables in the sample worksheet, I’ve put my third table in cells E14:N23, but the principle is exactly the same.

Download the font “Social Font Face” from dafont.com or similar.  Highlight your table and make sure that the font “Social Font Face” is selected.

Step 2: Add your calculations

Just like table 1, you are going to add calculations.  First we’re going to calculate the running sum:

social media table adjusted

The first cell (cell C15) of the new column is equal to your first value (0.47).  The second (cell C16) is equal to the first value + the next (0.47+0.33 = 0.80). And so on.  The total (normally) should add up to 1 (100%).

Once you’ve calculated the running total, you are ready to add the calculations for your table.

In the first cell of your table type in the formula:

=IF((E$24+$D23)<=($C$15*100),"F",(IF((E$24+$D23)<=($C$16*100),"T",(IF((E$24+$D23)<= $C$17*100),"y","G")))))

In essence this is a sumulative IF statement:

  1. If the sum of E24+D23 is less than our first data point, then the cell will show the value F.  If not it carries out the next IF statement.
  2. If the sum of E24+D23 is less than the sum of the first 2 data points, then the cell will show the value T.  If not it carries out the next IF statement.
  3. If the sum of E24+D23 is less than the sum of the first 3 data points, then the cell will show the value y.  If not it will show the value G

third table calculations

Once you’ve added the calculation, copy and paste it across the rows and columns to fill the table.  Your table should now look like this:

third table with no formatting

Step 3: Add the conditional formatting

To add colour to this table you can set up conditional formatting, one rule for each symbol in the same way as for table 1.

  • If the cell value = F, make the fill dark blue
  • If the cell value = T, make the fill light blue
  • If the cell value = y, make the fill red
  • If the cell value = G, make the fill green

Note: Conditional formatting doesn’t recognize the difference between Y and y.  The font “Social Font Face” however will, so make sure you put a lowercase y in the calculation, even though it doesn’t matter for the formatting.

final table conditional formatting

Once applied, your table should look like this:

final tableAnd that’s it.  Hope you find it useful.  You can download the sample worksheet here.

+Alesandra Blakeston