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

Chapter 17

Working with Data That Changes Size

What business strategy is all about, what distinguishes it from all other kinds of business planning, is, in a word, competitive advantage. Without competitors there would be no need for strategy, for the sole purpose of strategic planning is to enable the company to gain, as effectively as possible, a sustainable edge over its competitors.

Kenichi Ohnae

“One of the top five management gurus in the world”

—The Economist

A useful skill when creating Balanced Scorecards and dashboards is being able to handle data that changes size or location. This is useful if you want to import new data and the size of the data source changes, and it is also useful with charts. For example, you might have one chart, but you might want to allow the user to choose from among four different data sources. In another situation, you may have one chart, and you want it to use different starting dates or different periods of time.

The ability to work with data that changes size or shape also comes in handy with source data that changes size or shape. You will find this useful whether your dashboards work from manually entered data or from data retrieved through a dynamic link to a database.

This chapter begins by describing tables, a feature that was introduced in Excel 2003 as a List.

Tables are great for storing data that will expand or contract. Tables can be used for lists, databases, and charts. In earlier versions of Excel, complex OFFSET formulas were needed ...