Home > Dashboard Digest > Setup > 

ExcelUser Instructions:

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.

by Charley Kyd

1. Save a copy of your files.
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.

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...
 

1. Save a copy of your files.

When you first downloaded your purchase, you received a zip file that contains 43 files in three folders:

1. 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.
    
2. 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.
    
3. 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.
 

2. Set up your data.

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. This 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).

Here 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 account "C1200".

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", and "D0579".

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 formula...

="D"&TEXT(A4,"0000")

...or, if you would like to create a code that's easier to read, like "D-05-79", you could use...

="D"&TEXT(A4,"-00-00")

...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 database.

  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 they don't.

  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 is 12/1/2008.

  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:

1. Insert enough rows between rows 32 and 33 to contain your actual data.
    
2. Insert additional columns between columns BA and BB to contain your columns of data after December, 2008.
    
3. 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.
    
4. Copy and paste your Codes and data Labels (descriptions) into columns B and C.
    
5. 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.
 

3. Choose a report workbook.

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 extra time to experiment with your tables.
 

4. Plan your report layout.

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 to display.

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.
 

5. Enter your Control data.

Each 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.

After 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.
 

6. Set up the Figure Data Summary (FDS) sheet.

This is the first figure in Report 11 (IncSightDB_11.xls). Let's suppose you want to set it up first.

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 sheet.

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...
=D!FigTitle
...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

Here's 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 Control sheet.)

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 "Thousand".  

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

This 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 needed.

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 89 Percent.

At this point, your first figure is set up. Now set up the other chart figures in the same way.

7. Choose a color theme.

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:

1. Open the workbook with the colors you want.
2. Select your report workbook.
3. Choose Tools, Options, and then choose the Color tab.
4. In the drop-down list box at the bottom of the dialog, select the workbook that has the colors you want.
5. 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 reporting.

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 Fields.

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.
 

8. Set up your tables

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...

=AP!Table

...in your formula bar. That is, the Camera tool references the range named Table in sheet AP. Similarly, the Table 2 figure references...

=BP!Table

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 in mind:

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.

9. Print your report.

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 dashboard.