Tag Archives: Excel dashboards

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.

Bandlines in XLCubed

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.

 

Bandlines3

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.

 

 

 

 

Creating rounded corners in Excel – revisited

Today we’re revisiting one of our more popular guides, Creating rounded corners in Excel Tables, and have updated it for v7.1. When Igor Asselbergs was contemplating the value of round corners in design, he came to the conclusion that in many cases they added real value to the user experience.

The effect can be explained by the Gestalt Law of ContinuityGestalt is a set of rules based on research into perception psychology, and a very powerful tool for Excel table design. In table design this effect can help us to see the table columns as a unit.

The previous process to create rounded corners in Excel tables required quite a bit of persistence and patience. In Version 7.1, we’ve introduced a feature to enable adding rounded corners in a few seconds rather than several minutes, so while the theory is identical the implementation is much improved. Take this report showing sales KPIs, where we would like to add rounded corners to the header row in the table.

To do this we first highlight the required area:


Then we go to Extras -> Add/Edit Round Corners:


The Colours and Border thickness will be picked up from the selected cells. Select the corners to be made round (in this case the Top Left and Top Right corners):


Click OK to apply the borders

 

To edit existing corners which were created by XLCubed then you can just highlight the cell or range and Go to Extras -> Add/Edit Round Corners. The changes will be applied to the existing corners (or the corners can be removed by unselecting them).

It’s a simple addition to the product which would have saved us quite a bit of time in customer implementations over the years, and hopefully now does the same for our users.

Olympics Treemap

The 2012 London Olympics have now finished, and as a UK company we were pleased to see the games were such a success, and of course that team GB did so spectacularly well! We’re looking forward now to the Paralympics in a couple of weeks, and once the dust has settled there we’ll be shipping a new point release of XLCubed in September.

We’ll keep most of the changes under wraps for now, but one item which we are introducing is treemaps. The Olympic medal table gives us a nice opportunity to better understand the medal breakdown through the  new chart type. In XLCubed, treemaps can be produced directly from a cube or from a table held in Excel, as is the case here. The first example below shows the medals split by country and sport. The size of the rectangle depicts the total number of medals, and the colour shows the number of gold medals, the darker the colour the more gold. The numeric values list the total number of medals, then the number of golds. We can see the USA at the top, and that over half their medals came from swimming and athletics, with a bigger percentage of golds in the pool.

Any of the countries can be drilled into for a large view on their medal breakdown, not that we’re partisan of course… , but the view below is for Great Britain (GBR) where the particularly good showing by the cycling team stands out.

Taking a look at the same data split first by sport and then country, it’s easy to see the countries dominating the medals in each sport, and to delve into more detail by sport where required.


 Drilling into Athletics we can see that USA won most medals, and also most gold. Great Britain had just the 6 medals, but 4 were gold and hence the darker colour on their tile.

We’ll be making an interactive version of this available over the next few days.

 

 

A snappy fix for layout problems in Excel

Have you ever tried copying parts of one workbook to another and been restricted by column widths?  Or maybe you’re almost done with a report layout only to find that the last table you need to add has 4 columns, where there is only room for 3?  Today we’re going to show you how to use Excel’s Camera tool to get around any Excel column width limitations to achieve your dashboard goals!  Here we have an Excel heat Map on a separate sheet in our workbook.

It has been inserted into the dashboard below where the first thing to notice is the workbook’s  variable column widths, in particular columns J and K.  If we had just inserted our heat map as it was, the column widths in our dashboard would determine the width size of the heatmap.    Instead we used Excel’s camera tool to insert our heatmap sized at exactly what we wanted, regardless of the destination sheet’s column widths.

 

We follow these simple steps:

  • select  the heat map in the source sheet
  • click the Camera Tool icon
  •  navigate to the destination sheet
  • click and insert exactly where you want

The Excel Camera Tool is also a great way creating dynamic screenshots of particular groups of data.  The Camera Tool takes a picture of a selected area, and you can then paste that picture wherever you want it. It updates automatically, and because it is a picture rather than a set of links to the original cells, any formatting or data change in the source is automatically reflected in the picture.

The heat map chart source figures have been updated to show Europe’s higher sales – as you can see Europe now has the greater sales:

 

The dashboard heat map has updated automatically to reflect this value change.

 If you can’t see the Camera Tool on your Excel menu you can easily attach it to your Quick Access Toolbar by performing the following steps:

  • Click the File Tab
  • Click Options
  • Choose the Quick Access Toolbar Option
  • In the ‘Choose Command From’ dropdown, select Commands not in Ribbon
  • Find the Camera Tool from the alphabetical list of commands and add it to the Quick Access Toolbar.

