|
Home > Dashboard Digest
> Setup
>
ExcelUser Instructions:
Appendix: How to Make Other Changes
To Your PNP #2 Excel Dashboard Reports
You can change several Excel worksheets at one time. Here's how to use this feature
to save a lot of time when you work with Excel dashboard reports.
by
Charley Kyd
This appendix topic applies to all PNP versions. You can find other
setup instructions Excel Plug-N-Play dashboards
here.This appendix covers the following topics:
How to add or delete time periods in your report.
How to change from months to some other time period.
How to synchronize your charts' Y axes.
When you look at the sheet tabs in your report
workbook, you'll see two colors, gray and yellow.
I assigned the yellow colors to the charts' FDS tabs because the colors tell me
that these sheets allow me to use group editing. So what's group editing?
In Excel, you can select multiple sheets by selecting their tabs much
as you select cells in a worksheet. That is:
- Select one sheet by clicking on its tab.
- Select a continuous group of sheets by holding down your Shift
key and then clicking on the tab that's at the end of the group you
want to select.
- Select specific sheets by holding down your Ctrl key and then clicking on
the sheets you want to add to your group of sheets.
- To deselect the group, click on a sheet that's not in the group.
Or, if all sheets in the workbook are selected, click on a tab of any sheet
that's not the active sheet.
When more than one sheet is selected, Excel adds the text "[Group]"
after the name of your workbook at the very top of your Excel window.
The reason you will want to select a group of worksheets is that when
you change one of the sheets in a group you also change every other
sheet in the group at the same time. This can save you many hours of
work!
Warning! Remember to deselect the group immediately after
you've made the changes you intend. Otherwise, changes you want to
make only to one sheet will be applied to all sheets in the group.
The reason I assigned a different color to the tabs in the report
workbook is that the color tells me which worksheets allow me to edit
them as a group.
To see why you can edit them as a group, start with sheet D, then move to sheet E, and F, and so on. Notice
that although the data varies from sheet to sheet, the structure of each
sheet is identical. Actual data always begins in row 17. Chart data
always begins in row 45. And so on.
The formulas are the same as well. The formula in cell B10 is
identical in all worksheets with the yellow tab.
Therefore, if you need to change the structure, the formulas, or the
formatting in one of the chart FDS sheets, you should select the
entire group of sheets and make the same changes to them all.
I've explained group editing because it applies to the following
changes you might want to make to your reports.
By default, all charts are designed to
display 13 months of actual and target data. I use this design because:
- It allows your readers to compare the current month with the same
month one year ago.
- It eliminates the artificial information barrier typically imposed by
fiscal-year boundaries.
- It forces Excel to label both the first and last period in the
chart's X axis.
If you want to use a different number of reporting periods, or if you
want to use a reporting period other than month, that's easy to do.
First, save your workbook before you start the following process.
This will allow you to recover if necessary.
Next, you need to decide whether this change will apply to one FDS
sheet or to more than one.
Suppose the change will apply to some FDS sheets, but not to all of
them. Because you'll change the structure of only some of the FDS
sheets, arrange them as a group. Begin by assigning the same new color
to the tabs of the sheets where you plan to make the change.
(Right-click and choose Tab Color.) Then, if necessary, click and drag
the sheet's tabs so that they're side-by-side in their own group.
On the other hand, if the change will apply to all FDS sheets of the
same color, you can just proceed with the change.
Select all tabs in the group of sheets you plan to modify. To do so,
click the left-most tab in the group; hold down your Shift key, then
click on the right-most tab.
(WARNING! As soon as you're done making changes to the group
of worksheets, be sure to deselect the group. If you forget to do this,
changes you intend to make to one of the worksheets will continue to
apply to all of the ones selected.)
 Suppose you want to add more periods to your charts. This means you need
