In many dashboard you need to create a large Chart, due to small Chart is not good for proper visualization. If you add a Horizontal Scroll Bar from Developers tab to the Chart, then it would be an attractive Chart for viewing and you might be in spotlight for your good work in workplace. So, this article will help you how you can add the Horizontal Scroll Bar to your big Chart.
Make a Bar Chart:
Before you assign a Horizontal Scroll Bar into your big Chart, you first need to create a Chart. Assume that, based on the below data you have created the simple Bar Chart as shown in below:
Image 1: Sample data
Now create a Bar Chart (because Bar Chart and Horizontal Bar matches perfectly, that is why I'm using Bar Chart. But if you wish, you can change the chart type as Pie, Line or Column too) like below:
Image 2: Bar Chart created
After creating the above Bar Chart, you will definitely say, "this chart it is not good to view and recognize the area sales achievement". This thing can be asked by your Management to you too. But you can make this chart beautiful and view able. To do this you need to follow below steps:
Steps for adding a Horizontal Scroll Bar to Chart:
Step 1:
First of all Insert a Horizontal Scroll Bar in your worksheet. To do this Click on Developer ➪ Insert ➪ Scroll bar (Form Control).
Image 3: Inserting Scroll Bar (Form Control)
Step 2:
Drag in your worksheet to draw the Scroll Bar.
Image 4: Horizontal Scroll Bar inserted
Step 3:
Now you need to store the Scroll Bar scrolling value into a cell. You can select any blank cell for this. In this project I have decided to store my Scroll Bar scrolling value into $H$1 Cell. To set this Right Click on the Scroll Bar and Click on Format Control. This will appear a Format Control dialog box. Click on Control tab and set the dialog box settings as below image and Click on Ok:
Image 5: Format Control dialog box settings
In the Control tab, Current value has set to 0, that means Cell link: $H$1 has set and H1 value currently is 0. Minimum value 1 means minimum 1 bar chart will display in the chart window. Maximum value 60 means maximum 60 bar chart will display in the chart window. Maximum value depends on your data range. If data range is 101 then set it to 101 or 110. Incremental change set to 1 means each time while you clicked on Horizontal Bar, then it will add 1 more Bar Chart.
Now each time you Click the Left and Right Arrow of Horizontal Scroll Bar, then the value of Cell Link: will also be changed.
Image 6: Value changing while clicking on Scroll Bar Arrow
Step 4:
Now the important part is connecting the Scroll Bar with Chart Source Data. So that, each time we Click on Arrow key, the Source Data range of Chart can also be increased or decreased. And if the source data changed, then the output of Chart should also change.
To make this connection, first Click on Formula ➪ Define Name. Create a Name Location and use the formula in Refers to:
=OFFSET(Chart!$A$2,,,Chart!$H$1)
After setting it the dialog box will looks like below:
Image 7: Location Name created
In the same way, Again Click on Formula ➪ Define Name. Create a Name Achievement and use the formula in Refers to:
=OFFSET(Chart!$C$2,,,Chart!$H$1)
After setting it the dialog box will looks like below:
Image 8: Achievement Name created
Step 5:
Now we need to connect the Location Name (formula) and Achievement Name (formula) to Chart Source Data. To do it, Right Click on Chart and Click on Select Data. A Select Data Source dialog box will appear like below image:
Image 9: Select Data Source dialog box
All the settings in Legend Entries (Series) here is correct. But we need to change just the Achievement value Range. Because in general Chart, by default it takes $C$2:$C$52 and that is why all the Bar shows at once in a single chart.
But the trick is in Achievement Name, which is a range. Achievement Name range will increase or decrease based on the Horizontal Scroll bar value. If the Horizontal Scroll Bar value is = 1, then in Achievement column Achievement Name range will set to $C$2:$C$2. That means 1 bar shows in the chart. If again horizontal bar value is = 5, then in Achievement column chart data range changed to $C$2:$C$6 that means 5 bar will display in the Chart.
But the trick is in Achievement Name, which is a range. Achievement Name range will increase or decrease based on the Horizontal Scroll bar value. If the Horizontal Scroll Bar value is = 1, then in Achievement column Achievement Name range will set to $C$2:$C$2. That means 1 bar shows in the chart. If again horizontal bar value is = 5, then in Achievement column chart data range changed to $C$2:$C$6 that means 5 bar will display in the Chart.
So, to apply the Achievement Name range which contains an OFFSET function, Click on the Achievement from Legend Entries (Series) and then Click on Edit. This will appear a Edit Series dialog box:
Image 10: Edit Series Dialog box
Change the Series values to: =Chart!Achievement. The dialog box setting will looks like this:
Image 11: Achievement Name added
Now Click on the Horizontal (Category) Axis Labels and change the Axis Label Range to:
=Chart!Location
Image 12: Axis Labels changed
Step 6:
Now it will back to the Select Data Source dialog box. Click on Ok and You are done. It will looks like this:
Image 13: New Chart with Horizontal Scroll Bar
Now see the attractive changing:
Image 14: Final View changing the Bar Chart with the Horizontal Scroll Bar
You can try to change the Chart Type to Pie, Column, Line etc. for test.
|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw