Skip to main content

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:


Create a slider bead chart in Excel with an awesome feature

Kutools for Excel provides 50+ special types of charts that Excel does not have, such as Bullet Chart, Target and Actual Chart, Difference Arrow Chart and so on. With its handy tool- Project Status Bead Chart, you can create a slider bead chart based on percentage or actual and target values quickly as you need. Click to download Kutools for Excel for free trial!

doc kte progress circle chart 1


Download Slider Bead Chart sample file


The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


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, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations