Key Take-Aways

- Enable the Developer tab and insert two form-control scroll bars: one for scroll index and one for zoom index.

- Link each scroll bar to a worksheet cell and create named ranges for those cell links.

- Use OFFSET to create dynamic named arrays for X values (categories) and Y values (series) that depend on the scroll and zoom indices.

- Replace the chart series and axis ranges with the named arrays so the chart updates as you scroll and zoom.

- OFFSET is volatile. For very large data sets consider INDEX or FILTER alternatives in newer Excel versions.

## Why build a dynamic scrollable chart

Most Excel charts are static ranges. When you want to inspect trends at different granularities or present a moving window of time series data, a dynamic chart that can zoom and scroll is far more effective. The technique below uses simple Excel features: form control scroll bars, named ranges, and the OFFSET function. You get an interactive view without VBA.

## Overview of the approach

1. Add two scroll bars: one controls the horizontal offset (which slice of data you see) and one controls the zoom level (how many points show).

2. Link each scroll bar to a worksheet cell and create named ranges that point to those cells (scroll index and zoom index).

3. Create two dynamic named arrays using OFFSET: X values (category axis) and Y values (data series). The row offset uses the scroll index and the height uses the zoom index.

4. Edit the chart and replace static ranges for series values and axis labels with the named arrays.

## Step-by-step: build the scrollable chart

### 1. Enable the Developer tab and insert scroll bars

- If you do not see Developer: File > Options > Customize Ribbon and check Developer. Press OK.

- On the Developer tab click Insert and choose the Form Controls scroll bar.

- Draw two scroll bars on the sheet. One will be the scroll index and the other the zoom index. Adjust size and alignment visually.

### 2. Configure scroll bar properties

- Right click the first scroll bar and choose Format Control.

- Set the Minimum value to 0. Set Maximum to a sensible number based on how many starting positions you want to allow. Link the Cell link to an empty cell (for example D16). That cell will now show the scroll index.

- For the second scroll bar (zoom), set Minimum to 1 and Maximum to the maximum window size you want (for example 12 for months). Link its Cell link to another cell (for example D17). That cell will show the zoom index.

### 3. Create named ranges for the index cells

- Open Formulas > Name Manager and create two names:

- scrollIndex referring to the cell linked to the scroll bar (example: 'Scroll Chart'!$D$16)

- zoomIndex referring to the cell linked to the zoom bar (example: 'Scroll Chart'!$D$17)

Be careful with name capitalization and spacing because you will reference these names exactly later.

### 4. Build dynamic named arrays with OFFSET

- Create a named range called Xvalues and set Refers to to a formula that uses OFFSET. Suppose your months live in column B starting at B3. The formula is:

=OFFSET('Scroll Chart'!$B$3, scrollIndex, 0, zoomIndex, 1)

Explanation of OFFSET parameters: reference = starting cell, rows = scrollIndex, cols = 0, height = zoomIndex, width = 1.

- Create a named range called Yvalues and point it to the series column (for example impressions in column C starting at C3):

=OFFSET('Scroll Chart'!$C$3, scrollIndex, 0, zoomIndex, 1)

- If you want a second series (clicks), add Yvalues2 with the same pattern but starting at D3.

### 5. Replace chart ranges with named arrays

- Right click the chart > Select Data > Add (or edit the existing series).

- For Series values, remove the sheet reference after the exclamation point and replace it with the named array. For example: Scroll Chart!Yvalues

- For Horizontal (Category) Axis Labels, edit and replace the range with Scroll Chart!Xvalues

- Excel will evaluate the named arrays and the chart will populate with the current slice of data.

### 6. Test the interactivity

- Drag the zoom scroll bar to change the window height and see how many months or points are displayed.

- Drag the scroll scroll bar to move the window left or right. The chart will show a different slice of the series.

## Additional Tips, Pitfalls to Avoid & Pro Advice

- OFFSET is volatile. Every workbook calculation will recalculate OFFSET. If you have thousands of volatile formulas or very large data sets this can slow Excel. In Excel 365 you can often replace OFFSET with INDEX-based dynamic ranges or use FILTER for cleaner, nonvolatile behavior.

- Named ranges are case-insensitive in Excel, but keep consistent naming and capitalization to avoid confusion when editing chart references.

- When editing chart series, be sure to remove the default range after the exclamation point. If the name is misspelled the chart will show an error.

- Keep buffer rows above your data (for example start at row 3) so the OFFSET reference does not intersect header rows.

- If you want smoother stepping, adjust the scroll bar maximum and page change values. For example if you want pixel-smooth movement multiply indices by a step size using your formula logic.

- For presentation, hide the index cells and label the scroll bars clearly so end users know which is zoom and which is scroll.

## FAQ

Q: How do I make the scroll bars?

A: On the Developer tab choose Insert > Form Controls > Scroll Bar. Draw the control, then right click and choose Format Control to set min, max, and the linked cell.

Q: Why do I need named ranges? Can I reference the OFFSET formula directly in the chart?

A: Excel charts accept named ranges more reliably and the name makes the chart formula readable. You can paste the OFFSET directly in some chart editors but named ranges are easier to manage and reuse.

Q: The chart is slow. Is OFFSET the problem?

A: Possibly. OFFSET is volatile which forces recalculation. For large workbooks consider INDEX-based formulas or FILTER in Excel 365 to reduce recalculation overhead.

Q: How do I add a second series like clicks?

A: Create another named OFFSET array for the second series (for example Yvalues2) using the same scrollIndex and zoomIndex for rows and height. Then add it as a second series in Select Data.

Q: The chart shows #REF or an error after replacing the range. What did I do wrong?

A: Likely the named range is misspelled or you deleted the sheet name or punctuation. Make sure the reference looks like SheetName!Yvalues and that the named range exists.

Q: Can I use this with daily data instead of months?

A: Yes. The technique works with any ordered category axis. Just point the Xvalues offset at your date column and adjust zoom max to the desired maximum window size.

## Summary of Key Points / Take-Home Messages

- Use two scroll bars: one for position and one for window size.

- Link scroll bars to cells and create named ranges for those links.

- Use OFFSET to build dynamic X and Y named arrays that respect scroll and zoom indices.

- Replace chart series and axis ranges with the named arrays to make the chart interactive.

- Consider nonvolatile alternatives for very large or performance-sensitive workbooks.