Home > Dashboard Digest
How to Set Up IncSight DB (PNP02)
After you have your data available, you should be able to set up your first dashboard
report in about an hour. Here's how to do it.
Here are the set-up steps. Click on
each step for more information.
1. Save a copy of your
2. Set up your data.
3. Choose a report workbook.
4. Plan your report layout.
5. Enter your Control data.
6. Set up the
Figure Data Summary (FDS) sheet.
7. Choose a color theme.
8. Set up your tables.
9. Print your report.
Other Setup Instructions.
Here's how to...
...Edit several worksheets as a group.
...Adjust colors in Class and New Excel
...Change a chart's source data
...Change date formats in a dashboard
...Correct a chart's Y-Axis label
...Add or delete time periods in your report.
...Change from months to some other time period.
...Synchronize your charts' Y axes values
...Link your Excel dashboard report to PivotTables & Data Tables
Before you get started, I need to reassure you about something...
Setting up your report workbook doesn't take very long after you
gather up your data. You copy the data to an Excel database; update six
or eight settings for the entire workbook; update two or three settings
for each chart figure; choose a color scheme; and you're done.
However, I admit that the length of this article makes it seem like
there's a lot more work involved. The reason I let this article get so
long is that I took the time to explain in some detail the reasons
for your dashboard settings, certain Excel techniques that will
be useful in working with the dashboard, and optional things you can do
with your reports. So feel free to skim over the
areas you don't care about.
Let's get started...
When you first downloaded your purchase, you received a zip file that
contains 43 files in three folders:
- The main IncSightDB folder contains a short "ReadMeFirst.txt" file
that explains how to find these instructions and a ReportGuide.xls
workbook that helps you to choose your reports quickly. The folder
also contains the following two folders.
- The Reports folder contains your data file, Data_IncSightDB.xls, and
20 report files with names from IncSightDB_01.xls to IncSightDB_20.xls. The
report numbers correspond with the numbered images in ReportGuide.xls.
- The Colors folder contains 20 workbooks, each with its own color
scheme. When you
open any of these workbooks you'll see an example of what your report
will look like if you use the file's color scheme.
Of course, if you use New Excel (Excel 2007 or later), your workbook
files should end in "xlsx" rather than "xls".
First save these files where you can find them later. Then save a
working version in another directory. For now, keep the names of the
report files and the data file unchanged.
The 20 report workbooks are live-linked to the data workbook. So when you first open
a report file it will look for its data file.
You'll get an error dialog if its data file isn't already open.
is bound to happen from time to time, and it's not a problem.
Depending on the error dialog, I usually choose the "Don't Update" or
"Continue" option, which dismisses the dialog. Then I open
the missing data file and recalculate by pressing F9.
Changing Data Files
At times, I get the error message because I get in a hurry at some point
and change the name of my data file, or move it, without updating my
report. So my report can't find the data file. To correct this
problem, make sure your report file is open and then...
- In Classic Excel, choose Edit, Links...
- In New Excel, choose Data, Connections, Edit Links...
...then, in the Edit Links dialog, choose Change Source to launch a
dialog that you can use to find the missing data file. Once the data
file is open and the links are correct, be sure to save your report file
to capture the new source for its data file.
Copy Data Into Your Data File
The data workbook contains three worksheets.
The Control worksheet Das some brief documentation, and it lists some
range names that are used in the other two worksheets, "Act" (Actual) and "Tgt" (Target).
is the Act worksheet. So you can see its four corners, I've hidden columns G through AZ and rows 13 through 31. These
areas normally are visible, of course.
The gray borders are critical here. When you insert or delete rows or
columns, make sure you do so between the gray borders. If you don't do
so, you'll destroy range names that are anchored in those borders.
This database has the following important areas:
Codes--These could be General Ledger codes that accountants use,
Diagnostically Related Groups (DRG)
codes that healthcare workers in the United States use, product codes
that sales people use, and so on. The codes just need to be relatively short
ones that you can maintain easily.
These codes should be text rather than numbers. This is because
Excel's MATCH function, which the reports rely on, won't recognize that text "123"
is the same thing as number 123. To force number codes to always be text I usually add an
appropriate letter in front of the codes. For example, I often add "C"
to the front of Chart of Accounts codes. This would turn, say, account 1200 to
Some codes, like DRG codes in healthcare, count sequentially from 1. If they're
expressed as numbers, these codes sort easily. But if they're expressed
as text, they sort like this: "D1", "D10", "D2", etc. Therefore, to make
the codes easier to use over the long run, you might want to add leading
zeros as necessary. This would generate DRG codes like "D0001", "D0075",
This is easy to do. Suppose your DRG codes are in column A of some
worksheet, beginning in cell A4. In cell B4, you could enter the
...or, if you would like to create a code that's easier to read, like "D-05-79", you could use...
...and then copy the formula down the column as needed. When you
recalculate, you'll have a column of numeric codes formatted as text.
You then can copy these codes as values to the Codes area of your Excel
Labels--This is a short description of each row of data. Typically,
the label appears
only in your Figure Data Support (FDS) sheets to help you ensure that you've specified the code you
intended. (I explain FDS sheets below.)
Multiplier--The multiplier can be used for at least two purposes.
First, some external data is offered in thousands or millions.
For example, if you look at a company's financial statement online, the
numbers typically will be in millions. And some economic data is given
in thousands. This is a problem because your
internal data probably is expressed in ones. So unless you express all your data in
ones your ratios and your magnitude labels ("thousands", "millions",
etc.) will be incorrect.
Therefore, to fix this problem, just enter a multiplier for data
expressed in some magnitude other than ones. In the figure, for example,
the data beginning in cell F12 is in thousands. (That is, the value in
cell F12 is 1,948 thousand, which equals 1,948,000.) So the multiplier in cell D12
tells the FDS sheet to make the appropriate adjustment when it uses row 12's data
in its calculations.
Second, if you're an accountant, you might find it useful to maintain a
database of your General Ledger in an Excel database. Most general
ledgers maintain debits as positive numbers and credits as
negative numbers. This makes it difficult to get the sign right when
you're reporting GL data in dashboards.
To get around this problem, use the Multiplier column. Enter the value 1
for accounts with a natural debit balance and -1 for accounts with a
natural credit balance. When you do so, your dashboard will display
numbers as positive if they have their natural sign, and negative if
Dates--The dates in row 2 are date serial numbers. They all use the
convention that months are expressed using the first day of the month
for the day value. That is, the underlying date for Dec-2008 in cell BA2
Data--The data range is where the big numbers are shown. This
range can be as large or
small as you want (within Excel's limits, of course). Just be sure that as you add or delete rows or
columns you make your changes between the gray borders.
Adding Your Data
The original dashboard report is displaying the data shown in this
figure. If you erase the data, while adding your own, the dashboard will
display empty charts and errors. Therefore, for your first report, I recommend that
you add your
real data below the sample data, update your dashboards, and then erase
the sample data.
Here's how to update your database:
- Insert enough rows between rows 32 and 33 to contain your actual
- Insert additional columns between columns BA and BB to contain
your columns of data after December, 2008.
- Copy and paste your data into this new data area. Make sure that
you paste your data so that your dates are in sync with the dates in
row 2 above.
- Copy and paste your Codes and data Labels (descriptions) into
columns B and C.
- If you have target data, follow the same approach in the Tgt
sheet. If you don't
have target data, just erase the sample target data. To do so,
select the data and erase it by pressing the Delete key.
Now that your data is set up, let's set up your dashboard.
In your Reports directory, choose which of the twenty report workbooks you
want to use first. The workbook ReportGuide.xls will help you to decide
which report to use.
If you're in a big hurry, you probably should start with a report
that doesn't have a table. This is because you'll need to take
time to experiment with your tables.
Print a copy of the original report that you've chosen, then use that
page as a guide to help you decide what information you want each figure
As you decide which data goes where in your dashboard, this is a good
time to assign the section titles (originally like "Section Title 1" and "Section
Title 2"). These are designed to help your reader recognize different
logical groups of charts and tables in your report.
You can change the Section Titles, move them, add more, or erase them
entirely. Do whatever makes sense in your report.
report uses certain workbook-wide information, which you enter into the
top of the worksheet named Control, shown here.
Throughout the workbook, all cells where you'll typically enter data
are shaded in yellow.
The CurrentPd, (current period) value updates the date labels
for all charts in your workbook. Enter the month in this cell that
corresponds with your most-recent month of data. When you do so, make
sure you enter 1 for the day of the month, as shown in the next example.
your report is all set up, you can easily report an earlier period by
changing the date value. For example, to change your report...
...from 1/1/2007 (January, 2007)
...to 12/1/2006 (December, 2006)
...just change the date in cell B2 and then recalculate your workbook
by pressing the F9 key.
The YTDStartMo probably applies only if you're reporting
accounting data. This is the date for the first month for which you want
to calculate year-to-date (YTD) values. If you don't care about YTD
values, ignore this number. (The YTD column for Table 1, which is
supported by FDS sheet A, shows the YTD formula in use.)
In the bottom section of the figure above...
Enter your Company name.
Enter your country's CurrencySymbol and CurrencyName.
The LabelOption allows you to switch easily from Short-Scale
labels (like "Billions") to Long-Scale labels (like "Thousand
Millions"). Enter 1 for Short Scale or 2 for Long Scale.
(If your readers speak a language other than English, enter your
translation of "Thousand", "Billion", and so on in the ScaleLabels area of this
same Control sheet.)
Each report can display notes at the very top and bottom of your
report. Enter your text for these notes in the TopNote and
BottomNote cells, or erase the
contents of those
cells if you don't want to display a note.
is the first figure in Report 11 (IncSightDB_11.xls). Let's suppose you want to set it up
Activate Your Figure's FDS Sheet
Each figure gets its data from a particular Figure Data Summary (FDS) sheet. Your first step is to activate this figure's FDS
To do so, select cell C6 then tap your keyboard's down arrow once to active cell
C7. (You can't select cell C7 directly because the chart is on top of
that cell. So you must slide underneath the chart, so to speak.)
With cell C7 active, you see...
...in your formula bar. This tells you that cell C7 gets its information
from the FigTitle range name in its FDS sheet, sheet D.
To activate sheet D, merely press Ctrl+[. (That is, hold down Ctrl
and press the "[" key.) This selects the cell's
"precedents", which are the cell or cells that the formula refers to.
When you do so, Excel's GoTo command remembers your active cell when the
GoTo command was launched. So to return from the FDS to the report sheet,
press F5, Enter. To return to the FDS, press F5, Enter again...and so on
to toggle between the two worksheets.
Enter Your FDS Report Settings
the top-left corner of sheet D, the figure's FDS sheet. Enter or confirm
the following information for the yellow cells:
FigTitle--Enter your figure title, like "Unemployment Rate",
"Total Revenue", "Doctors on Staff", or whatever.
IsPercent--Enter TRUE to display the data for your chart as a
percentage, otherwise enter FALSE. When you do so, your chart will
display a number like .23 as 23 in the plot area, and the word "Percent"
will display in the bottom-left of the figure.
(If your readers speak a language other than English, enter your
translation of "Percent" in the ScaleLabels area of your report's
ShowUnits--Notice the label "Thousands of $" in the bottom
left corner of the chart figure above. If your chart title were something like
"Units Sold" you wouldn't want the currency symbol to display. So you
enter FALSE in the ShowUnits cell to cause the label to be only
AltUnits--Suppose your chart title still is "Units Sold" and
you want to show the unit of measure as "Shoes". To do so, enter "Shoes"
as the AltUnits (alternative units) value, and enter TRUE for
ShowUnits. After you do so, and press F9 to recalculate, the figure
will show "Thousands of Shoes".
Of course, the magnitude label adjusts as needed in all cases. So if you sold
only a few shoes, the label would be "Shoes". And if you sold
many, many shoes, the label would be "Millions of Shoes", as appropriate.
The SyncGroup and AllowSync cells allow you to synchronize the Y axes
of specified charts. I'll discuss them below. The remaining cells in the
figure contain formulas that support the FDS sheet. Don't modify them,
but feel free to explore them as you get the time.
Enter Your FDS Data Settings
is the last section of the Figure Data Summary (FDS) sheet that you'll need to set
up. The yellow cells in column E contain codes that correspond with the codes in
your Excel database.
For example, suppose that your database of Products Sold includes
product code B23XL. If you enter that label in cells E20 and E30 in
sheet D, then recalculate your workbook, all charts that get their data
from FDS sheet D will show the data for B23XL.
Often, you'll merely want to display numbers that already exist in
your Excel database. In this case, you'll only need to use one row of
formulas, as shown in rows 20 and 30.
At other times, you'll want to calculate ratios, a calculation that requires two
rows of numbers. In this instance, copy the entire row 20 to row 21 and
the entire row 30 to row 31. Modify the yellow formulas beginning in
cells H24 and H34 to
calculate the ratios you need. Then copy the formulas to the right as
shown here, the FDS worksheet J illustrates this approach by calculating
the ratio between two sets of values returned from the database.
When you calculate ratios that you want to express as percentages, remember to set the IsPercent value
in cell B4 above to TRUE. Here, for example, .89 is displayed as
At this point, your first figure is set up. Now set up the other
chart figures in the same way.
I list this step after you've added data to your report. This allows
you to view or print your actual report in a variety of colors, which
will help you to choose the color theme you like best.
But there's no particular reason to wait, if you don't want to. You
can experiment with the colors as the first step, if that's what you
want to do.
How to change color themes in Classic Excel
In Excel 97 through Excel 2003, open one or more of the workbooks in
the Colors folder. These workbooks have
names like Amandas Choice, Fuzzy Bruin, Quetzal, and so on.
Each of these workbooks has an image of a report and a color swatch at the top of
Sheet1. This information illustrates the colors contained in the workbook.
When you find an image you like, here's how to apply its colors to
your report workbook:
- Open the workbook with the colors you want.
- Select your report workbook.
- Choose Tools, Options, and then choose the Color tab.
- In the drop-down list box at the bottom of the dialog, select the
workbook that has the colors you want.
- Choose OK to apply the new colors.
How to change colors in New Excel.
Excel 2007 allows you to save custom color themes. Your first step is
to save one or more themes that you might want to use with your
To save a color theme, open one of the color theme workbooks. With
the workbook active, choose Page Layout, Themes, Colors, Create New
Theme Colors. In the Create New Theme Colors dialog, enter the name of the workbook (without
its extension, ".xlsx") as the name of the theme.
For example, suppose the theme workbook is named Gray Fields.xlsx. In the
Create New Theme Colors dialog, you would save the theme as Gray
After you've saved the themes you might want to use, activate
your report workbook. Choose Page Layout, Themes, Colors, and then
choose the theme you want from the list of Custom themes. Notice that as
you hover over any theme, Excel temporarily applies that theme to your
active sheet. Click on the theme name to select it.
In reports with tables, you'll see FDS worksheets that begin with A
or B. Sheets AP and AL contain one table design for either portrait or
landscape reports, respectively. Sheets BP and BL contain another design
for portrait or landscape reports. The reports use Excel's Camera tool to display a live image of each
table in the actual report.
My e-book, Dashboard Reporting With Excel, devotes an entire
chapter to the great things you can do with the Camera tool. But as an
overview, the tool uses a typical cell reference to define the range
that it returns. To illustrate, if you click on Table 1 in
the Report 5 workbook, you'll see the reference...
...in your formula bar. That is, the Camera tool references the range
named Table in sheet AP. Similarly, the Table 2 figure
If you double-click on a Camera tool object in a report (that is, if
you double-click the picture of the table), Excel will activate the
actual table range in an FDS sheet.
You can set up any tables you want for your dashboard; you aren't limited to the two
tables I've provided. When you do set up your tables, keep these rules
1. The FDS table sheets each have ranges named Table, which the Camera objects
rely on in the reports. Therefore, be sure to
build your new table within this named range in each of those worksheets.
2. Each new table should have the approximate size and shape of the
original table, otherwise, the Camera objects in your report will be too
large or too small to fit within your reports.
3. Landscape tables have slightly less vertical space available to
them than portrait tables. If i'm designing one table for both formats,
I'll create the table for a landscape design then increase the row
height slightly for a portrait design.
I've assigned Print Areas to each report.
So when you print a dashboard report, you won't need to take special
steps to keep from printing the brief instructions that are near each