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:
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:
Of course, if you enter only one row of data in row 15, change the Result formula to be...
...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.
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...
...in your formula bar. That is, the Camera tool references the range named Table in sheet A. Similarly, the Table 2 figure references...
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.
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:
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.
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
You can find information about other Excel dashboard solutions at the link.