Dashboard cards are a great way to group related data and make key information more accessible. The flexibility of Excel and XLCubed means there are a number of ways you might choose the incorporate a card-based design into your dashboards. In this blog we will consider one way to do this using XLCubed dashboard sheets and paged viewports.
Ever wondered how Santa Claus keeps track of the North Pole’s toy production all year round? He uses XLCubed of course! Continue reading “Christmas Dashboard”
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.
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.
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!
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.
#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.
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.
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.
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.
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.
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.
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.
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.