Chart & Visualization Issues

Change an Excel Chart Line to Flat Ends

Steve KSteve K Nov 14, 2026 869 views

Question details

The user wants a thicker horizontal chart line with flat ends that do not extend beyond the specified X-axis limits.

How to Change an Excel Chart Line to Flat Ends
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

Select the chart, then open Developer > Visual Basic or press Alt+F11. In the VBA editor, choose Insert > Module.

2
Add the macro

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

3
Run and check the marker

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.

4
Hide the original line if needed

Select the original zero-line series and use Format Data Series > Fill & Line > Line > No line so it does not appear beneath the rectangle.

Draw a Flat-Ended Rectangle at Zero with VBA
Marker position is not automatic: The rectangle is a chart shape, not a data series. Rerun the macro after changing the chart size or axis limits; it replaces the previous marker.
Free Microsoft Office alternative

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.

Create and format spreadsheet charts in WPS SpreadsheetOpen and save Microsoft Excel formats, including XLSXUse a familiar spreadsheet interface for everyday editingWork with a lightweight, free Office alternative
microsoft office alternative - wps office

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.