Dynamic Dashboards In Excel – Tutorial

In this post we will walk through how to use XLCubed’s dashboard sheets to create a responsive dashboard within Excel, that will work on any device!

Create a dashboard sheet

Add a dashboard sheet to the workbook using the option under the Visualise menu in the XLCubed ribbon. Adjust the number of rows and columns in order to provide more flexibility in layout. Initially, we are designing a dashboard for a desktop display, so all rows and columns are visible.

Use the dashboard ribbon to insert a title label. Two options to note that are used in this example is the ‘Auto size text to fit’ and vertical alignment of the text. This means that the text will size to fill its container and also be vertically centred.

Dashboard labels can also have background colours and be horizontally aligned, providing great flexibility in design – why not try creating a coloured header bar that spans the full width of the screen? Or even combine it with a viewport showing your company logo?

If you require links and navigation to be a part of the workbook, it is also possible to create a navigation bar on the dashboard.

Slicers

We will create two slicers that filter all the data displayed in the dashboard. Simply add from the dashboard ribbon, and set up as you would for any XLCubed slicer (you can read the documentation here). By default, dashboard items are automatically linked together; this means that whenever we add a new item to the dashboard, it will automatically be filtered by the slicers.

Small Multiple Charts

A large feature of the dashboard is XLCubed Small Multiples. These are animated interactive charts which can also be drilled in to, displaying charts in a matrix layout.

Highlight the area on the dashboard where chart should be, and select the Small Multiples menu item from the ribbon. This inserts a chart and opens the task pane from which we can set up the chart just as we would on a normal Excel sheet. The slicers will have already populated areas of the taskpane (auto-linking!) with their hierarchies. Move the hierarchies to the different chart areas and make member selections as needed. For example, on the categories (x-axis) place the date hierarchy and show children of the Dashboard Selection to give a breakdown of the selected date.

Add additional hierarchies to the chart as desired.

Treemap

In a similar way, add a treemap to the dashboard. Again, configure this in the same way as you would on a standard Excel sheet. To change the colour and display of the treemap, go to the ‘Appearance’ tab in the editor –  here you can set the gradient used, as well as the label font etc.

The treemap is split by geographical region. Since it is still linked to the rest of the dashboard, it also acts as a filter for the Province hierarchy (clicking on a square area updates the rest of the dashboard).

Grid

In the remaining space, add a grid from the dashboard ribbon. Place the desired hierarchies on columns and rows – if any of these hierarchies are already a part of the dashboard, the members will automatically be populated by the dashboard selection once the grid has been inserted. You can then go back in to the grid editor to make child/sibling selections etc. Any remaining hierarchies in the dashboard will be added to the grid’s filters.

Adding a grid in this way creates a new sheet in the workbook which holds the grid, so the grid’s properties and formatting can be edited here. The object on the dashboard is a viewport which points to the named range containing the grid.

Freeze panes are also automatically created so that the grid is still easily readable when scrolled.

Here you can also use the usual grid formatting techniques. In this example, we have increased the row heights, filled the column headers green, and applied conditional formatting to the values.

Creating targets for mobile view

You should now have a fully functioning dashboard! The elements resize whenever the screen size changes so that everything remains in view. However, this can actually make the information harder to read on small screens. We will therefore create different ‘views’ (a.k.a targets) of the dashboard in order to optimise the display on small devices.

Add a new target from the dashboard taskpane. You can set your own dimensions, or simply use one of the presets (mobile portrait, tablet landscape etc.). Select the newly created target from the dropdown in order to bring it in to view. The sheet will have automatically laid out the dashboard elements. It is likely that you will want to change this default layout – you can hide items in the taskpane initially in order to make the dashboard easier to work with.

Set the number of rows and columns. Since we are designing the view for a smaller device, it will be difficult to have all the items in displayed whilst maintaining readability. You can therefore set the number of visible rows to a number less than the total. The dashboard will then be scrollable.

Now turn the visibility of the dashboard items back on and position them in the desired order.

If the targets don’t change as expected when the screen resizes, try editing the target and increasing/decreasing the lower and upper dimensions.

‍