How to Add Scroll Bar in Excel Chart (Step by Step Guide)

Human being loves to have decorated things. The same thing goes even in Worksheet too. To fabricate the Excel Worksheet, sometimes we use Scroll Bar with the Excel Chart. In this article, I will try to explain how to add Scroll Bar in Excel Chart.
For more clarification, I am going to use a Dataset containing Batsman and Test Average Columns.

Table of Contents hide

Download Practice Workbook

Step by Step Procedure to Add Scroll Bar in Excel Chart

Create Excel Chart from the Dataset

Inserting a Scroll Bar with Chart

Customize Scroll Bar Format with Excel Chart

Fetching Data with Defined Name

Organize Data Along with Chart Axis

Practice Section

Conclusion

Download Practice Workbook

Adding Scroll Bar in Chart.xlsx

Step by Step Procedure to Add Scroll Bar in Excel Chart

Adding Scroll Bar in Excel Chart beautifies the whole Worksheet. It’s not that hard. The process is explained below:

Create Excel Chart from the Dataset

Definitely, at the very beginning, you need to add a chart of your requirement, so let’s create a chart. It’s the primary stage to add a scroll bar in an Excel chart.

Steps:

First of all, select the whole data.
Then, go to Insert and choose your Chart pattern. I have gone to Insert Column or Bar Chart and selected according to my choice.

Then, we will have our dataset in a chart.

Inserting a Scroll Bar with Chart

In this section, I’ll insert a scroll bar, which is the primary task of the article. We will add a scroll bar from the Form Controls, follow the steps to get to know in detail.

Steps:

Then, in order to add Scroll Bar, go to Developer —> Insert.
Click on Scroll Bar from the Form Controls.

Now, place the scroll bar in a suitable position.

Customize Scroll Bar Format with Excel Chart

The mere addition of the bar may not fit well for presentation, that’s why the additional scroll bar needs to be customized.

Steps:

Right-click on the mouse.
Select Format Control from the available options.

A Format Control box will appear.

Make the following changes in the Format Control box:

Current Value = 1

Minimum Value = 0

Maximum Value = 10 which expresses that the chart will show a maximum of 10 results at a time.

Incremental Change = 1 which explains the change in the chart per scroll in the scroll bar.

Page Change = 10

Cell Link = $C$5 where the change in the scroll bar will be shown with its results.

Now, press OK.

Fetching Data with Defined Name

In order to simplify the data, let’s use Define Name. This help to present the value in a more dynamic manner.

Steps:

Select a cell as a Cell Link. Here, I have selected cell C16.

Go to Formulas.
From there, select Define Name.

Now, choose a name for a selected set of data. I have chosen the name Name.
Use the following formula in the Refers to field to define the value in cells B5: B14:

=OFFSET(Sheet1!$B$5,,,Sheet1!$C$16)

Here, OFFSET Function will consider the value from the B5 cell till the end of the table and ignore other cells.

Press OK.

Use the following formula in the Refers to field with the name Average to define the cells from C5 to C14:

=OFFSET(Sheet1!$B$5,,,Sheet1!$C$16)

In this case, OFFSET Function will evaluate the values from C5 cell till the end of the table and ignore the rest.

Now, Press OK.

Organize Data Along with Chart Axis

In this step, we need to determine the axis along with its value to organize the whole dataset. Follow the steps to organize.

Steps:

Right-click on the mouse putting the cursor on the chart.
Pick the Select Data option.

Select Data Source box will appear.

Click on the option under Legend Entries (Series) and go to Edit.

Select the column number in the Series name. In my case, it was cell C4 in Sheet1.
Input the values in the Series values Here, I used the values in the defined name Average that belongs to Sheet1.
Next, press OK.

Now, click on Edit under the Horizontal (Category) Axis Labels.

Input the name in the Axis label range. I used the values in the defined name Name that belongs to Sheet1.
Finally, hit OK to finish the process.