Home > Dashboard Digest > Setup > 

ExcelUser Instructions:

How to Set Up IncSight PNP (PNP1)

After you have your data available, you should be able to set up your first dashboard
report in several hours. Here's how to set up that report.

by Charley Kyd

1. Save a copy of your files.
2. Choose a report sheet
3. Gather and arrange your data.
4. Enter your general data.
5. Enter data for your charts.
6. Set up your tables.
7. Choose a color theme.
8. Print your report.
9. Get more information about Excel dashboards.

Appendix: How to Do Group Editing In Your Excel PNP Dashboard Reports
Appendix: Making Changes to Your PNP #1 Excel Report Workbook

Before you start to set up, let's take a quick look at the files you've downloaded.

When you first downloaded your purchase, you received a zip file containing thee files:

• A short "ReadMeFirst.txt" file that explains how to find these instructions.
   
• Ten report files in a Reports folder.
   
• Fifteen files with color schemes in a Colors folder. When you open any of these files you'll see a small image of what your report 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".

Now let's set up your report...

1. Save a copy of your files.

First save your download files where you can find them later. Then save a working version of the report workbook.

To illustrate the working version, if your report will be for July, 2009, you would name the report workbook something like My Report 2009-07.xls.

Initially, save the 15 color theme workbooks with your working version. You'll probably delete those copies later. (You'll keep the originals, of course.)

2. Choose a report sheet.

In your report workbook, choose which of the ten report sheets you want to use first. You can delete the other report worksheets, or leave them in your workbook. It's your choice.

If you're in a big hurry, you probably should start with a report sheet that doesn't have a table. This is because you'll need to take the time to experiment with your tables.

3. Gather and arrange your data.

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.

And as you gather your data, notice that all charts are designed to display 13 months of actual and target data. Here's why we use 13 months of data:

1. It allows your readers to compare the current month with the same month one year ago.
   
2. It eliminates the artificial information barrier typically imposed by fiscal-year boundaries.
   
3. It generates more readable charts.
   

4. Enter your general 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.

Initially, the CurMonth, (current month) value updates the date labels for all charts in your workbook. Therefore, be sure to 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.

It's possible to set up your report so that changing the CurMonth cell actually updates the data in your report. Plug-N-Play #2 does this automatically. And my e-book, Dashboard Reporting With Excel, explains how to do this.

Each report can display notes at the very top and bottom of your report. Enter your text for these notes in cells B9 and B10, or erase the contents of those cells if you don't want to display a note.

Note: After you enter data in your PNP report workbook, remember to recalculate Excel by pressing F9. Until you do so, your report won't display the new data.

5. Enter the report data for your charts.

Before you get started with this step, save your report workbook. That way, if you do mess it up you can always recover.

Turn the chart-date heading formulas to values

If you haven't done so, enter the most-recent month of your data as the CurMonth in the Control sheet, and then recalculate your workbook. Doing so updates the date headings in every worksheet that supports your charts (worksheets with names like C and F).

Now that these headings are updated, you need to turn them into values. Doing so ensures that your data and dates never will be out of sync.

In one operation, you can turn the dates for all the chart-form worksheets into values. To do so, click on tab D in your report workbook. Hold down your Shift key, then click on tab Y. Doing so selects all the form sheets that support charts.

Now select the yellow date area in sheet D, the left side of which is shown here:

To copy these cells, press Ctrl+C. To paste them as values...

...In Classic Excel, choose Edit, Paste Special. Choose Values, and then OK.

...In New Excel, choose Home, Clipboard, Paste, Paste Values.

Now that you've turned these formulas into values, and this is very important, select some sheet that isn't currently selected, say sheet A. Doing so deselects the group of worksheets and makes sure that changes you make to one of the sheets doesn't affect them all.

How to move around your workbook easily

This figure is from the Rpt7 worksheet. It shows a typical chart from PNP #1. The number at the top of the figure makes it easy to refer to each figure when you're discussing your reports with others.

These numbers are returned by simple formulas in each report worksheet, and you don't need to update them.

