Creating a Scrolling chart

Excel 2016 2010 2007
Sometimes you need to create a chart that shows a large data amount. Then it would be useful to insert a scroll bar in your chart:
Scrolling chart in Excel 365

To create a chart like one above (sales data for the week for three months), do the following:

Step I: Create a chart

   1.   Select the data (in this example, B2:C94).

Note: You can select any of the data cell and click Ctrl+Shift+Space to select ass data range (see How to select row and column by keyboard and How to select a large data range in one click in Excel for more details).

   2.   On the Insert ribbon, in the Charts group, click the Insert Line or Area Chart button:

Line charts in Excel 365

Choose Line:

Line chart in Excel 365

Step II: Create additional data

   1.   Enter 1 in any cell (for example, E2) - this number we will use for scrolling (the scrolling cell).

   2.   On the Formulas tab, in the Defined Names group, click the Name Manager button:

Defined Names group in Excel 365

   3.   In the Name Manager dialog box, click the New... button:

Name Manager dialog box in Excel 365

   4.   In the New Name dialog box:

New Name in Excel 365
  • Type the name (for example, Dates) in the Name field.
  • In the Refers To field, enter:

    = OFFSET (Scroll!$B$2, Scroll!$E$2, 0, 7, 1).

    This formula says to start from cell B2 of the Scroll sheet, move down the number of rows in cell E2, and then take a range that is 7 rows (one week) by the first column.

  • Click OK to accept the name.

   5.   Add another name (for example, Sales) with the followint formula in the Refers To box:

= OFFSET (Scroll!$B$2, Scroll!$E$2, 1, 7, 1).

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

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

= SERIES (Scroll!$B$2, Scroll!Dates, Scroll!Sales, 1):

New formula for Chart Series in Excel 365

Test the chart. Enter a new number between 1 and the date (in this example, 92) in the cell E2. The chart should change.

Step III: Add a scroll bar

On the Developer tab (if you don't have the Developer tab available in the ribbon, see Show the Developer tab), in the Controls group, click the Insert button and then choose the Scroll Bar icon:

Controls group in Excel 365

Note: You can also add the Scroll Bar control to the Quick Access Toolbar (see Adding commands to the Quick Access Toolbar):

Customize Quick Access Toolbar in Excel 365

Step IV: Customize the scroll bar

   1.   Right-click the scroll bar and choose Format Control... in the popup menu:

control popup in Excel 365

   2.   In the Format Control dialog box, on the Control tab:

Format Control in Excel 365
  • Change Minimum value to 1
  • Change Maximum value to your maximum value
  • Set Incremental Change to 1 (or if you want to 7, 10)
  • In the Cell link field, choose a cell with the scroll point (in this example, E2).

   3.   Click OK. Click outside the scrollbar to deselect it.

   4.   To test the scrollbar, drag the slider. The value in cell E1 should change; the top chart should also change.

See also Creating Master and Scrolled Detail Charts.

See also this tip in French: Comment créer un graphique de défilement.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.