Interactive Charts in Excel header

Interactive Charting In Excel and Web

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”
XLCubed New Small Multiple Charts

Visual Analytics for Excel

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.

Continue reading “Visual Analytics for Excel”

Eat, Sleep, Report, Repeat!

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!

Continue reading “Eat, Sleep, Report, Repeat!”

Charting the Premier League Transfer Window

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”

Rio Olympics – Medals Treemaps

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:

CBS16

CBS12

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:

SBC16

SBC12

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.

GBNI_1612

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.

GB_c_1612

 

 

 

How to gauge data through charts – Creating Gauge Charts

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:

1

 

 

 

 

 

 

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.

2

 

 

 

 

 

 

 

 

 

This will give you a simple doughnut chart.

 

3

 

 

 

 

 

 

 

 

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.

4

 

 

 

 

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).

5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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’

 

6

 

 

 

 

 

 

 

 

 

 

 

 

 

 

As the cell is a ‘Linked Picture’ Any changes you make to the cell you copied, formatting or data, will update the image.

7

 

8

 

Your Gauge Chart is complete! These charts also look good when published to the web.

 

Bump Charts in XLCubed

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.

BBC1

 

 

 

 

 

 

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)

BBC2

 

 

 

 

for the eleven months prior to April 2008 (selected through the Date slicer)

 

BBC3

 

 

 

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.

Excel heat maps made easy!

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:

 v8B1

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:

v8B2

 

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:

v8B3

 

 

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:

v8B4

 

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.

Some Excel BI myths debunked: #4 – no maps

#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.

 Map3

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.

 

Some Excel BI myths debunked #2: Inflexible Charting

#2: Inflexible Charting

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.

waterqualitysmalt

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.

DrillIncell

So XLCubed brings the type of dynamic charting being described to Excel, and provides a simple web and mobile deployment option.