Balanced Scorecards and Operational Dashboards with Microsoft Excel, 2nd Edition

Chapter 26

Drilling to Detail

Never neglect details. When everyone’s mind is dulled or distracted, the leader must be doubly vigilant.

—Colin Powell

Statesman, General

Scorecards and dashboards display summary results of large amounts of information. Inevitably, this forces two needs: a concise and obvious way to navigate the data and the ability to drill down from summaries and results into the original data.

This chapter describes a couple of ways to create simple navigation systems. Some are obvious, such as hyperlinks, but can take a lot of display space. Another navigation method uses a simple combo box to enable your users to jump to any named location in the workbook.

It’s inevitable with Balanced Scorecards and operational dashboards that someone will find an item in the summary level that needs more examination—and that means drilling into detail. There are many ways of doing this in Excel, but the methods depend on how Excel accesses the data and the developer’s experience level.

This book is written for mid- to advanced-level Excel users who usually retrieve data into the workbook. For that reason, this chapter covers methods of drilling down into data within the workbook. These usually limit drill-down to going one level deeper into data. More-advanced methods, and almost unlimited levels and views in drilling, are possible when Excel is linked to an external database such as a relational database or an OLAP cube and when it uses a PivotTable or PowerPivot to analyze data. ...