How to Add Scroll Bar to a Chart in Excel (With Easy Steps)

In this Excel Tutorial, you will learn how to add a scroll bar to a chart. It allows users to navigate through large datasets or time-series data, making it easier to focus on specific sections or time intervals.

Scroll bars are particularly useful when dealing with interactive dashboards, parameter adjustments, or limited space scenarios. By linking the scroll bar to relevant parameters, users can dynamically update the chart, providing a more engaging and customizable experience.

Here is an overview of how the scroll bar in a chart can present the dataset graphically by sections. The dataset contains the average runs of some famous batters in their test cricket career. Creating a chart using their average runs would take a larger space. By utilizing a scroll bar, all the data can be presented within a reasonable area.

6 Steps to Add Scroll Bar to a Chart in Excel

Step 1: Create an Excel Chart from the Dataset

To create a chart using the dataset, follow the steps below:

1. Select the whole data.
2. Go to the Insert tab and choose your chart pattern.
   

The chart will be created according to the dataset.

Step 2: Insert a Scroll Bar

To insert a scroll bar for the chart, follow the steps below:

1. Select the Developer tab and click the Insert drop-down.
2. Choose Scroll Bar from the Form Controls
3. Now, drag the cursor to create the scroll bar under the chart.
   

Step 3: Customize Scroll Bar Format

The scroll bar needs customizations for the flexibility of its use. Whenever you click the scroll bar, it generates a value in a linked cell. And thus it can control the number of bars or the area of the chart.

In our chart, there are 11 bars. We want to show 3 of them at a time. We will format the scroll bar according to this condition.

To format the scroll bar, follow the steps below:

1. Right-click on the mouse.
2. Select Format Control from the available options in the context menu.
   
3. In the Format Control dialog box, set up the parameters properly:
   • Minimum Value: 0
   • Maximum Value: 8 (as we have 11 bars and we want to show 3 bars at a time, making the scroll bar value 11 will show the bars 9-11)
   • Incremental Change: 1
   • Page Change: 1 (if you click in the scroll bar area, the scroll box will move 1 step at a time)
   • Cell Link: $C$17
   • Now, press OK.

   

The linked cell will contain the values each time the scroll bar is triggered.

Step 4: Create Named Ranges for the Data Using Formulas and Linked Cell Value

In this section, named ranges will be created for the corresponding dataset columns. We shall combine the linked cell in the formula.

To create named ranges using the formulas, follow the steps below:

1. Define how many bars will be visible in the chart.
   
2. Now, select Formulas > Define Name.
   The Edit Name dialog box will appear.
3. Give a name to the range.
4. Use the following formula in the ‘Refers to‘ field to define the value in cells B5: B14:
   =OFFSET('Data Table'!$B$4,'Data Table'!$C$17+1,0,'Data Table'!$C$18,1)
Here, the formula uses the OFFSET function which defines a partial dynamic range from the data range B5:B14. The linked cell (‘Data Table’!$C$17) provides the number of rows of the partial range. The height of the range is set by the cell reference ‘Data Table’!$C$18.
5. Click OK.
   
6. Similarly, apply the following formula in the ‘Refers to’ field with the name Average to define the cells from C5 to C14:
   =OFFSET('Data Table'!$C$4,'Data Table'!$C$17+1,0,'Data Table'!$C$18,1)
In this case, the OFFSET function will evaluate the values from C5 cell till the end of the table and ignore the rest.

Step 5: Modify Data Range in Chart Using Named Ranges

In this step, we shall determine the axis along with its value to organize the whole dataset. And finally, we are going to be able to control chart appearance using the scroll bar.

To organize data and control the chart with the scroll bar, follow the steps below:

1. Right-click on the chart and select the Select Data option.
2. In the Select Data Source dialog box, select the column heading under Legend Entries (Series) and go to Edit.
   
3. In the Edit Series dialog box:
   • Insert the reference of the column heading in the Series name.
   • Use the named range for numeric data (Average) in the Series values.
4. Click OK.
   
5. Now, click on Edit under the Horizontal (Category) Axis Labels.
   
6. Use the named range for names (Name) in the Axis label range box and click OK.
   
7. Finally, click OK on the Series Data Source dialog box to complete the setup.
   

Step 6: Use the Scroll Bar in Chart

Now, all the setup is complete. Using this Scroll Bar, you will see 3 vertical bars out of 11 in the chart. Here’s a GIF to show you how this works.

You can see three vertical bars each time you scroll horizontally.

Download Practice Workbook

Conclusion

To sum up, you will learn a step-by-step procedure to add and use a scroll bar in an Excel chart. Adding a scroll bar to the chart helps you to compare data partially. You can also add maximum and minimum values in the chart for more comparison. If you have any questions or feedback regarding this article, please share them in the comment box.

Frequently Asked Questions

Can I use multiple scroll bars in one chart?

Yes, you can use multiple scroll bars to control different aspects of your chart, such as axis ranges or data series. Each scroll bar should be linked to a different cell.

How do I set up a dynamic range for my chart using a scroll bar?

Define a range of data and use formulas that incorporate the value from the linked cell (controlled by the scroll bar) to determine the dynamic range for your chart. This can be achieved using OFFSET, INDEX, or other relevant functions.

Related Articles

• How to Insert Scroll Bar in Excel 
• How to Adjust Scroll Bar in Excel 
• How to Remove Scroll Bar in Excel 
• How to Create a Vertical Scroll Bar in Excel

<< Go Back to Scrollbar in Excel | Excel Parts | Learn Excel