2011 Dashboard Competition

A slight departure from the normal blogging to let everyone know about the latest developments in XLCubed and to talk about a new dashboard competition with the chance to win an iPad 2!

Dashboard Design Competition

XLCubed are sponsoring Dashboard Insight’s first dashboard design contest. The competition is based on a provided data set, and we’d encourage as many as can to enter.

We believe that XLCubed offers a class-leading dashboard development environment, with fine grained control over chart and table sizing, and we’re looking forward to seeing some great dashboards. Take a look at some of our previous winners for inspiration.

Don’t forget that this blog also contains lots of helpful information that should help you come up with a great dashboard design.

We’ll provide entrants with the sample data set in a local cube format to fully exploit the strengths of XLCubed. Entry is open to customers and non-customers alike, and your dashboard skills can win you a shiny new iPad 2. Good luck if you choose to enter.

 

XLCubed v6.5

Version 6.5 is due for release in early October. Originally scheduled as 6.2, we decided it contains so much over the current version that it deserved a bigger billing. New for 6.5 are:

 

  • iPad / iPhone app – XLCubed web reports have always worked on smartphones and tablets. However our app brings an intuitive iPad optimised user experience to report navigation and selection.

  • Mapping – Integrated point and shape based mapping in Excel and on the web.
  • Scheduling – email delivery of XLCubed web reports by pdf or Excel. Schedules can be controlled by period, or by data exception.
  • Sharepoint WebPart – customers have been using XLCubed Web reports in SharePoint for a number of years, but we now introduce a dedicated WebPart to make the process simpler and provide greater flexibility and depth of integration.
  • Away from the headline items there are a number of significant smaller enhancements which make 6.5 another big step forward for us. We’re looking forward to bringing it to market. For an early test drive, contact us along with your specific area of interest at support@xlcubed.com.

Lastly we’d like to welcome Cardinal Solutions Group to our partner program. Cardinal operate in North Carolina and Ohio and are one of a select few Microsoft Managed Partners in the U.S. East and Central Regions. We look forward to working together with new and existing customers.

Small Multiples on River Quality

The phrase small multiple was popularised by Edward Tufte, and has become a generic term for a visual display using the same chart or graphic to display different slices of a data set. Their close positioning and shared scale make comparisons very easy and shared trends or outliers can be quickly spotted. Various other terms are also used to describe this charting approach, or specific aspects of it, including Trellis Charts, Lattice Charts, Grid Charts and Panel Charts.

The most common use case for small multiples is separate line charts to compare trend across a large number of varying elements. Placing them all within one chart would cause either a ‘spaghetti chart’ , or lots of occlusion as shown in the comparison below. Here we use a standard Excel line chart, and an XLCubed small multiple to chart the same data. Separating the charts while keeping a consistent axis scale makes for a much easier comparison than in the single chart.

We took a slightly different approach when using small multiples to take a look at differences in river water quality across regions of the UK. Our source data was not absolute numeric values, but 14 years of results categorised into four bandings (bad, poor, fair and good). We wanted to provide a ‘one-pager’ which gave a feel for the trend within each region, but also access to the annual breakdown of the different water qualities.

In the end we settled on a Small Multiple display of 100% stacked columns as shown below.

A percentage base seemed a sensible way to approach the data, as different regions will have differing numbers of rivers and of samples taken. Using this approach we’re able to see a comparison of the relative water quality rather than dealing in absolutes.

The user selects a geographic area of the country to view the regional breakdown within the selected area. The water quality for a particular year can be analysed by locating the region, and the specific year to see the percentage breakdown for each of the four categories.

The colouring of the 4 categories was chosen to aid ‘at a glance’ recognition of the overall water quality by region, and also of the trend. Dark blue signifies bad quality water (opaque), and light blue signifies good quality (think ‘you can see right through it….’).

So to read the display overall, or for trend:
• Dark colour signifies water quality problems.
• Light colour signifies good quality water.
• Reading left to right, increasing colour saturation shows declining quality over time.
• Reading left to right, decreasing colour saturation shows improving quality over time.
• Any region can be zoomed in on to see a larger chart and understand the breakdown in more detail.

Fairly quickly, and from just this one display we can draw a number of conclusions as below:
• Across the region, as a broad brush summary, water quality has improved since 1992.
• Doncaster has shown strong and steady improvement.
• Kingston upon Hull has the worst quality overall in the region, and varies significantly year on year.
• If you’re off for a swim in a Yorkshire river, Richmondshire looks a good bet!

