Categorized | excel dashboard

Excel Dashboard using Pivot Table




Excel Dashboards created using a pivot table have the advantage of being low maintenance. The excel dashboard created using this method will typically have three layers – the raw data, the pivot table and the final dashboard presentation. This loosely follows the Model–View–Controller pattern found in software programming with the pivot table acting as the controller.

Creating an Excel Dashboard using pivot tables

As we mentioned earlier, to create a complete dashboard, the following three pieces need to be put together. Let’s take a simple example of a company which sells products from three product categories – Electronics, Home Décor and Industrial. The company operates in 12 regions with each region employing the services of nine sales manager – three each representing each product category. In our case, we want to build a dashboard which presents the following points (for each region, for the most recent month) to the reader:

1. Sales target
2. Volume of Sales generated
3. % of sales target met
4. Total Profit generated for all the regions
5. The average sales volume per sales manager AND
6. The average profit generated per sales manager

Assume that the reader is interested in looking at this information so that he/she can compare the performance of a particular product category in a particular region viz-aviz other regions.

Raw Data for the Excel Dashboard

(Input Data)
The raw data for the scenario highlighted above looks something like this.

excel-dashboard-raw-data
(Only a portion of the data is shown)

Creating a Pivot Table for the Dashboard

Now let’s create a pivot table using this underlying raw data. (Refer to this article to know how to create a pivot table.)

excel-dashboard-pivot-table

The pivot table shown above has been created using Excel 2007. If you create the same in Excel 2003 or earlier versions, the orientation would be slightly different.

Presentation of Excel Dashboard using tables and charts

The pivot table is useful but it is not something that we would like to show to the user. So let’s build an intermediate table that can show pretty much the same information but is easier to work with. (Agreed that the pivot table can be augmented using calculated fields but this technique is simpler and is very useful for versions earlier than Excel 2007)

In the example shown below one such intermediate table is shown.

excel-dashboard-getpivotdata-formula

In the first column of this table, we insert the list of regions in each row. To make the values in the list of regions dynamic, we can have them refer to the cells within the pivot table so that whenever the user makes changes to the pivot table, these fields acquire new values. Once we have the first column in place, all we need to do is to use the GETPIVOTDATA formula to fetch values pertaining to each of these items. The GETPIVOTDATA formula can provide us with different values based on the values specified to the parameters. Hence , we can use a single formula to provide us different values. (Ex. GETPIVOTDATA(“Region”, B26) can provide values pertaining to the region mentioned in cell B26. We can now change the values in cell B26 to “Alabama”, “Ohio” or any other valid value). As we move to the columns towards the right, all we need to do is to keep changing the first argument (Ex. Replace “Sales” by “Target” so that while in the first column for formula is GETPIVOTDATA(“Sales”, B26) in the second, it is GETPIVOTDATA(“Target”, B26)).

excel-dashboard-pivot-table

To avoid showing the pivot table to the user, we can hide the relevant rows. So in our case, barring the drop-down for the division, all the other rows have been hidden (or grouped). We now have a 2-level cascade – the first one where the list of regions updates itself as the product drop-down is changed and the second where the change in the list regions triggers the GETPIVOTDATA to fetch updated values from the pivot table. The drop-down list that we use for this purpose need not be a part of the pivot table – a simple drop-down using data validation or a combo box can also serve the purpose equally well. If you feel like going the extra step, feel free to insert a few charts as well. Read this article to know how to create drop down lists using data validation in Excel.

This post highlighted a simple process for creating an excel dashboard using pivot tables. In practice, you would likely need to work with much larger and complex data sets. However the basic rules covered in this article will likely remain the same.

Excel 2007 and Later versions : Download a sample worksheet with a example of Excel Dashboard in 2007 and Later versions here or click on the button below:

excel-dashboard

Excel 2003 and Earlier versions : Download a sample worksheet with a example of Excel Dashboard in 2003 and Earlier versions here or click on the button below:

excel-dashboard


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>


Comments and Trackbacks

  1. Jay wrote:

    Ajay,

    Enjoy your blog ..worthevery min I spend reading your stuff. It is like an apple product.

    In this article I’m not able to download 2007 version of the excel file.. could pls make it available.

    -Jay

  2. Ajay wrote:

    @Jay – Oops! The dashboard should be available for download now. And thank you so much.

  3. Jay wrote:

  4. Sathyan wrote:

    I like this is a very useful & Time saving site.

  5. P Chandra Sekhar wrote:

    Please help trying to creat pivot table and getting error

    Sub sbCreatePivot()
    Dim ws As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable

    Set ws = Worksheets.Add

    Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, “Sheet1!R1C1:R50000C43”)

    Set pt = pc.CreatePivotTable(ws.Range(“B3”))

    With pt
    With .PivotFields(“Sheet1!R1:R43”)
    .Orientation = xlRowField
    .Position = 1
    End With

    With .PivotFields(“Statusid”)
    .Orientation = xlColumnField
    .Position = 1
    End With

    .AddDataField .PivotFields(“Statusid”), “Sum of netamt”, xlSum

    End With
    End Sub

  6. j wrote:

    Sub Button1_Click()
    ‘Sub sbCreatePivot()

    ‘‘Declaration
    Dim ws As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable

    ‘‘Adding New Worksheet
    Set ws = Worksheets.Add

    ‘‘Creating Pivot cache
    Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, “Sheet1!A1:C10”)

    ‘ ‘Creating Pivot table
    Set pt = pc.CreatePivotTable(ws.Range(“B3”))

    ‘‘Setting Fields
    With pt
    ‘‘set row field
    With .PivotFields(“Department”)
    .Orientation = xlRowField
    .Position = 1
    End With

    ‘‘set column field
    With .PivotFields(“Region”)
    .Orientation = xlColumnField
    .Position = 1
    End With

    ‘‘set data field
    .AddDataField .PivotFields(“Profit”), “Sum of Profit”, xlSum
    End With

    ‘End Sub
    ‘[/code]

    End Sub

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel


ARCHIVES