Mix: Dashboards in Excel
Dashboards are very popular in the business world at the moment. They contain lots of information, sometimes dynamic, on one page, so that people have access to a vast array of information without the need to juggle between documents.
This article explains a little bit more about what dashboards are and why Excel is a powerful tool for creating dashboards, with a few examples of dashboards that have been created in Excel.
Creating a dashboard in Excel following a 6 part tutorial
Part One - How to Create a KPI dashboard in Excel
This tutorial goes through how to take a large data set and display it as a 10 line table that you can scroll through to view the records you wish. Thus allowing a dashboard page to be created with access to a large dataset.
This link guides you through how to use the Offset function in Excel.
Part Two - How to add a Sort feature
This tutorial takes you through how to add buttons and build in a Sort to your dashboard page so that, by clicking in the appropriate button the data will be sorted. This will use Form Controls in Excel, along with the Large and Match functions, links below to tutorials for each of these:
The Large function returns the largest, 2nd largest, 3rd largest etc figure from a specifies range. This link guides you through several examples, making it very clear how to use it.
The Match function searches for a particular value in a specified range and comes back with the relative position of the exact match or the nearest match, depending on what criteria you have set.
This link explains how to use the match function using several examples.
What are form controls?
The following link shows where the form controls are in differnt versions of Excel. Makes it clear where to find them by using screenshots.
Part Three - Highlighting KPI's based on percentiles
How to show percentiles for the visible product on the dashboard page by clicking on the appropriate KPI button.
The Percentile function calclates the specified percentile from a given range. Several examples show how the function works.