Home > Dashboard Digest > Setup > 

ExcelUser Instructions:

Appendix: Making Changes to Your
PNP #1 Excel Report Workbook

One significant advantage to using Excel for your dashboard reporting is that you can
make just about any changes you want, and quickly. Here's advice to get you started.

by Charley Kyd

You'll find PNP#1 initial setup instructions at the link. Here are the topics covered in this appendix:

How to Adjust the Colors
How to Change a Chart's Source Data
How to Change the Date Formats
How to Correct a Chart's Y-Axis Label

Plug-N-Play (PNP) dashboard reports are merely formatted worksheets with a bunch of small charts and a few tables. No macros are involved. So you're free to modify these reports any way you want.

Although you can modify these dashboards, you certainly aren't required to do so. That is, if you don't know much about Excel and don't care to learn more, feel free to ignore this article.

Assuming you do want to modify your report, be sure to save the workbook under a new name. That way, you always can recover if you mess things up.

In general, use standard Excel techniques to modify your reports. Change formatting, move charts around, add more tables, and so on.

Feel free to be creative. After all, it's only Excel!
 

How to Adjust the Colors

You'll probably want to adjust the intensity of the colors of your dashboard report.

The background color for dashboards is intended to be faint. If the color is too intense, the report looks garish, and it wastes ink. But if it's too faint on your printer, or too bright, you're going to want to adjust the color.

And you'll probably want to adjust other colors, as well. This is your report, after all.

Adjusting the colors is easy to do. But be sure to save your workbook before you start, because Excel's Undo command doesn't work with these color adjustments.

How to adjust colors in Classic Excel

You take two steps to adjust the colors in Excel 2003 and earlier: Determine which color to adjust, and then adjust it.

To determine which color to adjust, select the cell or object whose color you want to adjust. Click on the Color Fill icon in your Formatting toolbar. Or else choose Format, Cells, and then choose the Patterns tab.

In either case, you'll see a display like this, with the color of the active object highlighted.

Here, the top-right color is highlighted. That position is where the table background color always is found in the PNP dashboards.

The figure below maps all color positions used in PNP dashboards for Classic Excel. (The chart styles referred to in this figure are illustrated in the next section below.)

0 Table Background 1 Figure Number Cells 2 Sheet Background 3 Chart Style 1: Background 4 Chart Style 1: Area Plot 5 Chart Style 2: Background 6 Chart Style 2: Area Plot

To adjust a color, choose Tools, Options, Color.  In this dialog, choose the color you need to change. Then choose Modify.

In the Colors dialog, choose the Custom tab. As shown here, you'll see the color in a vertical band, with a black arrow that serves as a slider.

Click and drag that slider up or  down to vary the intensity of the color, from white to black.

To vary the hue (to make it more red, or blue, or whatever) change the Red, Green, or Blue numbers, or click and drag the white cross around the face of the color display.

When you have the color you want, choose OK.

How to adjust colors in New Excel

In Excel 2007 it's not always possible to determine which color position needs to be adjusted.

If you select a cell you can learn this information. As in Classic Excel, choosing the Fill Color icon from the Home tab will show a color palette with the color position highlighted.

But certain chart objects offer no way to determine their color position.

Therefore, to adjust the color of a PNP dashboard, you'll often need to refer to the figures below.

0 Table Background 1 Figure Number Cells 2 Sheet Background 3 Chart Style 1: Background 4 Chart Style 1: Area Plot 5 Chart Style 2: Background 6 Chart Style 2: Area Plot

The left figure labels seven color positions, which are explained in the table.

For example, the Light 2 color position is labeled zero, and the table shows that we use this color position for the backgrounds of tables in plug-and-play dashboards.

For variety, most PNP dashboards use two different styles of chart figures, which this table refers to. This dashboard image labels these styles.

Once you've determined which color position you want to change, choose Page Layout, Themes, Colors, and then right-click the highlighted custom color and choose Edit. (If a custom color isn't highlighted, choose the custom color and then return to right-click your highlighted choice.)

In the Edit Theme Colors dialog, choose the color you want to change. Then choose More Colors at the bottom of the Theme Colors palette. Doing so launches the same Colors dialog shown for Classic Excel above. Use the same approach to adjust New Excel's theme color.

How to Change a Chart's Source Data

To point a chart figure at data in a different supporting worksheet, just change the sheet name that the four formulas in each figure reference.

To illustrate, this figure from Report 2 uses these four formulas to return data about Rome:

K7:  =F!FigTitle
The figure title

=SERIES(,F!Month,F!Actual,1)
The line chart object.

=SERIES(,F!Month,F!Target,2)
The series chart object.

K9:  =" "&F!UnitsLabel
The label with the units of measure.

If you changed "F" to "K" in these four formulas, Figure 4 would return information from sheet K, which initially has a figure title of "Los Angeles".

How to Change the Date Formats

In the small chart above, there's not room to display "Sep", "Nov", and so on. So the chart uses numbers to indicate the months. But larger charts do have room to use three-letter abbreviations for the months, as the following figure illustrates.

Each worksheet that supports a chart has two sets of formatted dates, as shown here:

If your charts' series formulas refer to the Month range like this...

=SERIES(,T!Month,T!Actual,1)
=SERIES(,T!Month,T!Target,2)

...they'll display numbers in their X axis.

But if your series formulas refer to the AltMonth range, like this...

=SERIES(,T!AltMonth,T!Actual,1)
=SERIES(,T!AltMonth,T!Target,2)

...they'll display the three-letter abbreviations for the the month in their X axis.

To change the formulas, just click on the line object and then the area object in a chart and change the SERIES formula to either Month or Altmonth.

Strictly speaking, only the first series formula needs to be changed. But it's usually a good idea to change them both.

How to Correct a Chart's Y-Axis Label

One minor frustration with Excel is that it doesn't automatically adjust the number format of the Y axis labels to their most efficient setting. These charts from Excel 2007 illustrate the problem.

In the left chart, the number format obviously needs to be changed. After all, we can't have repeating numbers in our Y-axis labels. The center chart shows how the values should be displayed.

The right chart shows the opposite problem. Having too many decimal values clutters the display and reduces the horizontal size of the chart.

We must correct this problem manually. To do so, select the Vertical axis. Press Ctrl+1 to launch the Format Axis dialog. In the Number tab, enter the number of decimal places you want, as a value. Then...

• In New Excel (2007), choose Add, and then Close.
  
• In Classic Excel, choose OK.