We’ve designed a pre-set view in this case to work for the data in question, but the small multiple concept is also very powerful when interactively exploring data. A picture can tell a thousand words as they say – take a look at our youtube videos on small multiples: Video1 Video2

 

Something on the Horizon

We had an interesting scenario while helping a customer extend an existing Excel dashboard.

We had recently performed some work to solve some performance and design issues they had with their existing Analysis Services cubes. They now had more of their underlying data available and the ability to query longer periods without the performance hit (a year’s worth of data vs 28-days).

They wanted to make the most of this by charting changes in daily sales data over the previous 12 months, broken down by their four main business groups. Ideally the chart would become part of the existing Management Report, the difficulty was the lack of report real estate to add the extra information. This is something we have all come across previously and of course typically solved by using In-Cell charts.

Plotting the data on an Excel chart in the space available would give us this:

 

 

Converting to Sparklines gave us a slightly better view, but given the number of data items being plotted still not ideal.

 

 

Luckily our customer had recently upgraded to V6.1 of XLCubed so we were able to use one of our newest incell chart types: SparkHorizons. There is a good explanation of Horizon charts as part of the research paper: Sizing the Horizon: The Effects of Chart Size and Layering on the Graphical Perception of Time Series Visualizations and Stephen Few has covered them previously.

Essentially a line chart is split into colored bands – degrees of blue for positive numbers and degrees of red for negative numbers. In XLCubed this is 3 bands of each colour. The separation of the vertical scale means that horizon charts can be a lot more effective than standard sparklines where the scale of the numbers vary significantly, but you still want to retain a common scale view.

In this case plotting the same data as horizon charts makes things a lot clearer:

It now becomes quite clear when sales a trending up vs down. It’s also possible to flip the negative values so they appear on the same direction as the positive values:

 

We are always looking at ways of developing and extending XLCubed, SparkHorizons were added because they looked like they had the potential to be useful where the data suited them, so it was pleasing to be able to use them in a real-world situation.

It’s also worth mentioning that although, in this case the data came from Analysis Services Cubes, because they are available as Excel formula they can be used to plot any Excel data, here’s an example of the formula:

=XL3SparkHorizon(Sheet1!$V$2:$V$262,Sheet1!E10)

This will plot the data from Sheet1!$V$2:$V$262 as a SparkHorizon graph in Sheet1!E10.

 


“Prev” and “Next” in XLCubed Slicers

We’ve been asked a few times in the last couple of months if we can build a ‘Previous / Next’ selector for date hierarchies, which allows the user to quickly navigate sequentially through months or days. The answer is of course ‘yes’,  otherwise it would be a very short blog..

One of the key strengths of XLCubed is it’s tight integration with Excel, and it means that with some creative thinking the answer is very rarely  ‘no you can’t’. Here we use a combination of our slicers, the xl3membernavigate function, and standard Excel formulae to produce a very effective selector for just this scenario.

A working example of this which connects to the sample bicycle sales local cube which we  ship with the product is available here or you can view the online demo here.

There are a couple of key things to note with this approach:

1) Slicers are typically populated direct from the cube, which makes them very flexible and dynamic. However a less well known aspect is that slicers can be driven from an excel range, and in this case that’s what we’ll be doing.

2) XL3MemberNavigate(). A fairly new formula which allows you to traverse a hierarchy dynamically in a multitude of different ways. Here we just scratch the surface.

To begin with we need to prepare a range of cells in Excel to base the slicer on, in this case the months, and we also need to ensure it’s dynamic and can change with the underlying data structure.  We need to prepare a table of similar structure to the below.

Cell B2 is the selection made by the user in the slicer, which we’ll come back to. The other columns in the table show:

Description:

Logical description of what the row is

Month:

The month available for selection, determined by whatever the user chooses in the slicer, and the Xl3MemberNavigate formula (Insert Formula – Member Navigate) .

Checked Month:

Validation checks on the month to cater for when the first and last available months are selected.

Slicer Display:

what will be displayed in the slicer dialog for user selection.

The first month uses MemberNavigate to get the first available month. This is very straightforward in the MemberNavigate dialog, and will insert a formula in this syntax: XL3MemberNavigate(1,”[Time]”,”[Time].[Month]”,”FirstMember”). Last month is achieved the same way, but using ‘lastmember’.

Previous and Next are again achieved using MemberNavigate, this time the syntax will be:  XL3MemberNavigate(1,”[Time]”,SlicerData!$B$7,”Previous”).

