Sometimes for data visualization, you may need to make a chart. Furthermore, a chart slider is a more interesting and useful thing for data visualization. You can visualize the situation easily with the chart slider in Excel. So, in this article, I will explain how to make a chart slider in Excel.
Furthermore, for conducting the session, I will use Microsoft 365 version.
Create Chart Slider in Excel: 2 Suitable Ways
Here, I will describe 2 methods to make a chart slider in Excel. In addition, for your better understanding, I’m going to use a sample dataset. Which contains 3 columns. They are Product, Sales, and Profit. The dataset is given below.
1. Insert Scroll Bar to Make Chart Slider
Here, I will use the Developer tab to create the chart slider in Excel. Basically, I’m going to use the Scroll Bar under this Developer tab. Actually, with the help of Scroll Bar, you can create a changeable or movable dataset. If you don’t see the developer tab, you can customize the ribbon to display the Developer tab. Now, follow the steps given below.
Steps:
- Firstly, you have to select the data. Here, I have selected the range B4:D9.
- Secondly, you have to go to the Insert tab.
- Thirdly, from the Charts group section, you may select Insert Line or Area Chart.
- Lastly, I have chosen Line with Markers. Here, the selection will be according to your preference.
- As a result, you will see the following chart.
- Then, you have to go to the Developer ribbon.
- After that, from the Insert tab >> you must choose the Scroll Bar under Form Controls.
- At this time, you have to drag the mouse pointer.
- So, you will see the scroll bar.
At this time, I will link up the Sales and Profit values with a blank cell like F5.
- Below, I have shown you a way to link up C5 cell value with F5 cell. Here, you should use your own way for this linking calculation according to your data.
- Similarly, I have shown you another way to link up D5 cell value with F5 cell. Here, you should use your own way for this linking calculation according to your data.
- Then, you have to right-click on the scroll bar.
- Consequently, from the Context Menu, you need to choose Format Control.
As a result, a dialog box named Format Control will appear.
- Now, from the dialog box you need to go to the Control menu.
- Then, select F5 cell in the Cell link box.
- Then, you should write the Minimum value, Maximum value, Increment change.
- After that, check 3-D shading.
- Lastly, press OK.
- Finally, you will see that if you click on the scroll bar then the value of F5 cell will be auto-updated along with the values of Sales and Profit.
- Here, I have changed the scroll bar. So, the value of F5 cell >> then the values of Sales and Profit >> lastly the chart is auto-updated.
- Additionally, you can drag the chart to hide the linked cell.
- Furthermore, for your better understanding, I have added a gif of the chart slider.
Read More: How to Use Form Controls in Excel
2. Use Spin Button to Create Chart Slider
Again, I will use the Developer tab to create the chart slider in Excel. Here, I’m going to use the spin button under this Developer tab. Actually, this method is similar to the previous method. Now, follow the steps given below.
Steps:
- Firstly, you have to create the chart.
Now, I will link up the Sales and Profit values with a blank cell like F5.
- Below, I have shown you another way to link up D5 cell value with F5 cell using cell reference. Here, you should use your own way for this linking calculation according to your data.
- Similarly, do the same for the other cells of the D column.
- Again, use a formula to link up with the C column to the F5 cell.
- According to my formula, the Sales and Profit values become zero because the F5 cell contains 0 as the cell value.
- Then, from the Developer ribbon >> go to the Insert tab >> then you may choose the Spin Button under Form Controls.
- After that, you will see a plus sign (+) and you have to drag the mouse pointer to your preferable place.
- As a result, you will see the spin button.
- Now, you have to right-Click on the spin button.
- Then, from Context Menu, you need to choose Format Control.
So, a dialog box named Format Control will appear.
- Now, from the dialog box you have to make sure that you are on the Control menu.
- After that, you should write the Minimum value, Maximum value, Increment change.
- Then, select F5 cell in the Cell link box.
- Subsequently, check 3-D shading.
- Later, press OK.
- Finally, you will see that if you click on the spin button then the value of F5 cell will be auto-updated along with the values of Sales and Profit also with the chart.
- Lastly, you can drag the chart to hide the linked cell, and then you can check the output.
Read More: Key Differences in Excel: Form Control Vs. ActiveX Control
How to Make Slider Bead Chart in Excel
In this section, I will show you how to make a slider pellet or bead chart in Excel. For your better understanding, I’m going to use the following dataset. Where you will see the profit percentage of some products.
- Firstly, I have introduced two new columns named Supporter 1, and Supporter 2.
- Secondly, use cells value 1 in the Supporter 1 column.
- After that, select the Product and Supporter 1 columns.
- Now, from the Insert tab >> you have to select the Insert Column or Bar Chart which is under the Charts group section.
- Then, from the 2-D Bar >> I have chosen the Clustered Bar. Here, the selection will be according to your preference.
- As a result, you will see the following chart.
- Then, press double-click on the Horizontal (Category) Axis.
So, a new window named Format Axis will appear at the right most side of the Excel sheet.
- Consequently, from the Axis Options >> go to Axis Options and set the maximum value as 1.
- Now, right-click on the chart.
- Then, from the Context Menu Bar >> you need to choose Select Data.
After that, you will see the following dialog box of Select Data Source.
- Now, from the dialog box of Select Data Source, you have to choose the Add feature.
After selecting the Add feature, another dialog box of Edit Series will appear.
- Now, you can write down or select the Series name in that dialog box. Here, I have selected the Series name as Profit Percentage from the D4 cell.
- Then, you have to include the Series values. Here, I have used the range D5:D9.
- Finally, you need to press OK to get the corresponding bar chart.
Subsequently, the previous dialog box of Select Data Source will appear.
- Then, click on OK.
- Furthermore, press double-click on the Profit Percentage chart.
So, another window named Format Data Series will appear at the right most side of the Excel sheet.
- Subsequently, from the Series Options >> go to Series Options and then make the Series Overlap to 100%.
- As a result, you will see the following output.
- Now, in the F5 cell write down the following formula.
=(ROW()-5+0.5)/5In this formula, I have used the ROW function. Additionally, 1st 5 is the row number where you have inserted this formula. Then, the divisor 5 is the total row number without the header of your dataset.
- Now, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.
- Then, add the Supporter 2 column in the chart by following the previous method.
- After that, you should click on the Edit option to change the Axis Labels.
At this time, a dialog box named Axis Labels will appear.
- Then, you have to select the Axis label range. Here, I have selected the range from D5:D9.
- Now, press OK to make the changes.
- After this, press OK on the Select Data Source box.
- Consequently, you must right-click on the Supporter 2Â chart.
- Then, from the Context Menu Bar >> you need to choose Change Series Chart Type. Here, if you don’t open the Context Menu Bar without using the Supporter 2 chart then you won’t be able to change the chart type of Supporter 2.
As a result, the Change Chart Type dialog box appears.
- Then, from the All Charts menu >> go to Combo chart >> then select Custom Combination.
- After that, choose Scatter for Supporter 2 and mark on Secondary Axis.
- Lastly, click OK.
- So, you will see the following situation.
- Now, delete the secondary axis.
- Here, change the Horizontal (Category) Axis Labels to product name again.
- Then, click on the point of the Supporter 2 chart.
So, the window named Format Data Series will appear at the right most side of the Excel sheet.
- After that, from the Series Options >> choose Fill & Line and then go to the Marker Options >> check Built-in and increase the size.
- Now, select Profit Percentage and from the Chart Elements >> check Data Labels.
- Subsequently, click on the Horizontal (Category) Axis.
As a result, the window named Format Axis will appear at the right most side of the Excel sheet.
- After that, from the Axis Options >> choose Axis Options and then go to the Number >> choose Category as Percentage and decrease the Decimal places to 0.
- Lastly, you will get the slider pellet chart.
Practice Section
Now, you can practice the explained method by yourself.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
I hope you found this article helpful. Here, I have explained 2 suitable methods for how to create a chart slider in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.
