Human beings love 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 a Scroll Bar in Excel Chart.
For more clarification, I am going to use a Dataset containing Batsman and Test Average Columns.
How to Add Scroll Bar in Excel Chart with Easy Steps
Adding a scroll bar in the Excel chart beautifies the whole Worksheet. It’s not that hard. The process is explained below:
1st Step: Create an Excel Chart from the Dataset
Definitely, at the very beginning, you need to add a chart of your requirements, 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.
2nd Step: Inserting a Scroll Bar with a 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.
3rd Step: 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.
4th Step: Fetching Data with Defined Name
In order to simplify the data, let’s use Define Name. This helps 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:
=OFFSET(Sheet1!$B$5,,,Sheet1!$C$16)Here, the 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.
5th Step: 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.
Practice Section
You can practice here for more expertise.
Download Practice Workbook
Conclusion
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.
