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