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

Create a heat map table

Imagine you have some data showing performance of some teams in different categories:

results table

A very quick way of showing the results more visually is to create a thermal map.  You can do this very quickly using conditional formatting!  Download the sample excel sheet here.

  • First, highlight the values in the first column (result 1)
  • Then on the Home tab, click on the small arrow next to the Conditional Formatting button (in the Styles section)
  • Click on Color scales and then More Rules

Home tab

  • Ensure that “Format all cells based on their values is selected
  • Then in the Edit the Rule description section, choose a 2-color scale and choose 2 colors.  I’ve left the default colors in place for the first column (orange and light orange)

new rule

  • When you are done, press the OK button.  The column should automatically update

first column

  • You can then repeat this for each of the other columns, using different colors or the same color

tables

  • Then all you have to do is format the remainder of the table:

draft 1

If you wish, you can also remove the numbers:

  • First highlight all of the numbers
  • Right click on the selection and choose format cells
  • In the number tab, click on custom
  • Then in the Type box, enter three semicolons ;;;
  • Press the OK button
  • The numbers will disappear

draft 2

 

What do you think?  You can download the sample excel sheet here.

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

Here is today

Here is today

Just loving this data visualisation.  It’s so simple, so clear and yet so very very cool!  I’ve just included three of the many visualisations from the site below – so please check out the original in its entirety complete with sliding animated effects

This slideshow requires JavaScript.

Enjoy!

Alesandra Blakeston

YOY (year over year) charts

In business, it is important to track KPIs over time to evaluate performance.  Often we see graphs by month or even by quarter, sometimes with trends lines sometimes without.  However, with your basic line and bar charts, it is difficult to track seasonal variation and to compare one year to the next directly.  Let me give you an example:

Basic chart

This chart shows the performance by quarter over the past ten years.  Although it appears that the results have varied significantly over time, it is difficult to draw any conclusions from this data using only the table.  Let’s say that you suspect that there is seasonal variation.  We could plot each quarter as a separate series in the graph (with a different colour) using a stacked bar chart.  Please note you can download the excel sheet I am using to create these graphs here.

Seasonal chart

Suddenly it becomes clearer that Q1 is usually the lowest performing quarter each year and that Q3 is usually the highest. This would then allow us to analyse why there is such seasonal variation – very powerful stuff!  The chart isn’t very elegant though so we could also see this with a series of charts, each showing just one year:

chart series

The placement of the charts also makes it clear that there is much lower performance in Q1 each year.  However setting up one graph / year is time consuming.  Surely there is a better way?  What about instead of plotting the actual result each year, you plotted the change in results?

YOY chart

I’m only showing  the percentage change of three years here, but suddenly you can see that not only is Q1 always the lowest performer, but that in 2004 performance increased in Q1 by 5% and then decreased in 2005 by over 10%.  For both years each quarter improved (or worsened) on the quarter before.  You can start to see statistical trends.  This type of chart is called a Year Over Year chart or YOY chart.  It is also known as an Index Year Ago IYA chart and is used a lot in sales and statistics.  In short, a YOY chart is obviously more powerful than a standard bar (or line) chart.

To plot this type of analysis, you simply need to perform a simple calculation.  Imagine 2003 Q1 is in cell B4 and 2004 Q1 data is in cell B8.  The calculation would be =B4/B3-1.  This will give you the percentage change.  To make the chart easier to read, you could also move the X axis to the bottom or top of the chart.

final chart

You can download the sample excel sheet YOY chart.  I hope you find it useful!

Alesandra Blakeston

How to display survey results in a polar area chart

survey

A friend of mine recently did a survey on her project.  She asked her team what they thought about certain aspects of the project, giving them the choice of “Poor”, “Satisfactory”, “Good” and “Excellent”.  She wanted to be able to see at a glance how everyone had responded to each question and started creating bar charts with the name of the team member at the bottom and giving “Poor” a score of “1” and “Excellent” a score of “4”.  She then ran into the problem that the survey was confidential, so she had to remove the names.  Unfortunately, the resulting graph was not so interesting.

She had the idea to create pie charts, but this was not so easy to view the data.  So, we played around a little, visualising the data in different ways until I suggested a kind of a radar chart called a polar area chart (amongst other things).  The more filled the chart, the more the team members were satisfied.  While I am sure that there are probably better ways to show the data, this is the one we went with, so I thought I would post it here.

Qs
In addition, she had to send the results to her manager, so rather than having a series of charts, I suggested having one with buttons and a little vba to adjust the chart to show the different questions

