Home > Excel Help Portal  >

How to Update Your Plug-

N-Play Report Each Month

To create your report for the first time follow the setup instructions for the Plug-N-Play Excel Dashboard Kit #1. Here's how to update your data for the months after that:

Open your report from the previous month and save it under a new name. For example, if you're going to create a report for October, 2007, you might save it as MyReport 2007-10.xls. (if you're using Excel 2007 the extension would be "xlsx".)

Change the CurMonth date in the Control sheet to the new month.

Before you enter your new data, you need to shift your old data to the left by one column...without messing up the formulas in your data sheets. To do so, simply copy the data in each data sheet one column to the left.

To illustrate, here's the actual and target data for the Montréal figure:

To shift the data one column to the left, you will need to copy the range F12:Q26, and then paste it to cell E12. This shifts everything to the left by one column while protecting the formulas. Then you will enter date and data for the new month in column Q.

Here's how to take these steps quickly and easily:

1. Activate a new supporting worksheet that you want to update. In the above figure, for example, I activated worksheet E.

2. Press the F5 key to launch the Go To dialog. As shown here, enter "D" or "d" (without the quotes) in the Reference box.

"D" is the name of a range I've defined for the area that you'll need to copy. The "D" stands for "Data".

At this point, your worksheet should look something like this:

3. Press Ctrl+C to copy the selected range.

4. Press your left arrow once to move the active cell from cell F12 to E12, or select cell E12 with your mouse.

5. Press Ctrl+V to paste the copied range one column to the left.

6. To enter the new date in cell Q12, edit the formula as necessary. For example to change Sep-07 to Oct-07, change 9/1/2007 to 10/1/2007.

7. Enter your new data in column Q.

When you're done updating one worksheet, you can press Ctrl+Page Down to move to the next worksheet.

You probably can shift your data more quickly if you go from page to page, copying and pasting. (You probably can shift all the data in less than three minutes.) Update all the dates in cell Q12 of every sheet that supports a chart. Finally, enter your data for the new month one sheet at a time.

The more efficient approach is set up your report to get its data from an Excel database or other source. With this approach, your report updates automatically when you change your report date. Our e-book, Dashboard Reporting With Excel, explains how to do this.