Dashboards in Excel

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.

The power of dashboard reporting with 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.

How to create a KPI (Key Performance Indicators) Dashboard in Excel

This link guides you through how to use the Offset function in Excel.

MS Excel: Offset function

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:

Creating KPI dashboards in Excel - Part 2 - Adding a Sort Feature

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.

MS Excel: Large function

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.

MS Excel: Match function

What are form controls?

Form Controls - Adding Interactivity to your Worksheets

Using controls on worksheets : where to find the 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.

Where to find the form controls in different versions of Excel