Chapter 24: Implementing Excel Dashboarding Best Practices
24
a great deal of RAM to process even the smallest change in your spreadsheet. You
may have noticed that when you try to perform an action on a large formula-inten-
sive data set, Excel is slow to respond, giving you a “Calculating” indicator in the
status bar. The larger your data set is, the less efficient the data crunching in Excel
will be.
■ Large data sets can cause difficulty in scalability. Imagine that you’re working
in a small company and you’re using monthly transactions in your data model. Each
month holds 80,000 rows of data. As time goes on, you build a robust process com-
plete with all of the formulas, pivot tables, and macros that you need to analyze
the data that’s stored in your neatly maintained tab. Now what happens after one
year? Do you start a new tab? How do you analyze two data sets on two different
tabs as one entity? Are your formulas still good? Do you have to write new macros?These are all issues that can be avoided by importing only aggregated and summarized data
that’s useful to the core purpose of your reporting needs.
Documenting and organizing your data model
Wanting to keep your data model limited to one worksheet tab is natural. Most users would
think that keeping track of one tab is much simpler than using different tabs. However,
limiting your data model to one tab has its drawbacks, including the following:
■ Using one tab typically places limits on your analysis. Because only so many
data sets can fit on a tab, using one tab limits the number of analyses that can be
represented in your data model. This in turn limits the analysis your dashboard
can offer. Consider adding tabs to your data model to provide additional data and
analysis that may not fit on just one tab.■ (^) Too much on one tab makes for a confusing data model. When working with
large data sets, you need plenty of staging tables to aggregate and shape the raw
data so that it can be fed to your reporting components. If you use only one tab,
you’re forced to position these staging tables below or to the right of your data
sets. Although this may provide all of the elements needed to feed your presenta-
tion layer, a good deal of scrolling is necessary to view all of the elements posi-
tioned in a wide range of areas. This makes the data model difficult to understand
and maintain. Use separate tabs to hold your analysis and staging tables, particu-
larly in data models that contain large data sets occupying a lot of real estate.
■ Using one tab limits the amount of documentation that you can include. You’ ll
find that your data models easily become a complex system of intertwining links
among components, input ranges, output ranges, and formulas. Sure, it all makes
sense while you’re building your data model, but try coming back to it after a few
months. You’ll find that you’ve forgotten what each data range does and how each
range interacts with the final presentation layer. To avoid this problem, consider
adding a model map tab to your data model. The model map tab essentially summa-
rizes the key ranges in the data model and allows you to document how each range
interacts with the reporting components in the final presentation layer.