Christmas Dashboard

Ever wondered how Santa Claus keeps track of the North Pole’s toy production all year round? He uses XLCubed of course! Take a look at the dashboard below….

Santa Christmas Dashboard Desktop

There are a few key features of this dashboard:

Charts and Viewports

The areas displaying charts and the grid are brought onto the dashboard using paged viewports. This allows us to create a viewport that displays a chart (which will scale with the size of the screen) and then add a label overlay to give a centred title of fixed size.

The paged viewports also have a white background colour and grey border, to contrast with the dashboard sheet which has a light grey background. This gives the appearance of a card based design.

Map-Driven Grid

In the top left is an XLCubed map that is plotting world countries. Clicking on the map outputs the selection to an Excel cell which filters the grid in the bottom left.

The label above the grid uses an Excel range as part of its text so that the country that has been selected is visible to the user.

Responsive Design

Santa can even view the dashboard on his phone while he is out in his sleigh…

Santa Christmas Dashboard Mobile

Merry Christmas!

Dashboard Designs: Responsive Navigation Bars

Many would believe that Excel is a static environment and not suited for making responsive mobile reports. XLCubed users know better. With XLCubed’s Dashboard Designer, it is possible to create dashboards that are not only interactive and data-connected, but also responsive to fit any device.

If you are new to XLCubed Dashboards, read this article to learn how to get started. For those that have used this feature before, you may be wondering how to make dashboard designs that are both user-friendly and best display your data.

One often needed dashboard feature is the use of a navigation bar. For small devices in particular, it is difficult to portray everything in one screen, so having a system in which users can tab through pages is useful.  With XLCubed, reports are designed in Excel which inherently has tabs. As useful as this may be, for mobile reports this is not the most intuitive. Users expect simple navigation clearly displayed at the top of the page.

Below is an example of a dashboard that uses a responsive navigation bar.

Desktop view:

 

Phone view:

XLCubed dashboard portrait phone

The dashboard uses a paged viewport that displays an Excel range with the navigation design. Each page has a different viewport pointing to a different range so that the current page appears to be highlighted.

The viewport remains responsive to screen size by using the ‘scale to fit’ with ‘lock aspect ratio’ option and setting the background colour of the viewport to be the same as the colour of the navigation bar. This means that if the screen width is longer than navigation bar display range, the viewport colours in the remaining space so it still appears to fill the screen.

For a better fit on a portrait view, the dashboard uses a different design optimised for a narrow screen. Then the ‘phone portrait’ target hides the original nav bar viewport and instead display a new viewport which displays this range.

For a step-by-step tutorial on how to recreate this design, read this article.

Using Slicers for Sheet Navigation

Slicers are normally used to change filter selections in a report, but a less well-known use is for report navigation, to allow users to jump to another location in the workbook.

Here I have a report with several sheets. I would like to hide all the tabs at the bottom upon publishing and just have a page navigation at the top of each sheet.

 To do this, I create a separate sheet containing the names of all the sheets that I want to be able to navigate between. Create an Excel slicer using this range that outputs the selected sheet to another cell.

I can now create an XL3Link that will link to whatever sheet is currently selected in the slicer. It uses an XL3Address that combines the outputted name with “!A1” as its link location argument.

=XL3Link(XL3Address(A8 & “!A1”), “Link”)

So now, changing the slicer and then clicking the link takes me to the selected sheet.

We would like the slicer to take us straight to the relevant sheet, without having to click the link after each selection. To achieve this, go back into editing the slicer and select ‘Activate XL3Link’, choosing the cell containing the link we just created.

Now, whenever the slicer is changed, the link is immediately activated and we are taken to the selected sheet.

I can copy this slicer into each sheet for easy navigation wherever I am in the workbook (right click on the slicer, select ‘Copy’ and then choose the location to copy to). Because each slicer outputs to the same cell, all the slicers stay in sync. I could of course also do this with a workbook slicer so it’s automatically present on each worksheet.

User control of dashboard layouts

Dashboard sheets were introduced with V9, primarily as a way to deliver mobile-friendly reporting with a responsive UI to auto-fit any screen size. Specific Targets which define the layout can be defined to optimise the layout for different devices and are automatically applied depending on the device type.

Another use-case which is less obvious but can also be very useful is to allow users to choose between a number of predefined layouts.

