Chapter 24: Implementing Excel Dashboarding Best Practices
24
more formulas and tabs to consolidate the data on each of the month tabs? The fundamen-
tal problem in this scenario is that the tabs actually represent data values that are fused
into the presentation of your analysis.
A best practice for avoiding these kinds of scalability issues is to build three layers into
your data model: a data layer, an analysis layer, and a presentation layer.
You can think of these layers as three different spreadsheets in an Excel workbook: one
sheet to hold the raw data that feeds your report, one sheet to serve as a staging area
where the data is analyzed and shaped, and one sheet to serve as the presentation layer.
The analysis layer consists primarily of formulas that analyze and pull data from the data
layer into formatted tables commonly referred to as staging tables. These staging tables
ultimately feed the reporting components in your presentation layer (such as charts, condi-
tional formatting, and other visualizations). In short, the sheet that contains the analysis
layer becomes the staging area where data is summarized and shaped to feed your dash-
board components.
There are a couple of benefits to this setup. First, the entire reporting model can be
refreshed easily simply by replacing the raw data with an updated data set. The formulas in
the analysis tab continue to work with the latest data. Second, any additional analysis can
easily be created by using different combinations of formulas on the analysis tab. If you
need data that doesn’t exist in the data sheet, you can easily append a column to the end
of the raw data set without disturbing the analysis or presentation sheets.
Note that you don’t necessarily have to place your data, analysis, and presentation layers
on different spreadsheets. In small data models, you may find it easier to place your data
in one area of a spreadsheet while building your staging tables in another area of the same
spreadsheet.
Along those same lines, remember that you’re not limited to just three spreadsheets either.
That is to say, you can have several sheets that provide the raw data, several sheets that
analyze, and several sheets that serve as the presentation layer.
Wherever you choose to place the different layers, keep in mind that the idea remains the
same—the analysis layer should primarily consist of formulas that pull data from the data
sheets into staging tables used to feed your presentation.
Starting with appropriately structured data
Not all data sets are created equal. Although some data sets work in a standard Excel envi-
ronment, they may not work for data modeling purposes. Before building your data model,
you will want to be sure that your source data is appropriately structured for dashboarding
purposes.
Spreadsheet reports make for ineffective data models
Spreadsheet reports display highly formatted, summarized data, and they are often designed
as presentation tools for management or executive users. A typical spreadsheet report