to add more columns to the body of your FDS sheet. To do so, insert the columns
somewhere near the middle of your data area, as shown here.
Now that you've inserted the columns you need, you must copy a column
of
formulas to that empty space and to the column of formulas on each side
of the empty space. (You need to replace the the formulas on each side
because the formulas often refer to cells to their immediate left or right.
So you need to
replace the formulas to correct their references after you insert
columns next to them.) Therefore, copy a
column of formulas that is two or
three columns away from the area you inserted, as shown in this figure.
Now be sure to deselect the group of worksheets, as the warnings urge
you to do.
If you want to delete columns rather than add them, be sure to
copy formulas from several columns away from the deleted area to the two
columns on each side of the deleted area.
Finally, recalculate your workbook by pressing F9. Then check to make
sure your modifications were successful. Check the FDS sheets you
changed to look for any errors, and check your charts for reasonability.
The reports are set up for monthly reporting. But you can could
easily report days, or quarters, or even hours. Here's how:
Cell T18 in the charts' FDS sheets contains the formula for the cell
shown...
T18: =CurrentPd
Cell S18 contains a formula that returns the date one month prior to
the date in cell T18. The formula in cell S18 has been copied to the
left as needed.
Therefore, to switch from months, to some other time period, your
first step is to modify the formula in cell S18. Here are some examples:
| S18: |
=T18-1
(Returns the date one day prior to the date in T18.) |
| S18: |
=T18-7
(Returns the date one week prior to the date in T18.) |
| S18: |
=DATE(YEAR(T18),MONTH(T18)-3,1)
(Returns the first day of the month three months prior to the
month in cell T18.) |
| S18: |
T18-TIME(1,0,0)
(Returns the date-time that is one hour prior to the date-time
in cell T18.) |
Once the formula in cell S18 gives you the new time period you want,
copy it to the left as needed.
 Your charts don't get their time information directly from row 18,
however. Instead, they rely on two sets of formulas in rows 46 and 47 in
your FDS sheets. These rows are titled Month and AltMonth (Alternative
Month). Because small charts use the Month range, it presents date
information using as few characters as possible. To illustrate, it
expresses the "March" as "3". But because larger charts have more space,
they use AltMonth, which would express "March" as "Mar".
When you change from months to days to hours, you'll need to
experiment to find the format that meets your needs. You'll probably use
two Excel functions in your Month and AltMonth formulas: TEXT and CHAR.
The TEXT function displays a number (including a date serial number)
as text. And we use CHAR(13) to return a carriage return character,
which causes charts to put the characters that follow in a second row of
text. In this figure, for example, the year values "06" and "07" are
below the month values because formulas in the Month row of the chart's
FDS sheet included CHAR(13).
(Take a look at the month formulas in the green area of sheet D in any
of your reports to see how this works.)
Assuming that cell A1 contains the date-time 1/31/2009 11:00:28 PM,
here are some formatting examples to consider:
=TEXT(A1,"d")&CHAR(13)&TEXT(A1,"mmm")
(Returns "31" on the first line of your X-axis label and "Jan"
on the second line." |
=TEXT(A1,"h")&CHAR(13)&TEXT(A1,"d")
(Returns hour "23"on the first line and date 31 on the second
line.) |
=TEXT(A1,"h am/pm")&CHAR(13)&TEXT(A1,"d/mm")
(Returns hour "11 PM" on the first line and day/month 31/01 on
the second line |
My Excel
Dashboard Kit includes the e-book, Dashboard Reporting With Excel,
which discusses CHAR(13) and date formatting in more detail.
Excel chart synchronization
allows you to visually compare the relative performance of two or more
data series.
To
begin, think of a name that you want to assign to a group of charts
you want to keep in sync. Let's call it "Regions".
Enter the name in the SyncGroup cell (here, cell B7) for each chart
that you want to be a member of the Regions group.
 Also enter TRUE in the AllowSync cell. I've made the same changes in the Montreal FDS sheet.
Next, go to the Sync worksheet in your report workbook. There, you'll
see two tables that support chart synching. The top of the second table
looks like this:
The SyncGroup Table uses two columns to control the
chart-synchronizing process for each Sync Group you want. You can see
that I've set up two Sync Groups, Regions and People.
To complete the setup of the Regions group, enter the name "Regions"
in cell J6, as I've done here, and TRUE in cell K6.
 Now,
when you recalculate your workbook by pressing F9, your charts will be
in sync, as shown here.
If you want to stop all charts in a group from synching, enter FALSE
in the group's Use cell, as I've done above for the People
group in cell M6. If you want to temporarily remove any chart from its
group, enter FALSE in the AllowSync cell of the chart's FDS sheet. That
is, in the figure at the right above, enter FALSE in cell B8.
|
