Creating a Scrolling Chart in Excel 2007:-

An interesting dynamic chart is a chart that shows a scrolling 12 months of data. In the Figure (given below), 36 months of data are available and are shown in the bottom chart. However, you can show a scrolling 12 months in the top chart. As you move the scrollbar, the top chart shows a closer view of a 12-month segment of the longer chart.



(When the scrollbar in row 2 is scrolled toward the right, you see recent months.)


In the above Figure, a gray box shades the portion of the bottom chart that is shown in the top chart.

In the Figure (given below), the scrollbar is moved left to show a different portion of the detail.



(Note the number 9 in cell D1.This cell changes in response to the scrollbar changes.)

You follow these steps to create a scrolling chart:

1. Format your dates with a MMMMMYY custom format. The five M’s are the code to force the month to be displayed with only the first letter of the month. This allows more dates to fit across the bottom of the chart.

2. Select the data in cells A1:B37. From the Insert ribbon, choose Line, Line. Delete the legend. Excel creates a basic line chart with all 36 months of data.

3. Move the chart so that it starts in row 22. Make a copy of the chart that starts in cell D3.

4. Deselect the chart. Enter a number between 1 and 25 in cell D1.

5. From the Formulas ribbon, choose Name Manager. Click the Add button.

6. Type Months in the Name field. In the Refers To field, enter:

=OFFSET(Scroll!$A$1,Scroll!$D$1,0,12,1)

This says to start from cell A1 of the Scroll worksheet, move down the number of rows in cell D1, and then take a range that is 12 rows by 1 column. Click OK to accept the name.

7. Add another name. Type the name SalesByMonth. In the Refers To box, use:

=OFFSET(Scroll!$A$1,Scroll!$D$1,1,12,1)

The only change is that in the third argument, you move right one column to grab data from column B.

8. Click the line series in the first chart. Wait for the SERIES formula to appear in the formula bar. Edit the formula so that it looks like this:

=SERIES(Scroll!$B$1,’7-Dynamic.xlsm’!Months, ➥‘7-Dynamic.xlsm’!SalesByMonth,1)

9. Test the chart. Enter a new number between 1 and 25 in cell D1. The top chart should change. You now need to provide an easy way for the reader to change the number in cell D1. This can be accomplished through a scrollbar.

The icon for adding a scrollbar is located on the Developer ribbon. Open the Insert dropdown and find the Scrollbar icon in the center of the second row of Form Controls. Since many people do not have the Developer tab available in the ribbon, you can use Step 10 to add the scrollbar control to the Quick Access Toolbar.

10. From the Office icon menu, choose Excel Options. Choose Customize. In the top-left drop-down, choose Command Not in the Ribbon. Browse through the list box on the left for Scroll Bar (Form Control). Select this item and click the Add>> button to add the control to the Quick Access Toolbar. Click OK to close the Excel Options dialog.

11. Click the newly added scrollbar control in the Quick Access toolbar. (Depending on your computer, the Quick Access toolbar is the row of icons either immediately below or immediately above the ribbon.) After clicking the scrollbar icon, drag to draw a scrollbar control on the worksheet. Drag from the top left of cell D2 to the bottom right of cell K2.

12. Right-click the scrollbar and choose Format Control.

13. Change Minimum Value to 1 and Maximum Value to 25. Set Incremental Change to 1. For Cell Link, click the Refers To button and choose cell D1. Click OK. Click outside the scrollbar to deselect it. To test the scrollbar, drag the slider. The value in cell D1 should change. The top chart should also change.

14. Change the chart title of the top chart to indicate that it is a rolling 12 months. The top chart is now complete. If you want to draw the shaded box on the bottom chart, continue with the following steps.

15. Enter the heading Shading in cell C1.

16. Enter the following formula in cell C2:

=IF(AND(ROW(A1)>=$D$1,ROW(A1)-$D$1<12),MAX(SalesByMonth),0)

Copy this formula down to the other 35 months.

17. Select the range C1:C37. Press Ctrl+C to copy the range. Select the bottom chart. Press Ctrl+V to paste a new series to the chart.

18. Click the line for the second series. From the Design ribbon, choose Change Chart Type, Area Chart.

19. Click the area chart to select the second series. From the Format ribbon, choose Shape Fill and select light gray to make the box less obtrusive.

The scrollbar makes this chart fun to use. People will be encouraged to interact with the chart, which will mean they spend more time with the chart. Perhaps it is a bit gimmicky. Perhaps it makes sense when you have 20 years of monthly data. I can certainly understand the argument that the 12-month chart is less informative than the 36-month chart. However, the scrolling chart is here in this book because it is unusual.