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.
Download Practice Workbook
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.
- 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.
- 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.
- 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.
- 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:
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:
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.
- 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.
Now, you can see the additional Scroll Bar in Excel Chart that works perfectly.
You can practice here for more expertise.
I have tried to explain the procedure on how to add Scroll Bar in Excel. I hope it will help the Excel Users. For further queries, comment below.
- How to Remove Scroll Bar in Excel (3 Easy Ways)
- [Fixed!] Vertical Scroll Bar Not Working in Excel (10 Possible Solutions)
- How to Create a Vertical Scroll Bar in Excel (Step by Step)
- [Fixed!] Bottom Scroll Bar Missing in Excel (7 Possible Solutions)
- [Fixed!] Excel Horizontal Scroll Bar Not Working (8 Possible Solutions)