Interactive Charting In Excel and Web

Proper data visualisation is essential for digestible reports and dashboards. Business charting needs to not only portray a true representation of the data, but must also be intuitive and dynamic in its use. Any user should be able to click around or drill into the chart/report to find the view that they need.

XLCubed v9.2 introduced new and improved animated small multiple charts (read more in this blog). They are an excellent solution for displaying single and multiple charts that can be used both in Excel and Web. Here we will showcase some examples of how these can be used!

Zoom Modes

XLCubed’s latest small multiples provide the ability to zoom in to areas of a chart for a close up view. These can be set in the Animation tab of the chart’s properties.

Slider

The slider zoom mode allows you to quickly slide between the categories on the chart’s x-axis. This can be especially useful when you have many categories on the axis, such as dates on a time series.

Interactive Excel chart of GBP to USD exchange rate over time

Zoom On Chart

With the ‘zoom on chart’ mode, you can highlight an area of the chart to focus on. This is can be particularly effective in scatter charts.

Scatter chart type showing sales vs profit
Drag the mouse to highlight an area of the chart to zoom in on.

Mini Chart

A mini chart also allows you to zoom in on particular x-axis categories. It displays a chart for the total of all the charts displayed (e.g. when there are multiple members on columns or rows) allowing users to then zoom in on particular areas of interest.

It also retains its display after zoom so you don’t have to un-zoom in order to zoom in on a different area (unlike when using ‘zoom on chart’).

Interactive chart with mini-chart zoom control

Diving Deeper

Charts are great for visualising trends and patterns in data. However, they can lack the depth and detail sometimes required. XLCubed provides various options to help you get the full picture…

Link To Other Report Objects

A great advantage to using XLCubed in Excel is the ability to drive reports and formula from standard ranges.

Small multiple charts can output any selection you make to an Excel cell. Grids, slicers, formulae and other charts can then be filtered by these outputs.

Interaction options in the small multiple properties form

Small Multiples can also be combined with an XL3Link or used to move to another part of the workbook for advanced report functionality.

Note: Small multiple charts on an XLCubed dashboard sheet are automatically linked to other dashboard objects – no need to output selections! This makes dashboard interactivity very simple to achieve. For complex selections, you may still wish to output selections which can then be used in formulae etc. You can change/remove dashboard categories or turn off auto-linking completely (read the documentation here).

View of dashboard with interactive chart that updates other dashboard elements
Clicking on the chart updates the other dashboard elements. You can read about how this dashboard was made here.

Labelling & Tooltips

Adding additional labels and tooltips can be a very practical way to quickly gain understanding of the numbers that make up a data point. Hovering over the chart displays a tooltip (this can be turned off if desired in the chart’s properties).

Additional measures can be added to tooltips when Measures is in the Values box – the tooltip option then becomes enabled at the top of the task pane. These can then be customised in the chart’s properties – use the placeholders %tooltip1%, %tooltip2% etc. to add the defined measures.

Read the details here:

Small Multiple chart with customised tooltip

More Excel Flexibility

Hopefully this post has shown how creating interactive charts in Excel is made simple with XLCubed. Design reports and dashboards your way to achieve advanced functionality, and share across your corporation via the Web.

Please do contact us if you have any questions or would like copies of any of the examples!

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.