http://www.excelcharts.com/blog/how-to-create-an-excel-dashboard/
How to create a dashboard in Excelhttp://www.blogger.com/img/blank.gif

by Jorge Camoes on September 16, 2007
Excel dashboards

Excel dashboards and executive reports are powerful, fairly easy to design and a great way to improve your Excel and data visualization skills. Because of its flexibility, you can virtually design any dashboard in Excel exactly the way you, or the users, imagined. And, best of all, you may want to implement it yourself or consider it a prototype and ask IT to implement it.

Once you know what will the Excel dashboard be used for and what kind of metrics users expect, there are three major areas that you must address when creating it:

how to bring data into your spreadsheets;
how to manage the data and link it to the dashboard objects, like charts and tables;
how to design the dashboard report.

Let’s take a look at each of them.
How to bring data into your Excel dashboard

Yes, Excel is a very flexible tool, but to create an Excel dashboard you can’t just paste some data and add a few charts, can you? You must maintain and update it and, if you want to decrease the cost associated with those tasks, you must impose some structure to your data.

Usually, the data should not be entered directly into the spreadsheet. You may copy /paste the data, but the best option is to connect the spreadsheet to the data source. There is a standard way to communicate with external databases called ODBC. You can use it to connect your dashboard to a table in Access or Oracle, for example (there’s a free video in the members area, that shows how to connect Excel to an Access table; create a free account to watch it).

Once you have that connection established, every time the data changes at the source it also changes in the spreadsheet, after refreshing. Keep in mind that a good practice is to minimize the amount of data you bring into your spreadsheet. If you don’t need it, don’t use it. If there are calculations to be performed, try to do them at the source.

Data can be imported using two basic structures: a flat file and a pivot table. As a rule of thumb, I would say that a pivot table tends to create larger files but the calculations will be faster. The flat file will be smaller but, if you need complex formulas to select the data, performance will suffer (you must test this in your own project).
How to manage the data and link it to the dashboard objects

If you use pivot tables you can extract the data using the GETPIVOTDATA function. If you use a flat file, there is a vast array of formulas you can use, like database formulas (DSUM, DGET), lookup (VLOOKUP, MATCH, INDEX) or math formulas (SUM, SUMIF, SUMPRODUCT).

Having many formulas and calculations is dangerous to your dashboard integrity. You can decrease this by using pivot tables. That’s one of the reasons I choose them by default.

Named Ranges

Do I have to tell you that you should document your work? OK, I’ll tell you: yes, you should. But you can simplify that boring task by using named ranges. I am sure this is a familiar example: “=Sales-Expenses” is better than “=$G$4-$H$5”.

Named ranges not only help you making your formulas cleaner and easier to read, but they also play a key role in data management for Excel dashboards. Example: let’s say that the next update adds a new column to your table. If you don’t use named ranges you’ll have to change every single formula and add that extra column. With dynamic named ranges, the extra column is immediately available. This means less and more reliable work.

Dynamic Charts

I am a truly believer in dynamic charts. Dashboards that the user can’t interact with don’t make much sense to me. All my dashboards have controls to change markets, change products, change sales territories, whatever. What is important is that the user must be able to interact with the dashboard and build his/her knowledge by exploring the available data. Some managers prefer a static dashboard (perhaps just a sheet of paper, or a PDF file), but even then you should implement interaction, because it will be easier to create those static reports (you can automate them).

If you are creating interactive charts you will probably need dynamic ranges. You can do it using the OFFSET() function.

Macros

OK, you are not a programmer (I am not) but you should consider using some macros to speed up some repetitive tasks. Also, some cool things that you can add to your dashboards can only be done using macros, like greeting the users and selecting the right profile when they open the dashboard (you may want to read the discussion around the use of VBA in Excel dashboards).
How to design the dashboard report

If you like the kind of charts that you find in marketing-oriented tools like Crystal Xcelsius let me tell you this: they don’t work. There is a first wow! and then their uselessness starts creeping in.

This is not a matter of opinion only: I tried (and failed) to replicate my Demographic Dashboard (see below) using Xcelsius and detailed the process in a series of posts (1, 2, 3, 4, 5), so I know why these tools are useless for serious work. Don’t try to use Excel to mimic them.

You may still be using Excel 2003. Excel 2003 chart defaults are ugly and you should avoid them like the plague (and make sure you select the acceptable formats). Excel 2007 and 2010 are much better, but they still need some work. Throughout this blog you’ll find many charting tips, and tips to improve your Excel dashboard, so I will not elaborate much on this. The basic premise is this: a chart is used to discover actionable patterns in the data, and you should do your best to show those patterns.

This means that you should remove everything that has no added value (3D effects, backgrounds, etc), create a hierarchy of focus and context data (make relevant data stand out, using color carefully) and add at least a basic form of interaction (let users select the series, for example).
Resources

The reference in dashboard design if of course Stephen Few’s book, Information Dashboard Design. Other recommended reading are John Walkenbach’ Excel Bible (2003 or 2007) and Excel Charts (2003 or 2007); Tomothy Zapawa’ Excel Advanced Report Development (2003 or 2007); Michael Alexander’s Excel 2007 Dashboards and Reports for Dummies and Jon Peltier’s blog.
An Excel Dashboard Example: The Demographic Dashboard

This is an example of dashboard reporting using many advanced functions:

As I said above, it is difficult to put everything to work together in an Excel dashboard project. With the Demographic Dashboard, I wanted to show how to connect the dots and create a fully functional dashboard. So, I used a very interesting dataset from the US Census Bureau (population by sex, age and country, 1996-2050) to design it.

Actually, there are three dashboards… I took advantage of Excel’s built-in flexibility to create three versions of the same dashboard. They all look the same, but each one uses a different set of techniques:

The VBA version: This was the first one. It uses Visual Basic for Applications and pivot tables. It is faster than the other ones, but it relies on VBA to perform some tasks, and that may put off some users (I’m just using recorded macros, nothing too complex);
The VBA-Free version: After some discussion on the merits of using VBA (Excel Dashboards: Do You Need VBA?) I decided to add a VBA-free Excel dashboard; It mainly uses pivot tables and the powerful GETPIVOTDATA function. It is a well balanced version: it is slower than the previous version but since it doesn’t use VBA some users don’t have to leave their comfort zone;
The LookUp Table: It uses a spreadsheet database (basically a table that you paste into a sheet) and lookup functions (VLOOKUP, MATCH, OFFSET) to get the data for the dashboard report. Personally I don’t feel very comfortable with spreadsheet databases (more maintenance, more error prune, slower) but it is a good starting point to learn advanced Excel techniques (and sometimes is it your only option).

For a deeper discussion on the pros and cons of each version you may want to read the post Performance vs flexibility in Excel: Demographic Dashboard Lookup Edition.
Wrap-Up

Excel is a great tool for any type of business executive reporting. It allows for fast, flexible and cost-effective dashboard implementation (or prototyping). Intermediate users should be able to design and implement an executive dashboard in a matter of days (or even hours, for the first draft), provided that they know how to select the right techniques.