Excel 2007: Take Control of Changing Data with Dynamic Charts – Part 1

By David H. Ringstrom, CPA

Charts often present special challenges for Excel users. For instance, the data being presented may shift from month to month, such as a rolling six-month chart. Or, you may have a chart that you manually expand to show the latest month’s data. In this article I’ll present some techniques that you can use to make your charts automatically incorporate new data. This month I’ll demonstrate a technique that creates a rolling six-month chart. Next month I’ll show you how to create charts that automatically expand to present new months of data. I’ll start out with the steps for Excel 2007, and then provide guidance in Excel 2003 for those that haven’t upgraded yet.

Try it now:  A free 60-day trial of Office 2007 is available for immediate download. Note that you can instruct the installation program to leave your present version of Office intact so that you’re free to work in either version. Or, try the online test drive.

Transition tools: Office Online offers an online command reference that shows where your favorite Excel 2003 commands reside in Excel 2007. You can also download and install the command reference tool on your computer. Yet another option is to add the Get Started tab to your Excel 2007 ribbon — this puts numerous training aids right at your fingertips.

Create a Rolling Six-Month Chart in Excel 2007

The first step is to create some sample data to work with. Carry out these steps in a blank workbook:

1. Enter 1/1/2008 and 2/1/2008 in cells B1 and C1, respectively.
2. Select cells B1 and C1, and then drag the fill handle across to column M to create a series of dates through 12/31/2008.

Fill handle: The fill handle is the little black notch in the lower right-hand corner of the current cell or selection. When you select cells B1 and C1, the fill handle is in cell C1.

3. Select columns K through M, and then double-click on the worksheet frame between columns K and L. This automatically widens the columns to display the full date instead of pound signs.
4. Enter the word Sales in cell A2.
5. Select cells B2 through M2, type the formula below, and press Ctrl-Enter:

=RANDBETWEEN(1000,2000)

Ctrl-Enter: This shortcut key transfers the formula to the selected cells all at once. Alternatively you can enter the formula in one cell, and then drag across into the adjacent cells.

RANDBETWEEN: The RANDBETWEEN function generates random numbers within a range that you specify, in this case between 1000 and 2000.

6. Press Ctrl-C to copy cells B2 through M2 (these cells should already be selected if you used the Ctrl-Enter trick) to the clipboard, and then right-click on cell B2 and choose Paste Special, and then double-click on Values. This provides sample data for the chart that we’ll build in a moment.

Next we’ll create a look-up table that will generate the data for our chart:

1. Enter 7/1/08 in cell G4.
2. Enter this formula in cell F4:

=DATE(YEAR(G4),MONTH(G4)-1,1)

DATE: The DATE function in Excel creates a calculated date from three inputs: year, month, and day. In this instance we’re using the YEAR function to return the year from cell G4 and the MONTH function to get the month from cell G4. I subtracted 1 from the resulting month, and entered 1 for the day argument. I also could have also used the DAY function, which works just like YEAR and MONTH.

3. Copy the formula from cell F4 to cells B4 through E4.
4. Enter the word Sales in cell A5.
5. Select cells B5 through G5, type the formula below, and then press Ctrl-Enter:

=SUMIF($1:$1,B$4,2:2)

SUMIF: In layman’s terms, the SUMIF function has three arguments: where to look, what to look for, and what to add up. In this case we’re instructing the function to look at row 1 for the date in cell B4. Whenever it finds a match, SUMIF adds up the values in row 2. I find SUMIF to be a great alternative to HLOOKUP when I’m working with numbers. The arguments for row 1 and cell B4 contain dollars signs, because I always want the formulas to look at rows 1 and 4, respectively. This means that I can copy the formula down additional rows without modification, should I decide to incorporate more data into my chart.

We’re now ready to build our chart:

1. Select cells A4 through G5.
2. Choose Column in the Charts section of the Insert tab of the Excel 2007 ribbon, and then select Clustered Column — this the first chart in the 2-D section. A chart should appear on your worksheet, as shown in Figure 1.

Figure 1: A rolling six-month chart based on a subset of the data in rows 1 and 2.

3. You’re now ready to see the dynamic nature of this chart. Enter the date 9/1/08 in cell G4. The values in the adjacent cells — as well as the chart itself — should change automatically.

Now let’s format the dates in month/year format, such as Jan-08 instead of 1/1/2008:

1. Select cells B4 through G4, and then click the arrow in the bottom right-corner of the Number section of the Home tab.

2.  When the Format Cells dialog box appears, choose Custom from the Category list on the Number tab, enter mmm-yy in the Type field as shown in Figure 2, and then click OK.

Figure 2: A custom number format transforms dates like 9/1/2008 into Sep-08.

3. The finished chart should look similar to Figure 3.

Figure 3: The chart now shows dates in mmm-yy format, instead of m/d/yyyy.

There are many ways that you can build on this basic functionality that we just put in place:

• Add as many months of data as you like on rows 1 and 2
• Extend the data subset on rows 4 and 5 for as many months as you need. If you do so, remember these pointers:
• The latest date on row 4 should be the last month on your chart. The rest of the dates should be formula-based. Whenever you want to update the chart, you’ll type over the last date in the subset range.
• You’ll need to manually change the data range for your chart after you expand the subset range. If your chart is simple, simply delete the chart and then rebuild it. Alternatively, right-click the bars on the chart, and then choose Select Data. As shown in Figure 4, change the Chart Data Range to match your expanded subset range.

Figure 4: The Select Data Source dialog box enables you to expand the data range shown on your chart.

• Move the chart to another location, such as a chart sheet. To do so, right-click on the chart, and then choose Move Chart.
• Move the data subset to another worksheet, either by dragging with your mouse or cutting and pasting the data.
• You can add more rows of data if you wish to present multiple series on your chart.

Create a Rolling Six-Month Chart in Excel 2007

Start with a blank worksheet, and then carry out these steps:

1. Enter 1/1/2008 and 2/1/2008 in cells B1 and C1, respectively.
2. Select cells B1 and C1, and then drag the fill handle across to column M. This will create a series of dates.
3. Select columns K through M, and then double-click on the worksheet frame between columns K and L to automatically widen the columns to display the full date instead of pound signs.
4. Enter the word Sales in cell A2.
5. Select cells B2 through M2, type the formula below, and press Ctrl-Enter:

=RAND()*1500

RAND: The RAND function generates random numbers with values between zero and one, which in this case I multiply by 1500 to generate sample data. Excel 2003. RANDBETWEEN is also available in Excel 2003 if you install and enable the Analysis ToolPak.

6. Press Ctrl-C to copy cells B2 through M2 to the clipboard. Right-click on cell B2, choose Paste Special, and then double-click on Values.

Create a look-up table that will generate the data for the chart:

1. Enter 7/1/08 in cell G4.
2. Enter this formula in cell F4:

=DATE(YEAR(G4),MONTH(G4)-1,1)

3. Copy the formula from cell F4 to cells B4 through E4.
4. Enter the word Sales in cell A5.
5. Select cells B5 through G5, type the formula below, and then press Ctrl-Enter:

=SUMIF($1:$1,B$4,2:2)

6. Select cells A4 through G5, and then choose Insert, Chart, and then click Finish.

You’re now ready to see the dynamic nature of this chart:

2. Enter the date 9/1/08 in cell G4. The values in the adjacent cells — as well as the chart itself — should change automatically.
3. Select cells B4 through G4, and then choose Format, and then Cells.
4. Choose Custom from the Category list on the Number tab, enter mmm-yy in the Type field as shown in Figure 2, and then click OK.
5. The finished chart should look similar to Figure 3.

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.