Dashboards are really popular in business at the moment. Contained here is some information about what a dashboard is, a series of 6 tutorials on how to create a dashboard in Excel. There is also information on any Excel functions used in the tutorials so that hopefully you have a one-stop shop for creating a dashboard in Excel.

Dashboards

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.

 

 

 

By tracey

This Mix is public

Only tracey can edit

Only tracey can add links

Comments are not enabled

Categories

Tags