Skip to main content

New Article

How to SUM by matching partial text in Excel

How to use Horizontal Scrollbar for large chart viewing in Excel

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.


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

Popular posts from this blog

How to display an image in worksheet based on a List or based on IF condition?

Excel can show image on worksheet based on a specific IF condition. So, how to do it? Simple follow the below steps: Step by Step: Step 1: Insert images in your Excel Worksheet. Here I've inserted 5 different types of balls, Football, Cricket, Pool, Basketball, Tennis ball. Note that, All balls are placed into different cell. These are placed in Picture sheet. Image 1: 5 balls placed in 5 different cells and covered photo's wide and height Step 2: In the report sheet, design the report as you wish. I've designed in my way like below: Image 2: Kids asking to Donald Duck, which ball need to throw now Step 3: Make a drop down list "Games" in E6 cell in Report sheet from Data Validation. which is as below: Football Cricket Pool Basketball Tennis You can do an IF function here in E6 in Report sheet, which will meet a certain condition and returned Football, Cricket, Pool, Basketball or Tennis. Step 4: Now the tricky part is...

Cumulative Closing Balance like a Bank Statement in PivotTable

A Bank Employee in many case needs to calculate the Closing Balance after each transaction in PivotTable like a Bank Statement . But with the help of Calculated Field of a PivotTable , you can only calculate Field with Field . It's not possible to use a formula in Calculated Field where you can mention a single cell with Relative Reference Cell . Because a PivotTable acts like a Table format where you can work with Field or Column Name . A Helping Column (A regular column, that helps to get a partial result where formula applied) can be a good idea. But there is an option in PivotTable by which you can Calculate Cumulative . In this article I will show you, how you can do it. Calculating with Formula: Assume that, you have an Excel file with Debit and Credit transaction of an Account . Image 1: Sample Bank Statement In a Bank Statement , Month wise Cumulative Balance is important. In the Excel sheet, as above, it is very easy to use a formula and calc...

Value Paste and Formula Paste

In many case, in our professional life, we need to do Copy and Value Paste or Copy and Formula Paste in all most always. Those employees, who works under MIS Department, have to do it lots of time in a single day to prepare reports. It is very time costing task in Office. Many of us use Excel Menubar to do it, others are using Keyboard Shortcut by pressing ALT key (like for Value Paste Press ALT+H+V+V and for Formula Paste Press ALT+H+V+F ). But did you noticed that, these two ways strongly need your attention to do this task. More clearly, if you choose Value Paste from Menubar , then what you need to do? First you should take the mouse in Hand Wheel the mouse in Home menu Then Click on Paste Then you pressure your eyes to find the Value Paste icon And then Click on it to paste it These all steps can take more than 1 second. Am I right? Below is an image of this process: Image 1: Value Paste from Home Menu On the other hand, if you choose the shortcut f...