vba buttons

How is it done?

Step 1 Set up the data table

spikyThis type of graph doesn’t exist in excel, so we need to set the data carefully beforehand.  A normal radar chart in excel, looks spiky as shown to the right:

To create the wedges as shown in the previous chart, we have to repeat each line of data several times.  In the example data sheet (you can download the sample excel sheet here), I have 3 questions and the responses for each of the 25 team members are repeated 10 times.

To set up the labels, we need another column.  In the centre of the replicated rows, type the label.

To set up the vertical lines as shown in the previous chart, we need to put the maximum number in the first row of each group of replicated rows.

So for each group we should see something similar to the table below (showing the responses for the three questions for the first 2 team members):verticals

Step 2: Create the first graph

Click on Insert > Charts > Other Charts > Radar > Filled radarInsert chart

Then add the Q1 column as the first data series.

Add the Verticals column as a second series.

Click on the edit button to add the Labels column as the Horizontal (Category) Axis Labels

select dataPress the OK button to finish.

Add a title e.g. “Question 1” by clicking on Chart Tools > Layout > Chart Title > Centered Overlay Title.

Then re-position the title as you wish.

Q1Step 3: Add the vba

Since all we want to do is update the series 1 and change the title each time, the vba is quite easy.  First though, let’s create the buttons.  Click on the Developer tab > Controls > Insert > Button.

button

Draw the button on the page.

Right click on the button that appears and select “Edit text”.  Change the text to “Question 1”.

Copy and paste the button two times, editing the text of each button to “Question 2” and “Question 3”.

Then on the developer tab, click on the visual basic button to launch the visual basic editor.

Copy and paste the code below into module 1.

Sub Macro1()

‘ Macro1 Macro

ActiveSheet.ChartObjects(“Chart 1”).Activate
ActiveChart.ChartTitle.Text = “Question 1”
ActiveChart.SeriesCollection(1).Values = “=Sheet1!$d$2:$d$249”
ActiveChart.SeriesCollection(1).Select
With Selection

.Fill.ForeColor.SchemeColor = 37
Range(“A1”).Select

End With

End Sub

Sub Macro2()

‘ Macro2 Macro

ActiveSheet.ChartObjects(“Chart 1”).Activate
ActiveChart.ChartTitle.Text = “Question 2”
ActiveChart.SeriesCollection(1).Values = “=Sheet1!$e$2:$e$249”
ActiveChart.SeriesCollection(1).Select
With Selection

.Fill.ForeColor.SchemeColor = 23
Range(“A1”).Select

End With

End Sub

Sub Macro3()

‘ Macro3 Macro

ActiveSheet.ChartObjects(“Chart 1”).Activate
ActiveChart.ChartTitle.Text = “Question 3”
ActiveChart.SeriesCollection(1).Values = “=Sheet1!$f$2:$f$249”
ActiveChart.SeriesCollection(1).Select
With Selection

.Fill.ForeColor.SchemeColor = 25
Range(“A1”).Select

End With

End Sub

Obviously, you can edit the names of the ranges, the sheet names, chart titles and colors as you wish.  Each macro (there are three) is very similar, just the range shown and the chart title changes.

Finally, right click on each button and assign Macro 1 to Question 1 button and Macro 2 to Question 2 button etc.  Test them, to make sure that they work!

Add finishing touches to your sheet, by removing grid lines, making the data look interesting etc.

What do you think?final

You can download the sample excel sheet here.  Enjoy

Alesandra Blakeston

Create an arrow chart

I’ve already posted how to make a bridge / waterfall graph where you can show with up and down bars the positive and negative impact of each category against the final sum.  However, what if you simply wanted to show the progress from one year to the next (both positive and negative) of completely different data sets that have no accumulate effect?  Perhaps using up and down arrows?  You can download the sample excel sheet herearrows

Step 1: Set up your data table

Imagine you have the following data table:initial table

As you can see in the table, the first value has fallen from 2011 to 2012, the other 4 have increased.  What we want however is to create several new columns as shown below:new columns

In essence what we will eventually create, is a stacked bar chart.  We’ll use custom shapes to create the arrow heads, and columns to create the bodies of the arrows.  In order for the arrows to float, we will also need a Blank column.

Decide the default size of the arrow head.  I have chosen 0.05, but you can alter this to meet your needs. I have placed this value in cell J2.

In the first row of the “Blank” column type in the formula: =MIN(B2,C2)-IF(B2=C2,$J$2/2,0)  Basically this formula will find the minimum value of the “2011” and “2012” columns, then if the two are identical, remove enough space for the arrow head or if they are different remove nothing.

In the first row of the “Down head” column type in the formula: =IF(B2>C2,IF(B2-C2>2*$J$2,$J$2,(B2-C2)/2),IF(B2=C2,$J$2/2,0))  Again we have an IF statement.  This checks to see if the difference between the values in the “2011” and “2012” column is positive or negative.  If positive, it will return a value of zero, if negative it will calculate the size of the DOWN arrow head series (a value between 0 and 0.05 – the maximum size of the arrow head).

In the first row of the “Down arrow” column type in the formula: =IF(B2>C2,B2-C2-E2,0)  This will create the series we will use to show the down arrow body.

In the first row of the “Up arrow” column type in the formula: =IF(C2>B2,C2-B2-H2,0)  This will create the series we will use to show the up arrow body.

In the first row of the “Up head” column type in the formula: =IF(C2>B2,IF(C2-B2>2*$J$2,$J$2,(C2-B2)/2),IF(C2=B2,$J$2/2,0))  Again we have an IF statement.  This checks to see if the difference between the values in the “2011” and “2012” column is positive or negative.  If negative, it will return a value of zero, if positive it will calculate the size of the UP arrow head series (a value between 0 and 0.05 – the maximum size of the arrow head).

first row calculations

Once you’ve added the formula for the first row, copy and paste the first row to fill the other remaining rows.

Step 2: Create a stacked bar chart.

On the ribbon, click on Insert > Charts > Column > Stacked Columnstacked column

Right click on the chart area that appears and click on Select data

Add the five columns, (Blank, Down head, Down arrow, Up arrow and Up head).  Then add the horizontal axis labels and press the OK button.select data source

A chart similar to the one below should appear.chart 1

Step 3: Format the chart

First, delete the legend, you will not need it.

Then left click on the blank series and press Ctrl+1.  Change the fill to none and press the close button.format data series

You should now have only the arrow and arrow head series visible on the graph.chart 2

Next we need to create an arrow shape.  First create a triangle for the head (click on insert > shapes > isosceles triangle), and then a rectangle for the body (click on insert > shapes > rectangle).isosceles triangle

Format the triangle and rectangle as you wish.  For example I have chosen a grey fill with no border for both.

Then select the triangle and press Ctrl+C.  Then select the down head series (shown red in the graph above) and press Ctrl+V.  The graph should update automatically:chart 3

Repeat the process for the rectangle and the down arrow series.chart 4

Then create a duplicate of the rectangle and triangle and format them as you would wish the up arrows to appear.  This time I chose a blue fill with no border.  Use Ctrl+C and Ctrl+V again on the up head and up arrow series accordingly.

Then format the chart as you wish.chart 5

You can download the sample excel sheet here.  Enjoy

Alesandra Blakeston

Create a spot matrix chart in 10 easy steps

When you want to compare data, radar charts can be a great tool.  However, they are not always so easy to read.

radarImagine you have the data set below and you want to compare each of the four areas against the 5 data parameters:

data

You set up the calculation in the overall column:

=AVERAGE(D4:H4)

where the data is found in columns D to H and the current area is on row 4.

Just looking at the data it is difficult to compare the data.  And when you look at the radar chart, with so many lines, it’s difficult to see each line.

Similar to my post on how to create a dot chart, you can create a spot matrix in Excel using the “Wingdings 2” font and formulae (download the spreadsheet here) original work by Chandoo.org:Spot Matrix

How is it done?

  1. First create a copy of your original table and remove the numberstable copy
  2. Then open up character map (click on the start button and type charmap in the program search box)character map
  3. Choose “Wingdings 2” as the font™š›œ˜
  4. Select and then copy the symbols as shown in the picture above in that order
  5. Paste the symbols into your Excel spreadsheet.  Mine are in cell B1.
  6. Then in the first line and first column of your table (where you want the first symbol to appear), type in the formula:
    =MID($B$1,FLOOR((C4-1)/5,1)+1,1)
  7. The MID function returns the characters from the middle of a text string, given a start position
  8. The FLOOR function rounds a number down to the nearest multiple of significance
  9. In other words, the FLOOR function returns a value between 1 and five.  If for example the FLOOR function returned a value of 2, then the MID function would display the second character from cell B1.
  10. Copy and paste the formula across the rows and columns of your table and the table is done.

 

Enjoy!

Alesandra Blakeston