Home > Dashboard Digest
IncSight DB Appendix:
How to Link Your Excel Dashboard
Report to PivotTables & Data Tables
Your Excel dashboard reports can be
live-linked to data in your data warehouse, to
Access, or to any other data that you can explore with PivotTables.
by Charley Kyd
One of the advantages to linking an Excel dashboard report to an
Excel database is that you can link your database, indirectly, to your
data warehouse. This means that your Excel dashboard can be linked,
indirectly, to your data warehouse.
Excel provides at least three ways to do this: You can use
PivotTables; you can use Data Tables; and you can manually import your
data using CSV files. The figure provides an overview of the process.
(The dashed line for the CSV Data indicates that it isn't returned by a
As this figure illustrates, three steps are required to use this
Step 1 is to create a data table or a PivotTable linked to
your corporate data. You also could import a CSV file manually. You'll have to
set up these tables on your own, because I don't have
access to your data and I haven't worked with relational databases in
years. (I used to consult about OLAP databases, which is a different technology.)
Step 3 is to use formulas in your Excel dashboard to return data from
your Excel database. Plug-N-Play #2 already
provides the Excel formulas to do this. And
Dashboard Reporting With Excel
explains these formulas in detail.
Step 2 is the missing link in
this process. You'll need to write formulas in your Excel database to
return data from your tables shown for Step 1. This article explains the
you, this process isn't for beginners. First, you'll need to set
up your PivotTables or your data tables on your own. Or generate your
own CSV files. I won't be able to
help you with this step, so please don't ask. (Your IT department might be able to help
with this step.)
Second, I won't take the time
in this article to
explain every new concept. But this shouldn't be a problem for Excel users who no longer are beginners.
(I'll probably offer video training later this year. Some videos
will explain Excel dashboards in detail. Others will explain the
following material in greater depth. Please
let me know if you
might be interested in a video series like this. I'm trying to get
an idea of what the attendance might be.)
Third, you'll probably need
to adapt these instructions to the pivot and data tables that you set
up. Your tables will look somewhat different from mine.
Of course, if you
don't want to learn quite so much about the Excel formulas I describe
below, you still can use
PNP #2 with a stand-alone Excel database. The
PNP #2 setup instructions explain how
to do it.
If you're familiar with PivotTables, you probably know something
about the GETPIVOTDATA spreadsheet function. It's the only function specifically
provided to return data from a PivotTable to your spreadsheet formulas.
I won't use that function here, for two reasons.
First, I cover it in
Dashboard Reporting With Excel,
and there's no need to repeat that material. Second, Excel's SUMPRODUCT
function is more powerful in many ways, and it can work with all three sources of data shown
in the figure. So that's
the function I'll explain here.
All the examples below are from one workbook
that I created in Excel 2007 using data from Access 2007's Northwind
database. The same techniques will work with earlier versions of Excel.
The workbook I discuss below has five worksheets: Control, DataTable,
PivotTable, DataTableDB, and PivotTableDB.
Introducing Dynamic Range Names and
the OFFSET Function
To use SUMPRODUCT reliably, you need to set up several dynamic range
names. These names are called "dynamic" because they change shape
automatically. We need this feature because data tables and PivotTables
usually change shape as we change our queries, and our range names need to adapt to the new shapes automatically.
To get started with dynamic range names, first consider standard
range names. The definitions of standard Excel names use simple cell references. For
example, we could define the name Foo like this:
However, range names also can define formulas that return cell
references. So we also could define Foo like this:
Both of these definitions return a reference to the same range,
The advantage to using OFFSET in Foo's name definition is that we can
replace the hard-coded numbers in the OFFSET formula with cell references or range names. That
way, as those values change, the range that Foo defines can
automatically expand and contract in response. That's what dynamic range names are
Most of the time, you'll use OFFSET to create a dynamic
range name. Here are it's arguments:
=OFFSET(reference, rows, columns,
That is, OFFSET returns a range reference that starts
at a given reference, is offset by a certain number of rows
and columns, and that has the specified height in rows and
width in columns.
It usually turns out that the key to creating
a good dynamic range name is to generate accurate values for its height and
But before we do that, we need to talk about "scope".
Sheet and the Scope of Range Names
All Excel range names have a "scope". Most range names are defined
with a workbook scope. This means that the name has the same meaning in
every worksheet in the workbook.
However, range names also can have a worksheet scope. That means that
when a name is used in one worksheet it can have one meaning and when
it's used in another worksheet in the same workbook, it can have a
this workbook we're going to use names with a worksheet scope. This will
let us use similar formulas in different worksheets to refer to
When you use range names with a worksheet scope, it's
always a good idea first to define the same names with a global scope as
I've done in the Control sheet, shown here.
These Reserved Names are the ones we'll define below. We begin by
defining each name with a workbook scope to refer to the cell at its
right, and then we enter =NA() in those cells. By using this approach, we
know immediately if we accidentally refer to the global version
rather than to the local version that we need. We know, of course,
because our formulas return #N/A.
Assuming that the names haven't been
defined anywhere else in your workbook, it's easy to define them
globally. Just select the range A3:B11, then...
- in Classic Excel, choose Insert, Name, Create...
- in New Excel, choose Formulas, Defined Names, Create From
Then, in the Create Names dialog, ensure that only Left Column is
Generating Height and Width Values
Data tables and CSV files both look about like the top of the data table shown
here, except that CSV files aren't quite so pretty and don't have the
controls shown in row 2. So if you're using CSV files, use the following
approach that I describe for data tables.
Here's the table I created in the DataTable
worksheet. You would begin the process to create this table in Classic Excel by choosing Data,
Import External Data, New Database Query. In New Excel, use Data, Get
Once you set up this table, enter the formula in the cell shown, which counts
the number of rows in the table:
The COUNT function returns the count of all
numeric values in the column, hidden or
not. Here, COUNT is returning the sum of all dates in column B. (Date
serial numbers are values, remember.)
Using the COUNT function does not allow you to vary your results by
filtering the data table. This is intentional, because we will be using
the SUMPRODUCT function to return values from the table, and, by
default, SUMPRODUCT ignores filtering.
(It is possible for SUMPRODUCT to return data only from non-filtered
rows, but doing so is beyond the scope of this article.)
entered the formula in cell C1, select the range B1:C1, and use the
Create Names dialog to define the NumRows range name. (If you've already
defined the workbook-scoped version of NumRows, the Create Names command
automatically defines a worksheet-scoped version.)
Now let's find the
number of rows and columns for the PivotTable, which resides in the
Notice that this PivotTable has a
very simple format, with no subtotals. The following method becomes
needlessly complex if your PivotTable does include subtotals, or if it
has more than one field in its rows or columns.
Also, for this figure only, I'm showing only the data for January, which allows you to
see a complete table. For the remainder of this article, I've used all
six months of dates that the sample data provides.
Because PivotTables can change height and width, we need to calculate
To find the number of rows of data, we use the COUNTA function. This
function counts both text and numbers. To get the actual number of data
rows, we count the total number of rows of text in column A, then
subtract 5. We subtract 4 to account for the labels in cells A1 through
A4, and we subtract 1 to account for the "Grand Total" label in cell A12.
To find the number of columns of data, we use
the COUNT function to count the number of numeric values in row 4. (Date
serial numbers, again, are numeric
To complete this step, select the
range A1:B2 and use the Create Names dialog to assign the two names.
Dynamic Range Names
Now that we have the heights and widths we need, and we've set up their range names, we can define our
dynamic range names. Let's start with the table in the DataTable
We need to define dynamic names for all three columns. To begin, define
the three standard names shown in the first table below. When you do so,
make sure you define them scoped to the DataTable worksheet. To do so in
New Excel, specify the scope using the Scope list box in the New Name
To do so in Classic Excel, use the Define Name dialog and specify
the scope by entering your range name using this format:
To illustrate, to define ProductName with
a worksheet scope for the DataTable worksheet, define the name like
Define these three names using the DataTable scope:
Now define the dynamic names shown below. Be sure to define them scoped
to the DataTable worksheet.
Notice my naming convention for these three names, where the dynamic name begins with
"a". This groups dynamic names together near the top of the list of
names, which helps you quickly to find dynamic names when you need them.
(Here, "a" stands for "array".)
To see how these dynamic names work, take a look at aAmount, which
references the table shown here.
Here's the general OFFSET formula again:
=OFFSET(reference, rows, columns,
Although aAmount uses the reference defined by
DataTable!Amount (cell C2 above), it returns a reference that begins in cell
C3, which is 1 row below cell C2 and zero columns in
either direction. The height of its reference is defined by
DataTable!NumRows. And here, the width of the reference is 1 column.
Now let's define the names in the PivotTable worksheet.
To begin, define the standard name shown below. Here, make sure to
define it scoped to the PivotTable worksheet:
Now define these three names scoped to the PivotTable worksheet:
To test any of these dynamic names, press the F5 function key,
which launches the Go To dialog. Type in any of the dynamic names,
choose OK, and Excel should select the appropriate range. (You'll notice that
the Go To dialog doesn't include dynamic names in its list. Perhaps
Microsoft will correct this bug in some future version of the
In case you've forgotten after this long introduction,
our purpose was to create a way to return data from your tables into
your Excel database. We can do that now.
Set Up Your
The top section of the DataTableDB worksheet is shown below. With
the exception of the title in cell B1, the Excel database in
PivotTableDB is identical to the figure shown here. This is because
I set up Data Table and the
Pivot Table to use two different methods to return the same data from
the same table in the Northwinds database.
So the resulting databases display the same values.
These Excel database worksheets use the same format as the one that
comes with PNP #2. In that
database, however, the data is hard-coded. But here, it's returned
Enter this formula in the cell shown, and copy it to the
remainder of the data area:
Similarly, in the PivotTableDB database, enter this formula for
the cell shown, and copy it to the remainder of that data area:
These two formulas use the same logic:
- Calculate the first of the month for each date in aOrderDate
and compare it to the corresponding date in row 2 in the figure above. Return
an array of TRUE where the dates match and FALSE where they
- Compare each product name in the aProductName range to the
corresponding product name in column B. Return an array of TRUE where the
products match and FALSE where they don't.
- Get the array of values from the aAmount range.
- Multiply the array of TRUE and FALSE values in (1) by the
array of TRUE and FALSE values in (2). This returns an array of
1's and 0's, where the the 1's are found only when the
corresponding values in arrays (1) and (2) both are TRUE.
- Multiply this array by the values in (3). This returns an
array mostly consisting of zeros, with the occasional non-zero
values where the dates and the product names match the cell's
- Finally, use SUMPRODUCT to return the sum of that final
Once you've checked that your Excel database has the data you expect,
the process is done. Your data warehouse is feeding your data table or
your PivotTable (Step 1 in the figure at the top of this page). Your
Excel database is summarizing data from your data or pivot tables (Step
2). And your Excel dashboard is using the formulas provided by
Plug-N-Play #2 to pull the live
data from your Excel database (Step 3).