XLCubed visualisation components (e.g. repeaters, in-cell charts) and charting techniques
//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.
//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.
//Continuing our discussion of common criticisms of Excel focused BI, let’s take a look at charting.
“Excel charts are static, inflexible and you need to start from scratch if you want to change them.“
The flipside is that everyone knows how to use them, and in reality many charts in business reporting are in effect static – the numbers being charted change, but the chart layout and number of elements being charted stays the same.
Of course there are cases when charts can vary considerably with the data, or perhaps you would like to be able to drill into more detail on the chart, or to quickly display multiple charts split by one variable. Excel charting can’t handle those scenarios, but XLCubed caters for it through Small Multiples. The example below depicts river water quality in different regions of England. It could be built in native Excel, but would be a painful and time consuming process. With XLCubed it’s a drag and drop process in our small multiple designer.
If the number of regions being reported changes, the number of charts being plotted will automatically stay in sync, and there is a direct data connection rather than having to maintain Excel ranges etc.
Sometimes with charting small is beautiful. Perhaps we just want the key numbers with a Sparkline alongside, or a bullet graph or bar chart to display actual to target. Native Excel 2010 and 2013 can handle the Sparkline, but not the ability to then drill the report and have the Sparklines extend, and there is also the issue of needing to bring the data itself into Excel before charting it.
XLCubed Grids can contain dynamic in-cell charts which build the charts as part of the query, and as such are drillable and remove the need to maintain a data range in Excel as shown below.
So XLCubed brings the type of dynamic charting being described to Excel, and provides a simple web and mobile deployment option.
//In early January this year Stephen Few introduced the concept of Bandlines. He identified a useful extension to Sparklines, making use of shaded or coloured horizontal bands to provide more information on the context of the trend line itself. See Stephen’s article on Bandlines and the thinking behind them for a detail description.
The Sparklines are ideal for showing individual trends in a small amount of screen real estate, and we use them extensively in dashboards, typically in a ‘visual table’. By definition Sparklines are small, and to make the trend easily readable, they are typically scaled individually so that each Sparkline uses the whole vertical axis. This means they do not give any impression of the scale of the numbers involved across different rows. It’s possible to use a common scale, and while sometimes that works more often it means many of the rows with smaller values are excessively flattened.
Bandlines address this by introducing horizontal shaded areas depicting the lower, middle and upper quartiles and the median represented by a line. The user can determine the context of the bands. The two most common examples would be plotting recent trend in the context of a longer period, or plotting individual rows in the context of the overall set of data being displayed.
We think Bandlines add real value, so hats off once again to Stephen, and we’re pleased to say that Bandlines are now available in the current version of XLCubed (see here for more detail).
The screenshots below show two examples, displayed in two colour schemes.
The charts depict historic margin by store. The ‘Banding across all stores’ charts show the 30-day trend for the individual store, set in the quartile context of the data for all 11 stores in the table. We can see that for the Gilroy store in row 1, while the margin has varied, it remained in the top quartile when set against all stores for almost the whole period.
The ‘Banding by store, 90 days’ charts show the individual 30 day trend, set in the context of the previous 90 days for the individual store. This helps provide much more historical context, but the line itself still focuses on the more recent trend. Stockton is probably most noteworty here as across the 30 day period it has dropped from the top quartile into the 1st quartile across the whole 90 day period.
We’d love to hear your thoughts (and also which colour scheme works best!), we will also be adding Sparkstrips in the near future so watch this space.
//V7.2 of XLCubed is released soon and we thought we’d take the opportunity to run through one of the new features that you’ll be seeing, Pareto Charts.
The Pareto Principle is often referred to as the 80-20 rule, that 80% of outcomes are attributable to 20% of causes. They are named after Vilfredo Pareto who lived in Italy in the 19thcentury and observed that 80% of the land was owned by 20% of the people. Pareto charts have both bar charts and a line graph where the bars represent individual values and the line represents the cumulative total.
So how do you use Pareto Charts from XLCubed? Very simply, within a grid you right-click on the column header to access XLCubed’s right-click menu, Grid Charts and Add Pareto Analysis.
Take this simple grid showing Reseller Sales for Product Model Categories for Canadian cities:
Right-clicking on All Products to Add Pareto Analysis brings up this window:
Click OK to return to the workbook – you will see that we have a chart showing that the top 9 cities provide some 80% of the sales.
You could also include the rolling total and percentage in your Pareto Chart.
Notice that we now also have some extra columns on the grid showing the cumulative total of all sales, the sales percentage per category and the cumulative percentage.
So that’s Pareto Charts – in a nutshell, an easy to use graphical tool which ties directly into dynamic XLCubed grids.
