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.

4.1) Select Minus in the Direction section;
4.2) Select No Cap in the End Style section;
4.3) Select the Percentage option in the Error Amount section, and then change the percentage to 100%.

Now the chart is shown as follows.

5. In the Format Error Bars pane, you need to keep on configuring as follows.

5.1) Click the Fill & Line tab;
5.2) Specify a color for the error bars;
5.3) Change the width for the error bars as you need;
5.4) Select the Oval Arrow option from the Begin Arrow type drop-down list.

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.

6.1) Click the Fill & Line tab;
6.2) In the Fill section, select the No fill option;
6.3) In the Border section, select the No line option.

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.

5.1) Select Minus in the Direction section;
5.2) Select No Cap in the End Style section;
5.3) Select the Percentage option in the Error Amount section, and then change the percentage to 100%.

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.

6.1) Go to the Fill & Line tab;
6.2) Shift to the marker options view by clicking the Marker tab;
6.3) Check the Vary colors by point box in the Fill section.

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.

1.1) Select a blank cell (here I select C2), enter the below formula into it and press the Enter key.
=1/11/2
1.2) Select the cell below C2 (here is C3), enter the below formula into it and then press the Enter key.
=C2+1/11
1.3) Select C3, and then drag the Fill Handle to apply the formula to other cells.

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.

12.1) In the Series name box, enter the series name as you need;
12.2) In the Series X values box, select the range of data value without the header;
12.3) In the Series Y values box, select the range of helper data without the header;
12.4) Click the OK button. See screenshot:

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.

14.1) Change the Maximum value to 1 under the Axis Options tab;
14.2) Check the Values in reverse order box.

15. Remove the secondary axis from the chart.

16. Now configure the bars as follows.

16.1) Click on any one of the bars to select them all;
16.2) Click Design (Chart Tools tab) > Add Chart Element > Error Bars > Standard Error to add error bars.

16.3) Keep the bars selecting, go to the Format Data Series pane, select No fill and No line in the Fill and Border sections successively under the Fill & Line tab.

Now the bars are hidden in the chart.

17. Select the error bars, go to the Format Error Bars pane and configure as follows.

17.1) Select the Minus option in the Direction section;
17.2) In the Error Amount section, select the Percentage option and then change the percentage to 100%.

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

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.