Displayed month is simply what the user has chosen in the slicer.

 Adding the slicer:

Add a slicer from the XLCubed ribbon (or insert slicer menu in 2003). On the selection tab, choose ‘slicer range’ and select C5:D9 on the table shown above. Then set the slicer Type to be buttons. Lastly, on the settings tab, set the slicer to update cell B2 on the SlicerData sheet.

Optionally, you can also name the slicer and choose to show a title bar, as we have in this example.

On inserting the slicer, you’ll need to resize the control itself, and possibly also the size of the buttons if the data member names are long.

You should now have a slicer which enables Prev/Next selections, along with first and last.

Using the slicer in a report

The slicer isn’t currently connecting to anything, or changing filters within a report. To do that, as it’s not directly connected to a hierarchy in the same way as a standard slicer, we need to go via the excel cell which it updates. So any XLCubed grids or formulae need to reference the cell which the slicer outputs its selection to, in this case in this case SlicerData!$B$2.

In our example we’ve just connected one grid, but there can be as many as required. Our example also gives some sales and costing detail for the main product categories. We also use in-grid sparklines to give a feel for the trend, and these can be drilled or sliced and diced in the same way as a standard grid.

The working example can be downloaded here, or a similar version published to XLCubedWeb used online here.

 

 

Heatmap Tables with Excel – Revisited

We’ve revisited one of our more popular guides Heatmap Tables with Excel as they can be a very effective way of presenting data on a dashboard, and have now updated it for Excel 2010…

This Heatmap Table is designed to show you the revenues and the discounts of a company over the course of one year per product group. The size of a bubble shows the revenue made in a particular month and the bubble color shows the discount rate given. The discount rate has been encoded as a range of green colors, ranging from a light green, for low discounts to a dark green for high discounts. The years and product totals are shown at the right and bottom as an integrated part of the table.

Tufte often talks about the integration of numbers, images and words; I think he’s quite right. A way to achieve this in Excel is to integrate charts into tables, so called graphical tables, a very effective means to show “More Information Per Pixel“.

The heatmap table is based on a regular Excel bubble chart. To integrate a bubble chart into a table the bubbles are positioned in a matrix that has the same row and column layout as our table.

 

 

 

 

 

 

 

 

 

 

 

 

 

In our case we generate a data series table with one column for the X-Series going from 1-12 for January – December and one column for our Y-Series going from 1-8 for our 8 product groups and one column for revenue.

In the sample spreadsheet we’ve setup some simple excel formula to translate data from the classic grid layout:

to the required format:

Now we can insert the bubble chart:

 

To ensure that the charts fit exactly into the table grid we set Min/Max for the X axis to 0.5/12.5 and for the Y axis to 0.5/8.5. Excel would calculate much larger auto scales otherwise. Also set the Major units to 1 so we can use that later to set some grid lines.

 

Now we remove the legend, the X and Y axis, maximize the plot area and align the chart with the Excel table. As the bubbles are initially too large we have to make them smaller. To control the bubble size go to Data Series Options and scale the bubble size to 50%:

 

This already makes a nice bubble table you could use to reproduce the Twitter Charts.

For the grid lines format your table headers and grid lines with light gray grid lines. Resize the plot area, remove the border and re-position the chart so that the chart and the table grid lines align.

To create the heatmap with different colored bubbles we use the fact that by default Excel does not plot data points for #NA values.  For the heatmap we overlay 8 bubble series, one  series per green shade, and show a revenue bubble only if the value fits into the value range that corresponds with a green shade of our color ramp, otherwise we show #NA.

We divide the range MAX(Discount)..0 into 8 groups to define the colours.

The data series columns use the following formula to test if a discount value corresponds with an interval / colour shade:

=IF(AND($E7>I$6-Step,$E7<=I$6),$D7,NA())

The formula returns the revenue, if the discount values is in the interval defined in the column header I$5.

 

 

Now create the eight data series so that the bubble size refers to the eight columns in the data table:

 

And use the Excel chart styles to pick a colour range – make sure you  remove the border from the chart area.

 

 

And you could use the chart styles to quickly switch between different colours – or customise each series to refine the colors.

You can download a starting point for these files here: HeatmapSample.xlsx. Most of the formulae should adapt to data values that you can feed into the data sheets, including data straight from Analysis Services if using XLCubed grids or formulae.

You can see an interactive version of the Heatmap here – we added a link to some cube data, some Slicers for driving the parameters and then published to XLCubedWeb.