Create a Lollipop Chart in Excel
Although the lollipop chart plays the same role as column or bar chart, it is more beautiful and unique in the appearance, which can be more attractive for readers. In this tutorial, we are providing a step-by-step guide to show you how to create a lollipop chart in Excel.
The screenshot below is a comparison between the lollipop chart and the column chart.
Create a lollipop chart with same oval color in Excel
Create a vertical lollipop chart with different oval colors in Excel
Create a horizontal lollipop chart with same or different oval colors in Excel
Easily create a vertical or horizontal lollipop chart with an amazing tool
Download the sample file
Video: Create a lollipop chart in Excel
Create a lollipop chart with same oval color in Excel
Supposing you want to create a lollipop based on the data in the below screenshot, please do as follows to get it down.
1. Select the whole data range, click Insert > Insert Column or Bar Chart > Clustered Column if you want to create a vertical lollipop chart.
If you want to created a horizontal lollipop chart, click Insert > Insert Column or Bar Chart > Clustered Bar. See screenshot:
2. Then a clustered column chart or a clustered bar chart is inserted in the worksheet. Keep the chart selecting, and then click Design > Add Chart Element > Error Bars > Standard Error.
3. Select and right click the error bars in the chart, and then select Format Error Bars from the right clicking menu.
4. In the opening Format Error Bars pane and under the Error Bar Options tab, you need to configure as follows.
Now the chart is shown as follows.
5. In the Format Error Bars pane, you need to keep on configuring as follows.
The chart is displayed as the below screenshot shown.
6. Now you need to hide column bars in the chart. Click to select the columns, go to the Format Data Series pane, and then configure as follows.
Now the lollipop chart is finished.
Note: If you want to mark the oval with different colors, please try the below method.
Create a vertical lollipop chart with different oval colors in Excel
If you want to create a lollipop chart with different oval colors in Excel, please do as follows.
1. Select the whole data, click Insert > Insert Line or Area Chart > Line with Markers.
2. In the created chart, right click the line and select Format Data Series from the context menu.
3. In the opening Format Data Series pane, click the Fill & Line tab, select No line in the Line section.
Then the line in the chart is hidden as the below screenshot shown.
4. Keep the marks selecting, and then click Design > Add Chart Element > Error Bars > Standard Error.
5. Select the error bars in the chart, go to the Format Error Bars pane, and then configure as follows.
Now the chart is displayed as the below screenshot shown.
6. Click any one of the marks to select them all, go to the Format Data Series pane, and then configure as follows.
Now the lollipop chart with different oval colors is completed as the below screenshot shown.
Create a horizontal lollipop chart with same or different oval colors in Excel
In this section, you will learn how to create a horizontal lollipop chart with same or different oval colors in Excel.
1. Create a helper column beside you original data table as follows.
Note: For the number 11 in formulas, it represents the number of bars you will create in the chart based on the original axis labels. In my case, there are 11 axis labels in column A, so I specify the number 11 in the formulas. You can change it based on your own axis data.
2. Select the range of axis labels and the range of data range (here I select the range A1:B12), and then click Insert > Insert Column or Bar Chart > Clustered Bar.
3. Then a clustered bar chart is created in current worksheet. Please right click the vertical axis labels and then select Format Axis from the context menu.
4. In the opening Format Axis pane, check the Categories in reverse order box under the Axis Option tab.
5. Right click on any data series in the chart, and then click Select Data from the right-clicking menu.
6. In the Select Data Source dialog box, click the Add button. Then an Edit Series dialog pops up, click the OK button directly without any configuration.
7. When it returns to the Select Data Source dialog box, you can see a new series (Series2) is added in the Legend Entries (Series) box, click OK to save the changes and close the dialog.
8. Right click on any data series in the chart, and then select Change Series Chart Type from the right-clicking menu.
9. In the popping up Change Chart Type dialog box, change the chart type of Series2 to Scatter and then click the OK button.
10. Right click on any data series in the chart, and then click Select Data from the right-clicking menu.
11. In the Select Data Source dialog, select the Series2 in the Legend Entries (Series) box, and then click the Edit button.
12. In the Edit Series dialog box, please configure as follows.
13. When it returns to the Select Data Source dialog box, click the OK button to save the changes.
Now the chart is shown as below.
14. Click the secondary axis in the chart, and then go to the Format Axis pane to configure as follows.
15. Remove the secondary axis from the chart.
16. Now configure the bars as follows.
Now the bars are hidden in the chart.
17. Select the error bars, go to the Format Error Bars pane and configure as follows.
Now a horizontal lollipop chart with same oval color is created as the below screenshot shown.
Tips: If you want to specify different colors for the ovals, please change their colors one by one manually.
Please click an oval twice to select it only, go to the Format Data Point pane, enable the Fill & Line tab, click the Marker tab, and then specify a solid fill color and a solid line color for it.
Finally, the horizontal lollipop chart with different oval colors is shown as below.
Easily create a lollipop chart in Excel
The Lollipop Chart utility of Kutools for Excel can help you quickly create a vertical or horizontal lollipop chart in Excel with several clicks only as the below demo shown.
Download and try it now! 30-day free trail
Download the sample file
Video: Create lollipop chart in Excel
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
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.
