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

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

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

 

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

How to create a word chart

A while ago now, I posted a blog on how to create a dollar chart.  I’ve had a few requests since then along similar themes and thought I’d post one in particular: a word chart.

word chart

The principle is the same as the dollar chart.  You simply plot the total amount of your stacked chart on the secondary axis and then paste a picture (with the appropriate shape cut out) onto that series.  (If you need more detail on how to do this, check out the dollar chart link above).  With word charts, you obviously have to use a stacked bar chart rather than a stacked column, but that is the ONLY difference.

If you are interested, you can download the *.xlsx file I used this time here. Obviously the more complicated your shape, (or word) the harder it is to read the data. Of course if you are only plotting one series, then it’s much easier to read!

word chart single series

Please note: The font I’ve used to create the cut-out shape is italicized in design.  This makes it even harder to read the values!  Best case would be to have a font that is squarish.  Remove the space between the letters and you can create a great cut-out shape.

I used inkscape as usual to create the shapes and saved it as an *.emf file which can then be imported and used in Excel.  To make the white cut-out visible, I’ve given it a red fill in Excel – normally that isn’t there:

red fill

I’ve really enjoyed hearing how others have used this technique in their charts, so don’t hesitate to put a comment below to let me know what you think!

Enjoy

+Alesandra Blakeston

 

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

How to create demographic charts for infographics

Creating charts for infographics is not easy.  You need a chart that is simple and easy to read and one that doesn’t need a lot of text or explanation.  To that end I wondered if I could create this kind of chart using Excel…

finished grid

 

It’s actually relatively easy.  Let’s imagine that you want to create an infographic showing the percentage of households owning different pets. You have the data table as shown below:

table 1

We can also create a grid of squares 10 x 10 across by altering the width and column size of columns D to M and rows 7 to 16.  I have changed them to approximately 10 pixels across / down.  To make it easier to see, I also put a frame around the grid.  I then repeated this for each of the different types of animal.  I also added internal borders in white.  To do this I pressed Ctrl+1, clicked on the border tab and added black borders on the outsides and white borders on the insides.

grids

 

I then added numbers along the bottom and right hand side, one for each column / row.

numbers

 

 

To make the chart look cleaner, I then made these numbers white.

To add the fill, I added a calculation into each of the cells in the grid. The first cell calculation is:

=IF((D$17+$C16)<($B$2*100),”1″,””)

The $ symbols are there to prevent the row or column from changing as I copy paste the calculation across the columns and up the rows.  But basically if the sum of the two numbers in that specific row and column are less than the corresponding figure in the table, then to add a “1”, otherwise, to add nothing.

Copy and paste this calculation so that it fills every cell in the grid.

For the first cell, the calculation adds up the 0 from the left, plus the 1 from below to equal 1), and then compares that to the % household with dogs (36.2%), since 1 is less than 36.2, the figure 1 is put into the cell.  The next cell would add 0 and 2.  The cell above would add 10 + 1 and so on.

I then repeated this for the other grids, substituting $B$2 with the corresponding cell reference for each of the animals.  Each of the grids now contain lots of “1”s, however this is not very attractive, so lets add some conditional formatting!

Highlight the first grid and on the home tab of the ribbon, click on conditional formatting > new rule.  Click on “Format only cells that contain” and change the formula to say “equal to” and put the number 1 in the data entry text box as shown in the picture below.  Change the format so that the cell fill is the same colour as the text colour, I have chosen light blue for the dogs grid and press the OK button.

formatting

 

Repeat the procedure for the other grids using a different fill (and text) colour for each animal grid.  Your grids should now look like the pictures below:

coloured gridsAll that you need to do now is alter the text colour of the animals in the original data table to match the grids:

final table

 

You can of course add formatting and titles as you wish to make the charts easier to read, but the basics are there…  finished gridsYou can download the source file here.

Enjoy

+Alesandra Blakeston