Change an Excel Chart Line to Flat Ends
Question details
The user wants a thicker horizontal chart line with flat ends that do not extend beyond the specified X-axis limits.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A VBA macro formats a horizontal line at y = 0 in an Excel chart.
- Observed behavior
- Increasing the line weight extends the line horizontally as well as vertically, and applying the CapStyle property to the chart line produces an error.
Select the chart and check that its primary vertical axis uses a linear scale with zero between its minimum and maximum. The macro below draws across the full plot area; use it only if the desired line should span the displayed X-axis range.
Draw a Flat-Ended Rectangle at Zero with VBA
Use a rectangle as a visual substitute when the chart line does not support CapStyle.
The rectangle's width matches the plot area's width, while its height controls the apparent line thickness. This changes the visual marker, not the chart's axis limits or data.
The macro assumes a standard, non-reversed primary vertical value axis. Run it again after resizing the chart or changing the axis limits.
Select the chart, then open Developer > Visual Basic or press Alt+F11. In the VBA editor, choose Insert > Module.
Paste this code into the module: Sub DrawFlatZeroLine() Dim ch As Chart, ax As Axis, pa As PlotArea Dim y As Double, thickness As Double Dim marker As Shape If ActiveChart Is Nothing Then MsgBox "Select a chart first." Exit Sub End If Set ch = ActiveChart Set ax = ch.Axes(xlValue, xlPrimary) Set pa = ch.PlotArea If ax.ScaleType <> xlScaleLinear Or ax.ReversePlotOrder Then MsgBox "Use a linear, non-reversed primary value axis." Exit Sub End If If ax.MinimumScale >= 0 Or ax.MaximumScale <= 0 Then MsgBox "Set the vertical axis minimum below zero and maximum above zero." Exit Sub End If thickness = 4 ' Points; change to adjust the line height. y = pa.InsideTop + pa.InsideHeight * ax.MaximumScale / (ax.MaximumScale - ax.MinimumScale) On Error Resume Next ch.Shapes("FlatZeroMarker").Delete On Error GoTo 0 Set marker = ch.Shapes.AddShape(msoShapeRectangle, pa.InsideLeft, y - thickness / 2, pa.InsideWidth, thickness) marker.Name = "FlatZeroMarker" marker.Line.Visible = msoFalse marker.Fill.ForeColor.RGB = RGB(0, 0, 0) End Sub
Return to Excel, select the chart, and run Developer > Macros > DrawFlatZeroLine. Check that the rectangle aligns with zero and ends at the plot area's edges.
Select the original zero-line series and use Format Data Series > Fill & Line > Line > No line so it does not appear beneath the rectangle.

Create and Edit Spreadsheet Charts in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for editing spreadsheets and charts. Open Excel files to make the move easier, although WPS does not fix Excel's CapStyle error.

Frequently Asked Questions
Why does CapStyle produce an error on my Excel chart line?
Excel chart lines may not expose CapStyle in the same way as other Office drawing objects, even when properties such as line weight and dash style work. Use a shape as a visual workaround rather than relying on that property.
Does the rectangle workaround change my X-axis limits?
No. The macro places a rectangle across the existing plot area and does not edit the axis minimum or maximum. Check the limits under Format Axis > Axis Options.
How can I make the zero marker thicker?
In the VBA module, change the value of thickness in the DrawFlatZeroLine macro. The value is measured in points; rerun the macro after editing it.
Will the rectangle move automatically if I resize the chart?
No. Select the chart and rerun Developer > Macros > DrawFlatZeroLine after resizing it or changing the axis limits.




