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
- Excel 2010 KPI tutorial 1: Conditional formatting
- Excel 2010 KPI tutorial 2: Simplify your charts
- Excel 2010 KPI tutorial 4: Less clutter, more info
- Excel 2010 KPI tutorial 5: PowerPivot tips
- Excel 2010 KPI tutorial 6: Conditional format icons
- Applied Excel: Building a traffic light KPI dashboard (2008)
- Build a dashboard with Excel 2010 Sparklines
- Presenting financial figures with Excel: Charting choices
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.