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

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:

=(ROW()-2+0.5)/5
Note: In the above formula, the number 2 indicates the row number of your formula located, and then number 5 indicates that how many rows of your data (exclude the header row).

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:


Download Slider Bead Chart sample file


The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.