Excel® 2007 Dashboards & Reports For Dummies®

Chapter 11. Giving Users an Interactive Interface

Ah, life was so much simpler in the days of one-view reports and static dashboards. Remember the good old days when a static report was enough to have managers carrying you on their shoulders?

Today, managers increasingly want to be empowered to switch from one view of data to another with a simple selection from a menu of choices. For those of us who build dashboards and reports, this empowerment comes with migraines and acid reflux. How do you handle a manager that wants to see multiple views for multiple regions or markets?

Fortunately, Excel does offer a handful of tools that enable you to add interactivity into your reports. With these tools and a bit of creative data modeling, you can give your managers the choices they crave with relative ease.

In this chapter, I show you how to incorporate menus, options, and selectors into your reporting mechanisms and offer a few useful examples you can implement into your processes.

Introducing Form Controls

Excel offers a set of controls called Form controls, designed specifically for creating user interfaces directly on a spreadsheet. The idea behind using a Form control is simple. You place a Form control on a spreadsheet and then configure it to give it a specific task.

Excel's Form controls can be found on the Developer tab, which is initially hidden in Excel 2007. By hidden, I mean you don't see a tab called Developer when you first open Excel 2007. You have to explicitly tell Excel to ...