Excel 2010 KPI tutorial 3: PivotTable dashboards

Simon Hurst continues his KPI reporting casebook by incorporating some of the more advanced features available with Excel 2010, including PowerPivot.

In this series we are looking at ways to present information in Excel clearly and with maximum impact. Part 1 demonstrated the use of some of the new conditional formatting features available in Excel 2007 and Part 2 looked at some charting options in Excel.

One of the points made in Part 1 was that several of the enhancements in Excel 2007, and particularly 2010, are designed to allow Excel to be used for “self-service business intelligence”. This is possibly because PivotTables can provide the data engine to power the BI reports.

The Excel 2010 PowerPivot add-in makes it easy to create a basic dashboard based on PowerPivot data. Once you have the data you want in the PowerPivot window, the PivotTable dropdown in the Reports group of the Home ribbon tab includes a range of options to display sets of tables and charts. For example, you could choose the ‘Four Charts’ option as shown in the full tutorial...

More Excel KPI and charting tutorials

About the author
Simon Hurst is a former chairman of the ICAEW IT Faculty and runs The Knowledge Base, a consultancy dedicated to helping accountants make effective use of technology. He is a regular contributor to AccountingWEB's ExcelZone and the author of '100 Time-saving Tips for Microsoft Office'. For more information, visit The Knowledge Base website.

Continued...

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.

Comments
AfinNet's picture

Childishness.

AfinNet | | Permalink

.

shurst's picture

Presentation of data

shurst | | Permalink

Thanks afinnet for taking the trouble to comment on the post. Your point is well, and admirably concisely, made. We should indeed be striving to make our presentation of complex financial data so clear and simple that it could be understood by a child.

Excel 2010 KPI tutorial 3: PivotTable dashboards

Cantona1 | | Permalink

 

Hi Simon,

This is not childishness. I do not think many people are aware the power of Pivot Tabel. Personally, PT in excel 2010 is not a cosmetic improvement to previous excel versions. It has some very powerful functionalities.  For e.g Flattened PT is a new feature in excel 2010.  Linking different tables in to one so that you do not need to do separete columns for Lookup functions is another one. Grouping data is a bit annoying in excel 2010, particularly Date.

I hope to see more of your articles in Power Pivot in the future.

AfinNet's picture

Childishness....

AfinNet | | Permalink

You are sweet, with that "For kids"... ;)

4 pivot tables for kids? Why not 8 or 23?

This is a kind of "excel hell" you propose. To make wonderful charts in Excel is very simply, try to professionally include your pivots into live informatic environment.

Wojciech G.

www.afin.net

shurst's picture

PowerPivot

shurst | | Permalink

Thanks for the varied comments, and I look forward to contributing more on PowerPivot. There are lots of ways of creating 'Excel Hell' but I think one advantage of PivotTables is that you can do some quite advanced analysis without having to create any Excel formulae and a lot of what goes wrong in Excel is to do with formulae: incorrect formulae, inconsistent formulae, overwritten formulae. I'm sure that PivotTables do have issues of their own,but I often find them more manageable than sheets of hard to understand formulae.