You do maintain the other items in this figure by using its supporting worksheet. But to update that supporting worksheet you first need to activate it. You can do this easily.

Select the cell with the figure number (cell G6 in the figure above), and then press your down-arrow key one time to activate the cell that contains the chart title (here, cell G7). (You must take this two-step approach because the chart gets in the way if you try to select the title cell directly.)

When the title cell is active, you'll see a formula like this in your formula bar:

=G!FigTitle

That is, this particular formula returns its text from the FigTitle range name in worksheet G. To select that source cell, choose Ctrl+[. (That is, hold down Ctrl and press the "[" key.)

To return to your report after updating your worksheet, press F5 (which launches Excel's Go To dialog) and then press Enter. (This step works because when you go to a cell using Ctrl+[ or by using the Go To dialog, Excel uses the "from" cell as the default Go To range the next time you press F5.)

Update your chart's title and unit of measure

To update your chart data for a figure, go to its supporting worksheet. At the top of the sheet you'll find a small area where you enter its title and unit-of-measure information.

In cell B1, enter a FigTitle like "Sales" or "Headcount" or whatever.

In cell B3, enter a ScaleType of 1 to display your data as percent. If your data must be displayed in ones, enter 2. If it can be displayed in thousands, enter 3. If in millions, enter 4. And so on.

In cell B4, enter an IsCurrency value of TRUE if your data is currency, like Euros or dollars. Otherwise enter FALSE.

As you change values in cell B3 and B4, and then recalculate, you'll notice that the units label in cell B6 updates to reflect your changes. All chart figures display this label in their bottom-left corner.

Update your chart's data

Enter your data and descriptions in the yellow areas of your figure's supporting worksheet. For example, the following figure shows the first few months of the default data for Montréal, in sheet E.

The figure shows that two sets of four rows are available to contain your data. This allows you to enter data that you can summarize with a formula in each Result row. For example, the figure above relies on two rows of data to calculate a ratio, which is then charted. Here's the simple formula that calculates the ratio in the cell shown:

E19:  =E15/E16

Of course, if you enter only one row of data in row 15, change the Result formula to be...

E19:  =E15

...and then copy it to the right as necessary.

Be sure to enter your data without scaling adjustments. That is, enter it in ones, not in thousands, millions, and so on. Otherwise, the unit-of-measure labels will be incorrect.

If you don't want to include a Target measure in a chart, enter zeros for the chart's Target data, or just erase the data in the Target area.

Tip...How to speed data entry using paste special

If your data is in another workbook, or in a file that you can open in a workbook, you can copy and paste your data rather than entering it manually. However, if you paste it normally, you'll destroy the formats, including the borders and yellow shading.

Also, there's a chance that some of your data will be in columns rather than rows.

You can solve all these problems using the Paste Special dialog.

To do so, copy 13 months of your source data...either as a row or column. Select the left-most cell in your target area (cell E15 above, for example). And then...

...in Classic Excel, choose Edit, Paste Special.

...in New Excel, choose Home, Clipboard, Paste, Paste Special.

When you do so, Excel launches the Paste Special dialog:

Choose Values to paste only the values and not the formatting. If the data is in columns, also choose Transpose. Then choose OK.

6. Set Up Your Tables

Worksheets A and B contain tables used in several of the plug-n-play 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 Rpt5 worksheet, you'll see the reference...

=A!Table

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

=B!Table

You can set up any tables you want for your dashboard; you aren't limited to the two tables we've provided. When you do set up your tables, keep these rules in mind:

1. Sheets A and B 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 the two 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 the reports.

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 other workbooks that came with the report workbook. These other workbooks have names like A Gray Day, Civic, Giraffe, WSJ, 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 with 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 Civic.xlsx. In the Create New Theme Colors dialog, you would save the theme as Civic.

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. Print your report.

I've assigned the Print Area to each range that contains a dashboard. 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.

9. Where to get more information about Excel dashboards.

You can find information about other Excel dashboard solutions at the link.