For example, on a specific report there may be just 3 or 4 slicers which are typically used, but occasionally users may need access to a much larger list of slicers to filter by. It would be a shame to clutter the report for everyone permanently with all the slicers as it makes the selection process less intuitive, and probably forces us to use only combo boxes to save space. Ideally we’d like users to be able to switch from a ‘Quick Slicer’ view to an ‘All slicer’ view.

Another example would be where users want to include additional dashboard items, or remove items to get a larger view of a data table.

These scenarios and others can be handled by giving users control over which Dashboard Target is active via a slicer.

In the example shown below the button slicer allows switching between a ‘Quick slicer’ view with the 3 primary slicers shown as list boxes, an ‘All Slicer’ view with all 9 slicers available as combo boxes, and a ‘Table View’ which maximises the space for the data table and removes the charts.


So how do I…?

Firstly, you’ll need to define the various Targets which you want the user to choose between (see here for the details).

Next you need to add a slicer allowing the user to select between Target layouts. This slicer will be based on an Excel range, and will output its selection to another cell which you specify. It’s easiest if the input range for the slicer exactly matches your Dashboard Target Names (otherwise you can use vlookups to cross-match). Of course you’ll need to enable that slicer on each of the targets to allow the users to switch views.

Finally, we can use the XL3SetProperty() formula to set the active Target for the dashboard, based on the output of the slicer we just set up. The syntax is:

XL3SetProperty(Object Type, Object Name, Property to set, value to set the property to)

The screenshot above shows the slicer and formula setup – hope it proves useful for some of you!

Icon-based Navigation and Filtering in XLCubed

Version 9 introduced an embedded icon library and XL3PictureLink, which together make the creation of icon-led navigation and filtering simple.

XL3PictureLink provides the same parameterised navigation capabilities which the XL3Link() formula has done for years, but with an added visual aspect. Users can choose from one of the thousands of icons provided, in any colour, or choose a custom image as required.

The images can then be used as an intuitive way for users to navigate to another sheet within the report, while passing a dynamic parameter to ensure the data is in-context. Alternately, where there are a small number of selection choices they can be used as visually appealing slicers.

Insert PictureLink is available from the Insert Formula tab on the XLCubed ribbon.

Click the drop-down and search for an appropriate image from the picture library – you can also specify the colour by clicking the Colour drop-down.

You then select the required image, and enter the destination cell location in the “Link to” box – this is the location where the user will be taken when they click on the image (can be the same sheet or a different sheet in the report).

You can also parameterise the image – “Value” is the content  which will be inserted into the cell specified in “Range to Set” (can be text or a cell reference).

To edit a Picturelink once it has been inserted, hold down Shift and then click the image.

Note that PictureLinks do not need to pass parameters – they can used as a simple link to another location:

Lastly, on web reports XL3PictureLink can also be used instead of the standard ‘Submit’ toolbar button.

There is more information on that here.

Some Excel BI myths debunked: #3 – limited dashboards

#3: Limited and difficult to Maintain Dashboards

Third on our list of common criticisms of Excel focused BI, is the limitations of Excel Dashboards.

“Excel dashboards are ugly, limited, and inflexible…”

It’s possible to build a truly awful dashboard in pretty much any dashboard tool. No tool is magic, ignoring the Doctor’s Sonic Screwdriver of course, and if you make bad design choices when building a dashboard the end result can be a mess. Similarly you can build a pretty decent dashboard in most tools. So even in base Excel with no additional software you can build a dashboard which looks good, and many people do.

In native Excel there are undoubtedly some limitations around the available chart types, and the handling of dynamic charting. However you do have the benefit of very fine grain control over the layout and positioning of tables and charts. The camera object also lets you break out of the fixed column width which is sometimes seen as a limitation.

XLCubed extends the core charts available in Excel with a rich library of in-cell charts, small multiple/trellis charting, mapping and TreeMaps. It means you can deliver more in Excel visually, rather than have to leave the environment totally. Dashboards mean different things to different people, for some a dashboard can be a table with a chart, but most contain significantly more than that. The example below uses a mixture of native Excel charting and XLCubed in-cell charts.

FinanceSShot

It’s based around a sample personal finance data set, and brings a lot of information together in hopefully a visually appealing and effective way.  If you want to build a highly formatted and relatively densely populated dashboard like this, it’s going to take more than a few minutes in any tool, no matter what the marketing says. In reality you’ll most likely struggle to get the exact layout in a widget based dashboard tool as you lose some of the fine-grain control over table and chart sizing which you have in Excel.

