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 has brought me face-to-face with an ugly truth. Although Excel is like the cool gym teacher that lets you do anything you want, 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 of this chapter is to show you the concepts and techniques that help you build effective data models. In this chapter, you discover that creating a successful reporting mechanism requires more than slapping data onto a spreadsheet. Although you'll see how to build cool dashboard components in later chapters, those components won't do you any good if you can't effectively manage your data models. On that note, let's get started.
Building an effective ...