Creating dynamic dashboards in Excel

Creating a live dashboard is a skill combining data sourcing and staging (arrangement of the data to serve the dashboard elements), queries (typically SQL, or smart Excel functions), visual design, user experience and layout, and business requirements to be fulfilled. My students learn how to create beautiful, business dashboards.

I recently offered an answer on Quora to the question: How can you create a live dashboard with Excel for your clients? After submitting my answer, I thought my Blog readers may benefit from this as well, so here it is 🙂

Consider the following dashboard:

This is one of my first dashboards to a customer in Vancouver, Canada. It is a personal sales-person dashboard (not the typical management dashboard) that feeds off the sales database, which is stored locally as an Excel table. This table is maintained by the sales person using a smart form, here it is:

As you can see, the dashboard is conceptually divided into two sections. The upper is drawing the sales person’s attention to business exceptions requiring his action. The lower part in informing him of relevant KPIs. This is a straight forward dashboard implemented straight off the data table, with no staging tables required. You can see it has no filters/selectors for the user to manipulate.

Consider the following dashboard:

This is from the same project, serving management, on a central, aggregating Excel Workbook, quietly and automatically retrieving data from all 15 sales persons’ Excel files into a company sales pipeline database.

Here you can see an upper fixed strip with global selectors for the user to choose. They affect immediately some staging tables maintained in a hidden Worksheet that feed the visible element you see below on the dashboard. The layout is vertically conceptualized, by which each chart has its data table below, so that all KPIs charts are arranged horizontally in the upper strip, while a more detailed, data-oriented view, is arranged in the bottom strip. This allows also for tables data to have room to grow down as needed.

It is worthwhile to mention that this dashboard is not driven by any (VBA) code. It’s all using live formulas, however advanced and elaborated, in the staging tables area – a hidden Worksheet.

Consider the following dashboard (however not in English :)):

This dashboard also has dynamic selectors for the user on the upper strip, with “All” buttons to reset the customers and products filters. The third selector cuts the analyzed period: last 12 months, 3 years or 5 years. This is part of an Excel-based solution for small business to manage their customers, products, proposals, orders, invoices and receipts. All data is taken from local Excel tables, however the dashboard is completely populated using VBA code that implements SQL queries for fast performance and execution of complex SQL queries.

On the right there is a fixed panel showing global sales KPIs, such as total sales sum as per confirmed orders, proposals conversion rate, number of customers generating 80% of sales orders total and average days between sales. Below that section on the panel is a customer specific info card, populated when a specific customer is selected in the upper customers selector. On the blue table the user can choose whether top customers are displayed, or bottom customers are displayed, and if by count of orders or by sum of orders (using the sorting buttons on the table columns’ headers).

The last dashboard is actually a dashboard my students create as their last assignment on my on-line course: Advanced Microsoft Excel Applications with SQL.

The students write a VBA program that controls this dashboard, leveraging SQL statements against a MySQL Database we build together over the course. As you can see, all dynamic elements of a real-life dashboard are manifested here: dynamic selectors, fast performance using SQL queries against an external Database, Layout and design for a great user experience and business conceptual arrangement of elements.