Views > How to create a Lollipop Chart in Excel

3. To calculate the Negative Error Value, add the following formula into cell F3
= IF(C3 < 0,0,C3)
4. Select cell F3 then drag the handle to copy the formula into cells F4:F55. The data source should look like this:

5. Select the Fatality rate 2017 series in the chart then click the Charts Elements button located on the upper-right corner of the chart. Next, in the expanded menu, check the Error Bars option, as per the screenshot below:

6. Select the Y Error Bars in the chart and then turn them off by pressing the Delete key on the keyboard. The chart should look like this:

7. Right-click the X Error Bars in the chart and then, on the shortcut menu, click Format Error Bars.
8. In the Format Error Bars pane, with Error Bars Option icon selected, set the End Style to No Cap.
9. Set Error Amount to Custom and click on the Specify Value button.
10. In the Custom Error Bars dialog box, edit the Positive Error Value to include cells E3:E55 and the Negative Error Value to include cells F3:F55 as follows:

11. Right-click the Horizontal (Value) Axis in the chart and then, on the shortcut menu, click Format Axis.
12. In the Format Axis pane, with Axis Options selected, reset the Bounds Minimum and Bounds Maximum to Auto.
13. Select the Secondary Horizontal (Value) Axis in the chart and then reset the Bounds Minimum and Bounds Maximum to Auto. The chart should look like this:

14. You may be wondering why I used both the Positive Error Value and the Negative Error Value. My intention is so to make the chart dynamic so that it could handle negative inputs without breaking down. Try it. Replace Michigan’s score—just for the sake of testing—with a negative value such as -1.0. The chart should look like this:

Thanks to Excel’s versatility and flexibility, I can transform the above chart into many different types by making few formatting changes. Let's have a look.

For example by doing the following:

- Delete the primary and secondary horizontal axis
- Delete the horizontal gridlines
- Remove the outline of the plot area and the vertical axis
- Change the color and line weight of the X Error Bars
- Change the size and fill color of the markers
- Add data labels to the data series

The chart will look like this:

If you find the dots too bulky and imprecise, you can replace them with vertical ticks. To achieve that you can do the following:

- Draw a short vertical line anywhere on the worksheet using the Shapes in the Illustration Group under the insert Tab
- Copy the newly created shape to the Clipboard by pressing Ctrl + C
- Select the markers of the data series in the chart and paste the shape by pressing Ctrl + V

The chart will look like this:

Or if you want to create a bar graph while emphasizing the end points you can do the following:

- Hide the X Error Bars by setting their lines to No Outline
- Change the Fill color and the outline of the Horizontal Bar series (which is hidden)
- Set the Gap Width of the Horizontal Bar series to 0
- As per previous chart, draw a short vertical line anywhere on the worksheet using the Shapes in the Illustration Group under the insert Tab. The length of the line should match the width of the Bar (may need few trials to succeed)
- Copy the newly created shape to the Clipboard by pressing Ctrl + C
- Select the markers of the data series in the chart and paste the shape by pressing Ctrl + V

The chart will look like this: