Excel Dashboard using Pivot Table

Posted on 13 August 2009

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. ... Continue Reading

Comments (3)

How to synchronize two or more pivot tables using VBA

Posted on 08 June 2009

One often comes across situations when one wants to synchronize two or more pivot tables. The problem arises when two or more pivot tables have the same underlying data set but show different views to the user. In such cases, combining both of them without losing data separation is not really an option.I wrote this small piece of VBA code a few months back that can help synchronize pivots across ... Continue Reading

Comments     

How to Create a Dashboard in Excel

Posted on 19 May 2009

How do I create a dashboard in Excel?To create a dashboard in Excel you organize the data used by the dashboard in an excel workbook, create flexible models out of the data and then finally present the data as a properly formatted report or a dashboard. Before we begin, always keep in mind that if the dashboard you are creating is going to be used (and oftentimes maintained) by other ... Continue Reading

Comments (4)

Excel Offset Function

Posted on 01 April 2009

The OFFSET function in Excel returns a reference to a range that is offset by a specific number of rows and columns from another range or cell. Before we begin, you can download the workbook containing a few examples of the offset function in excel here. The syntax for the Offset function in excel is: Offset( range, rows, columns, height, width ) range is the starting range from which the offset will be applied. Often it is a single cell but can ... Continue Reading

Comments (3)

Excel Chart Types – Basic

Posted on 31 August 2008

These are some basic chart types in Excel. You can play around with these by downloading the file here. Stephen Few, Data visualization Guru and author, mentions a few rules for creating charts on his website PerceptualEdge. Some good reads are: Practical rules for using colors in charts Godin’s Silly Rules for Great Graphs Stephan has, over the years, written on many facets of presenting data ... Continue Reading

Comments     

Excel Dashboard : Data Modelling

Posted on 23 August 2008

If you have followed this till now (silly me, if you’re reading this, you likely might’ve :-)), you’ve completed the first of the three important phases in the development of your dashboard. You may want to use these formulas to get your data up to scratch for the next important step – Data Modeling and the generation of pivot tables. Generating a Pivot Table Once your data has been properly scrubbed, cleaned for errors and filled up, its time to us ... Continue Reading

Comments (1)