Create a slider bead chart in Excel

In Excel, a slider bead chart is a type of progress bar chart which is composed of bars and circle markers as below screenshot shown. Compared with the normal progress bar chart, the unique thing about this chart is that it uses circle markers to represent the project status. This type of chart is more intuitive and beautiful, creating this chart in your daily work will make you stand out from others. This article, I will talk about how to create this slider bead chart in details.

• Create a slider bead chart in Excel
• Create a slider bead chart in Excel with an awesome feature
• Download Slider Bead Chart sample file

Create a slider bead chart in Excel

To create a slider bead chart, please do with the follows step by step:

1. Create the first helper column, enter the number 1 into cell C2:C6, see screenshot:

2. Insert the below formula into cell D2, and then drag the fill handle down to the cells that you want to apply this formula, see screenshot:

3. After creating the helper data, now, please select the data in column A and column C, and then click Insert > Insert Column or Bar Chart > Clustered Bar, see screenshot:

4. And a bar chart has been inserted, you just need to delete the unneeded elements, such as chart title, gridlines to get the following results:

5. Then right click the horizontal axis, and choose Format Axis option, see screenshot:

6. In the opened Format Axis pane, under the Axis Options tab, change the number to 1.0 from the Maximum text box, see screenshot:

7. Then, right click the bar of the chart, and then click Fill, and choose one bright green color from the theme colors section, see screenshot:

8. And then, click to select the chart, right click the chart area, and choose Select Data from the context menu, see screenshot:

9. In the popped out Select Data Source dialog box, click Add button, see screenshot:

10. In the popped out Edit Series dialog box, select cell B1 as the Series name, and select B2:B6 as the Series values, see screenshot:

11. Then, click OK > OK to close the dialogs, and you will get the chart as below screenshot shown:

12. Next, please right click the new added data series (orange bars), and choose Format Data Series, see screenshot:

13. In the opened Format Data Series pane, under the Series Options tab, adjust the Series Overlap to 100%, and change the Gap Width to 120% as you need. See screenshot:

14. Still in the Format Data Series pane, under the Fill & Line tab, from the Fill section, select Solid fill option, and then choose one darker green color from the Color drop down, see screenshot:

15. Then, go on clicking to select the chart, and right click the chart area, choose Select Data from the context menu to go to the Select Data Source dialog box, click Add button, see screenshot:

16. In the popped out Edit Series dialog box, select D1 as the Series name, and select D2:D6 as the Series values, see screenshot:

17. Then, click OK button to return the Select Data Source dialog box, in this dialog, please click Edit button from the Horizontal (Category) Axis Labels section, see screenshot:

18. In the following Axis Labels prompt box, select B2:B6 as the Axis label range, see screenshot:

19. Then, click OK > OK to close the dialogs, and the chart has been displayed as below screenshot shown:

20. This step, please right click the new added data series (grey bar), and choose Change Series Chart Type from the context menu, see screenshot:

21. In the Change Chart Type dialog box, in the Choose the chart type and axis for your data series list box, change the chart type of the Helper 2 data series to Scatter, see screenshot:

22. And then, click OK button, and the chart has been changed to the below chart, now, you should delete the secondary axis as you need, see screenshot:

23. In the chart, you can see the vertical axis label has been changed, to return the original axis label, please right click the chart area, and choose Select Data to go to the Select Data Source dialog box, in the opened dialog, click the Completion % from the Legend Entries (Series) list box, and then click Edit button from the Horizontal (Category) Axis Labels section, see screenshot:

24. Then, in the popped out Axis Labels prompt box, select the cell A2:A6 as the Axis label range, see screenshot:

25. Click OK > OK to close the dialogs, and you will get the below chart:

26. Now, you should format the grey dots as the bead you need, right click the grey dot, and then choose Format Data Series, see screenshot:

27. In the Format Data Series pane, under the Fill & Line tab, click Marker option, and then do the following operations:

• In the Marker Options section, select Built-in, choose one marker type you need, then specify the size for the marker;
• In the Fill section, select Solid fill and specify one color for the marker;
• In the Border section, select Solid fill, and specify one color and width for the border you like, then, select double line from the Compound type.

28. Now, the beads have been formatted as below screenshot shown:

29. In this step, please add the data labels for the chart, click to select the beads, and then click Chart Elements icon to expand the Chart Elements box, click More Options from the Data Labels drop down, see screenshot:

30. In the expanded Format Data Labels pane, under the Label Options tab, check X Value and uncheck Y Value from the Label Options section, and the data labels have been inserted into the chart, see screenshot:

31. Then, you should change the values in horizontal axis to percentage values, right click the horizontal axis, and choose Format Axis, in the Format Axis pane, under the Axis Options tab, click to expand the Number section, select Percentage from the Category drop down list, and then specify the Decimal places to 0, see screenshot:

32. And now, the slider bead chart has been created successfully as below screenshot shown:

Create a slider bead chart in Excel with an awesome feature

Download Slider Bead Chart sample file