Data Dashboards

Wednesday 10th January 2024

Elegant Data Visualization built in Excel

Mullen Lowe are a creative advertising agency which produce high global advertising campaigns. They required a series of dashboards to be produced in Excel for a wide range of data for their Brands which were to be used at a senior level within the company. Each dashboard was to have 4 or more components and the overall look of the dashboards had to have a very professional look which shied away from the default Microsoft charts and tables.

Complicating matters was that the data came from a wide range of sources and was not in a consistent format. For example there was no guarantee that the data in a column would be the same for the next data upload, so a method to read the column headers would have to be built.

The Dashboards themselves would require individual filtering for specific components, as well as requiring some filters to apply for all dashboards, no matter on which dashboard they were set from. Furthermore, some components required specific functionality, for example being able to alternate between the chart or a table of data.

An additional requirement was that two types of dashboards were to be produced from the data. A “Key Brands” dashboard showing the data for just one brand and a dashboard for senior management called the “Super User” version. The versions differed by having different components as well as the “Key Brands” version showing only individual brands and the “Super User” version allowing a filter to select specific Brands.

Finally it was also required that specific components could be exported as a PDF or into a separate Excel sheet as required

After a consultation meeting, it was decided that the solution would be to create an Excel workbook which allowed the user to upload the report data. This would become the “Master” document. Once data was uploaded and checked, the user could then produce the “Brand” or “Super User” versions as required.

Main Menu

A simple menu was created which allowed the user to upload data, perform checks and create the outputs. In order to keep with the clients request to move away from the typical Excel look, icons were used as buttons rather than the standard form ones.

Example of part of the main menu

Data Loading

When data was loaded from external sources, it was required to transform the data in order to store it in the workbook. In most cases this could be fully automated, however some data tables were very complicated with data from one month appearing in a different column the next. In order to facilitate this and not have the user need to contact Xlteq for support each month, the user was provided with a simple table which allowed them to specify the name of the column. This column name would be searched for when reading the data and, if found, the appropriate fields in the output table could be populated

Example of data column header information

The data table below will populate the Year and Period fields with the appropriate information if the Data Table Column is found.

Dashboards

The dashboards were specifically designed to have a non-Excel look to them. A striking black background with bold colours, accented with a light glow effect was decided upon. As each year was given it’s own colour, the first problem to overcome was ensuring Excel kept using these colours when the data changed, as Excel would often revert to it’s own standard set of colours. A simple procedure was created which re-coloured the charts depending on the detected year. This colour scheme was also updatable by the user so that any new years not yet given a colour could be added.

Example of Dashboard 1

This first dashboard incorporated some heavily modified Excel charts as well and it’s own bespoke components and functions. For example with the bar charts we are able to alternate between showing the charts and the underlying data by a simple press of a button

With some charts, where the data is too much to display nicely all in one go, a bespoke function was added to allow the user to “scroll” through the data. In the example below the use can press an arrow key to change the data from the top 5 results to the next 5. The code also detects if a new scale is required to display the results clearly (for example using a scale unit of millions or thousands)

Even where Excel had charts which could be used, it was decided in some cases to manually create the output. For example whilst excel has a pie chart as part of the charting tool options, we would require 9 charts to be created, each one needing to be populated with data, refreshed and coloured. In this case it was easier to build the pie charts using chape objects which allowed a greater control over how the charts were displayed and only requiring a small amount of code to do so.

Other styles of charts include a combined Bar and Line chart which has the ability to filter out data independently of any other filters which have been selected by the user.

Historic share price information is also displayed using real data taken directly from the stock exchange. This uses Excel’s inbuilt “STOCKHISTORY” function which is refreshed when the workbook is opened and the chart is automatically adjusted to take into account any high or low values.

Example of dashboard 2

Example of dashboard 3

Outputs

The ability to output each chart independently was a requirement. To facilitate this the user is presented with a simple screen which allows them to select the charts they wish to output in either an excel workbook or PDF format.

Additional Challenges

The users not only used Windows versions of Excel, but also used Excel on Apple Macs. This meant code had to be adapted to be usable on Apple Macs, including using class modules instead of Windows objects, the “#IF” statement to change the code to be Mac or Windows compatible and re-writing code to work on the Mac.

Overall

Despite being a very complex project in terms of data manipulation and design specification, Xlteq were able to meet the customer brief and provided a very high-quality end product with a unique look far removed from the standard Excel cut-and-paste templates.

Contact

Data Dashboards