Complete Introduction to Excel Interactive Dashboards

Excel is an amazing tool for analyzing data, but the challenge is understanding what your data is telling you.  A problem I find people regularly battle with is that when you start exploring different aspects it’s easy to end up with Analysis Paralysis and getting overwhelmed by how much information is available.  In this video I will show you to create powerful interactive dashboards using nothing complex, just simple drag and drop techniques, which will give you the big picture as well as the ability to filter and sort as needed.

Dashboards are the ideal tool for providing a high level overview of what is happening in the business, department, team or project and are traditionally made up of tables of data and graphs that can quickly and easily convey important information.
Before starting to set up a dashboard, you need to decide on your key metrics.  These are normally important totals that you want to share, for example, total sales, total profit or average profit per customer.  I suggest that the Key Metrics are bold and placed in the top right area of the dashboard as this is where most people will start viewing your dashboard. 

Visual elements are another important element of a dashboard as graphs convey a lot of information very simply.  A trend graph easily communicates the general trend of data, is the data moving up, down, stable or volatile? Line or area graphs are normally used to communicate trends.

Column and bar charts are used to communicate which items are highest or lowest.  These types of graphs provide an understanding of magnitude differences between items and are used to communicate comparisons.

Pie charts can be used to communicate percentage contribution to the whole.  It is easy to overcrowd a pie chart by using too many items - so the general rule of thumb is to keep the number of items down to no more than 5 to 7 items. 

Colour also plays an important role because they convey subconscious messages.  When designing dashboards, I suggest not using too many colours and to keep in mind the impact of the colours you choose (ie. green is associated with good and red is associated with bad).  My advise is to use use soft grays, browns or blues for backgrounds and bold colours for the key things you’d like to highlight.

A key point I’d like to bring your attention to, is that while I’m going through the processes of how to create an interactive dashboard in Excel using only Pivot Tables, Pivot Charts and Slicers, I never use complex formulas, VBA or macros.  Microsoft have really moved away from these complicated methods and made things simple for all Excel business users.

I hope you enjoy this video and if you are interested in learning more about creating interactive dashboards with Excel, please have a look at our on-site classroom courses and online or virtual courses.