Dashboards can be fundamentally simpler than the first example, but require more specialised chart types like the example below. In this case it’s a dashboard built in XLCubed Excel Edition and published to the Web, looking at fuel pricing for a downstream oil company (fictitious data). It’s a ranked table of data for a selected county in Florida, and is then using an extended boxplot to display the price distribution in the market, and a map to show the Revenues and Volumes geographically.

RampMap

One major issue with Excel dashboards can be the maintenance. If it’s an Excel-only dashboard, bringing in the new data, and checking all the links can be a time consuming process. In an XLCubed environment the cube is updated behind the scenes and the next time you open the report you’ll get the updated data, the ongoing burden of maintenance is largely removed.

So in summary, Excel when well used, is a very good dashboard tool, and XLCubed extends the capability further still in terms of available chart types, flexibility and maintenance.

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.

2009 Excel Dashboard Competition Winners

Thanks to everyone who entered this years competition, again the standard was very high, and it’s always great to see the product being used so effectively. The entrants were extremely varied in both their style and subject matter, and made for a difficult decision. However I’m pleased to be able to announce the winners:

1) Ajay V Singh – Operations Dashboard for a Debt Collections Company.

The target audience are the CXO level execs of the business, aiming to provide a view of all the nerve points of the organization in a single unified interface that is portable and yet comprehensive.

The dashboard layout is dense but uncluttered and well thought through. Colours are well balanced, and allow the reds to draw the reader’s attention as intended.

Ajay’s background summary of the dashboard, with larger screen shots, will be available on our web site in the coming week.

Collections Dashboard Screenshot

 

 

 

 

 

 

 

 

 

 

 

 

 

2) John Munoz – Insights into Unemployment in the United States.

Using data from the bureau of Labor statistics, the dashboard gives a deep glimpse into the unemployment situation in the US. A large volume of disparate and tabular information is brought together in a single concise view, which aids understanding and adds real insight. The trends and demographic splits come through very well, and make for easy comparison.

John’s background summary of the dashboard, with larger screen shots, will be available on our web site in the coming week.

unemployment_dashboard_munoz

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3) Lisa Cunningham – Anti-Social Behaviour Dashboard

The dashboard is produced by the Research and Information Team at Leicestershire County Council as part of a suite of dashboards produced for the Crime and Disorder Reduction Partnerships. It is available to the public through the local web portal, which makes readability, and also the contact information provided vital. The dashboard aims to provide an at a glance view of the level and trend of ASB, and does an excellent job.

Lisa’s background summary of the dashboard, with larger screen shots, will be available on our web site in the coming week.

ASBDashboard

Excel Dashboard Competition – deadline extended

We have decided to extend the entry deadline through the holiday period, to 28th August.

As a reminder, the competition is for real world solutions (no sample data set), and judging criteria include:

  • Clean and clear organization
  • Effective table and chart design
  • A single-screen display, properly designed for the web, screen or print outs

See the competition page for more detail.

-Thanks to all of you who have already entered, the quality has again been good, and will doubtless lead to an interesting debate when it comes to choosing the winners. As we’ve extended the deadline if there are any additional tweaks you’d like to incorporate you can of course send revised versions.

Excel Dashboard Competition: Bank Dashboard

This blog post is the first in a series of blog post that features the winners of the 2008 Excel dashboard competition.

“A dashboard is a visual display of the most important information needed to achieve one or more objectives; consolidated and arranged on a single screen so the information can be monitored at a glance.”

Stephen Few, Information Dashboard Design (2006)

The dashboards were judged on the clarity and effectiveness of their design, particularly

  • Clean and clear organization
  • Effective table and chart design
  • A single-screen display, properly designed for the web, screen or print outs

Furthermore we honored the technical aspects of the dashboard, did it use effective (Excel) techniques for

  • The Dashboard layout
  • Data management, pulling data from a database or data warehouses
  • Data logic and calculation : YTD figures, variances, etc….
  • Dashboard delivery: Sharing the dashboard via PDF, the web or as an Excel Workbook

Today we will review the winning entry, Wades Stokes Bank Dashboard:

image


Continue reading “Excel Dashboard Competition: Bank Dashboard”