Excel Dashboards and Reports for Dummies, 3rd Edition

Chapter 2

Building a Super Model

In This Chapter

Understanding the best data modeling practices

Leveraging Excel functions to deliver data

Creating smart tables that expand with data

One of Excel’s most attractive features is its flexibility. You can create an intricate system of interlocking calculations, linked cells, and formatted summaries that work together to create a final analysis. However, years of experience have brought me face to face with an ugly truth: Excel is like the cool gym teacher who lets you do anything you want — the freedom can be fun, but a lack of structure in your data models can lead to some serious headaches in the long run.

What’s a data model? A data model provides the foundation upon which your reporting mechanism is built. When you build a spreadsheet that imports, aggregates, and shapes data, you’re essentially building a data model that feeds your dashboards and reports.

Creating a poorly designed data model can mean hours of manual labor maintaining and refreshing your reporting mechanisms. On the other hand, creating an effective model allows you to easily repeat monthly reporting processes without damaging your reports or your sanity.

The goal ...