In the last few months we have significantly extended the charting elements within XLCubed. We wanted to get the product formally certified by IBCS, and I’m delighted to say we achieved that in early June. We’ve been aware of IBCS for several years now through some of our customers and partners, but it was really only at the beginning of this year that we started to look in detail, and it really resonated with us.Continue reading “XLCubed and IBCS”
Proper data visualisation is essential for digestible reports and dashboards. Business charting needs to not only portray a true representation of the data, but must also be intuitive and dynamic in its use. Any user should be able to click around or drill into the chart/report to find the view that they need.
XLCubed v9.2 introduced new and improved animated small multiple charts (read more in this blog). They are an excellent solution for displaying single and multiple charts that can be used both in Excel and Web. Here we will showcase some examples of how these can be used!Continue reading “Interactive Charting In Excel and Web”
One of the biggest improvements in 9.2 is undoubtedly in the area of interactive charting. We’ve hugely extended the capabilities of Small Multiples through a new charting engine which brings rich interactive Visual Analytics to Excel (and web, and mobile…).
The ‘Small Multiple’ concept of many charts with a shared axis is very powerful, but in some cases users just need a single interactive chart and 9.2 caters for both scenarios. We have added zoom controls, sliders and a play axis to help users quickly focus in on and further explore specific areas of interest within the chart.
Repeaters are a visualisation feature introduced in v9.1. They are effective when you want to repeat a formatted section of a report by one variable. They can save so much time as you don’t have to go through the tedious, error-prone task of recreating the same section many times by copying and pasting manually. Imagine the time you’d save setting the design up just once and have the repetition handled by XLCubed!
This summer English Premier league clubs spent more than ever before on player transfers, a staggering £1.47bn in total. Some spent a lot more than others, and while PSG are making the Financial Fair Play headlines globally, the EPL clubs as a group spent more than any other league.
There are lots of ways to analyse spending, and rather than write a detailed analysis or opinion piece (as I’d doubtless end up being biased), I’ve taken the opportunity to simply present the transfer activity in a few different visualisations and readers can draw their own conclusions. Continue reading “Charting the Premier League Transfer Window”
Well, the Rio 2016 games have finished and we now all need to find something else to watch on TV. As always at the Olympics there was plenty to entertain and inspire. After the London games in 2012 we blogged showing the medal distribution using Treemaps. We’ve updated that for 2016 below with the corresponding 2012 equivalent:
The charts are split by country, and then sport where the size of the tile represents total number of medals, and the colour saturation represents the number of Gold medals. We can see immediately that the US retains a significant lead over the other nations, and also that roughly half its medals overall were won in Swimming and Athletics. Great Britain and France have seen their relative medal positions strengthen in the four years. It’s difficult to see the breakdown for countries with smaller numbers of medals, but the interactive version can of course be drilled to additional detail and we’ll make that available in the coming weeks.
Looking at things split by Sport then by country it’s as below:
Athletics and swimming have the most events and hence the most medals and largest presence on the Treemap. USA dominates both categories across both London and Rio, with an even stronger grip on athletics in Rio. Elsewhere China rule the diving boards, winning 7 of the 8 events in Rio.
Team GB again did spectacularly well in Rio, and as a British company we can allow ourselves a slight bias in our coverage (a roundabout way of saying the remaining charts are just about the British team). Firstly we’ve brought the 2012 and 2016 data for GB together into one treemap as shown below.
While the mix of sports is slightly different, and in both games the team won medals across 19 sports, the core strengths remain fairly consistent. Despite that, there are some interesting movements. Gymnastics and swimming have shown the biggest improvements between 2012 and 2016. Cycling (all cycling disciplines grouped) had the same number of medals in total, but 2 fewer gold. Having said that when you start from such a high base even being close is success – when other teams are videoing your warm up / stay warm routines it’s safe to assume you’re doing something right!
Last but not least, a column chart showing overall GB medals by discipline across the two games – if you need a binary sport by sport comparison rather than contribution to total the classics still tell it best.
A common question that comes up in support for XL Cubed is how to add charts that look like a dial, or a gauge. Something like the below:
These are actually very easy to make and publish to the web, plus they have the further bonus of adding something different to make your reports look more professional.
Once you have your data ready, add a new doughnut chart and configure it to show the information you want it to.
This will give you a simple doughnut chart.
Next up, pick the cell that contains the information you want to show in the middle of the doughnut chart and reference it in another cell. For example, in the below example we have the two numbers that make up our doughnut chart in cells B3 and B4. Cell E3 contains the information we want to show in the middle of the doughnut chart.
As you can see, the formatting is different in E3 to the other cells. This is because we have formatted the cell to show the data how we want it to appear in the chart.
Once we are at this stage, it is just a case of transferring the number to the middle of the doughnut chart. You can do this by selecting the formatted cell, in our case E3, copying it and then paste special as a ‘Linked Picture’ anywhere in the worksheet (we will move it into the chart in the next step).
The ‘Linked Picture’ appears as a cell but it actually acts like a picture so, lastly, move the picture into the middle of the doughnut chart so it looks how you want it, then, right click on the new picture and select ‘Send to Back’
As the cell is a ‘Linked Picture’ Any changes you make to the cell you copied, formatting or data, will update the image.
Your Gauge Chart is complete! These charts also look good when published to the web.
So today’s blog is about adding Bump Charts in Excel using v8 XLCubed.
Initially a Bump Chart looks the same as a line chart – the difference is they plot the rank position rather than the actual value.
Let’s imagine that I sell a product in a marketplace with 10 other competitors. I may like to see how the rank position of my product and the competition changes over time to check if I’m gaining or losing market position. It’s a common scenario in pharma, where we have a good customer base.
You will usually want dates on the category axis so the trends are shown across time. The series then holds the items to be compared, in this case the products.
Our example has been set up with Measures on Headers, Product Categories on Series and Date Calendar on Categories. For more information on using Small Multiples in XLCubed please visit Small Multiple Charts.
The currently selected measure is Reseller Order Quantities (selected though the Measures slicer)
for the eleven months prior to April 2008 (selected through the Date slicer)
for a subset of products.
Looking at the bump chart you can see that I’ve selected Road Bikes and Mountain Bikes for easy comparison. You can quickly see that the rank position for Road Bikes dropped quite dramatically from May 2007, picked up again in September before dropping again in November and rising in December through to February 2008. The change for Mountain Bikes, on the other hand, was less dramatic, rising and falling slightly, steadying in February 2008 before dropping again the following month.
To create a bump chart just select Line – Bump as the Chart Type on your Small Multiple chart. The neat part is that all the rankings are worked out for you behind the scenes, without the need for lots of complex Excel gymnastics trying to work through the full result set month by month.
With the recent release of version 8 we’re going to blog about a number of the new features, starting with how to create a heat map in Excel.
Here’s a fairly large table showing sales for thirty six products across twenty six US states:
There’s a lot of data here but it’s not giving us any helpful information as the table is too large to see any pattern or comparison.
A heat map could be a useful way to give a quick visual picture of the spread of the sales volume. Let’s add a simple heat map, new in version 8 of XLCubed.
Select the data area in the table, and then from the XLCubed ribbon select the InCell-Chart group, and heat map:
As we have already selected the data area to be charted this prompt is already showing the correct cell locations.
Choose the formula destination (where the formula controlling the chart will be located), and the Chart destination (where the top left cell in the chart area will be located).
We can now define the look of the heat map in the Chart Format dialog:
We have set the low and high colours to define a blue colour gradient.
Outlying values could potentially skew the chart so you have the option to exclude these by setting minimum and maximum values. Select the icon to use, squares in our case, and the number of steps or bands to split the range of values into.
We have pre-arranged the Excel cell sizes to be squares, and this is the resulting heat map:
You can now quickly assimilate the spread of values in a glance, and note the higher sales volumes in Maine, Michigan and Missouri for Road, Touring and Mountain Bikes.
To alter the formatting of the chart simply double click on any one of the squares in the heat map, or on the chart formula to bring up the formatting dialog.
If you are not already a user of XLCubed you can get started with an evaluation of XLCubed by going to our registration page.
#4: no Maps
The next commonly listed criticism of Excel BI is the lack of integrated geospatial mapping. While for some reports maps remain an irrelevance, for others they can make a huge difference, with targeted advertising or awareness campaigns being the most obvious. While you can now use mapping in Power View, for most corporate office users that isn’t an option yet. XLCubed brings both point and shape based mapping to any version of Excel.
The example report below mixes mapping with the Small Multiples concept. The approach means you can have multiple identical maps which vary only by the time period. This helps you see change over time regionally, and also to make comparisons between the direction and pace of change by region.
You can zoom in and pan the maps as needed and they’ll stay in sync, and individual points on the map can also be set up as report selectors which update the rest of the report in sync.
So, mapping in Excel? – absolutely, right now and in any version.