How to Create and Customise a Lollipop Chart in Microsoft Excel for Clean Data Comparisons

Standard bar and column charts are the traditional choice for comparing categorical values, but when dealing with numerous categories, large numbers of bars create heavy visual clutter. High data-ink ratios can make executive dashboards look crowded and obscure key insights. A lollipop chart solves this problem elegantly by replacing bulky bars with slender lines capped with distinct circular data markers (resembling a lollipop). This design maintains precise visual alignment while introducing ample whitespace, making comparative rankings far cleaner to read.

While Microsoft Excel does not offer a native “Lollipop” chart type in its standard chart gallery, building one is straightforward using a 2D Bar or Line chart combined with custom Error Bars, or by using an XY Scatter Plot. Once constructed, you can customise marker colours, stem thicknesses, and direct data labels for boardroom-ready presentations. If you build executive dashboards in spreadsheets, you may also appreciate our tutorials on creating dumbbell charts in Microsoft Excel and designing bullet charts in Microsoft Excel.

Structuring Your Source Data

Before inserting any chart elements, arrange your dataset in a clean tabular format. Ensure categories are sorted in descending or ascending order so your lollipop chart delivers an intuitive visual hierarchy:

Department	Satisfaction Score (%)
Customer Support	94
Product Engineering	88
Sales & Marketing	82
Human Resources	76
Operations & Logistics	71

Method 1: Building a Horizontal Lollipop Chart Using Bar Charts and Error Bars

The most robust method for creating a horizontal lollipop chart leverages a standard 2D Bar Chart with customized error bars:

1. Select your data range including headings (for example, cells A1:B6).
2. Navigate to the Insert tab on the Excel ribbon, click Insert Column or Bar Chart, and select 2-D Clustered Bar.
3. Click on any of the rendered horizontal bars to select the entire data series.
4. In the Chart Elements menu (the green + icon at the top right of the chart), tick Error Bars, then click the small right-facing arrow and choose More Options to open the Format Error Bars pane.
5. Under Direction, select Minus. Under End Style, select No Cap.
6. Under Error Amount, select Percentage and enter 100%. This causes the error bar line to draw backwards from the data point to the vertical axis baseline.
7. Click the Fill & Line (paint bucket) icon in the Format pane, set the error bar line colour to a neutral grey or dark accent, and increase the width to 1.5 pt or 2 pt to form the lollipop stems.
8. Now click directly on the original bar series to select it. In the Format Data Series pane, set Fill to No fill and Border to No line. The thick bars become completely invisible, leaving only the error bar lines visible.

Adding and Formatting Circular Data Markers

To add the circular “candy” heads onto each stem:

1. With the chart selected, click Select Data on the Chart Design tab.
2. Click Add to create a secondary series. Set the Series Name to "Markers" and select the exact same satisfaction values (cells B2:B6) as the Series Values. Click OK.
3. Click Change Chart Type on the Chart Design tab, choose Combo, and locate your new “Markers” series. Change its chart type to Scatter (or Line with Markers).
4. Select the markers on the chart, open the Format Data Series pane, navigate to Marker > Marker Options, choose Built-in, select the circle icon, and set the size to 10 pt or 12 pt.
5. Choose a solid accent colour fill (such as deep blue or emerald green) and a white border to make the heads pop sharply against the grid.

Refining Labels and Axis Formatting

To deliver a professional presentation, clean up redundant chart elements and add direct values:

• Direct Data Labels: Click the + icon, tick Data Labels, and select Center. Change the label font colour to white so the numeric values sit neatly inside each circular marker, eliminating the need for a horizontal value axis.
• Remove Clutter: Delete the horizontal value axis and vertical gridlines by clicking them and pressing Delete.
• Category Order: If Excel reverses your category order, double-click the vertical category axis, open Axis Options, and tick Categories in reverse order to ensure your top-ranked item appears at the summit.