Excel Line Chart: Change Color Partway Through a Line

To change a line chart’s color partway through in Excel for Microsoft 365 on Windows, split the plotted values into two helper series, make each series return NA() outside its section, and format the two series in different colors. Let both series include the value at the transition point so the colored lines meet without a gap.

Why a line chart needs separate series

Excel assigns a line color to a data series. A normal line chart draws one continuous series, so changing that series’ color changes the whole line, not just the portion after a chosen date or value. The practical workaround is to make two series from the same source data: one supplies the earlier points and the other supplies the later points. Each can then have its own color.

This technique is useful for showing a change in status or meaning, such as actual results before and after a target date, a forecast after the latest observation, or values below and above a threshold. The data itself remains unchanged; the helper columns only control what the chart draws. You can use more than two helper series if the line needs several color sections.

Think of the transition as a shared point, not a break between two unrelated points. The first series draws the line up to that point, and the second draws from it onward. That shared value anchors both colored sections at the same position on the chart.

Set up helper columns for the color change

Suppose column A contains dates in A2:A8 and column B contains the values in B2:B8. The example values are 12, 14, 13, 17, 16, 20, and 18. To change color at the fifth worksheet row, which contains the value 17 in this example, create two helper columns. Put names such as Before and After in C1 and D1.

With the source values in B2:B8 and the first section ending at worksheet row 5, enter this formula in C2 and fill it down through C8:

=IF(ROW()<=5,B2,NA())

It returns 12, 14, 13, and 17 in C2:C5, then #N/A in C6:C8. With the same source layout and the second section beginning at worksheet row 5, enter this formula in D2 and fill it down through D8:

=IF(ROW()>=5,B2,NA())

It returns #N/A in D2:D4, then 17, 16, 20, and 18 in D5:D8. The value 17 is deliberately present in both series. The first color draws the incoming segment to that point; the second color draws from that point onward. In a real worksheet, change the row number 5 in both formulas to the row containing your desired transition point.

NA() produces the not-available error, which Excel charts generally do not plot as a numeric value. This is preferable to using zero, because zero would create a visible drop to the bottom of the chart. Keep the original data column intact; the helper formulas refer back to it, so updates to the source values flow through to the chart.

Create a chart from the two sections

If you already have a line chart, you can edit its series rather than start over. On Windows, select the chart and choose Chart Design > Select Data. Remove the original series, then add the Before and After series. In the Select Data Source dialog, use Add for each series, set Series name to the appropriate heading in C1 or D1, and set Series values to C2:C8 or D2:D8. Set the Horizontal (Category) Axis Labels to A2:A8 so the dates remain on the horizontal axis.

If you are making a new chart, one straightforward route is to select A1:B8, choose Insert > Insert Line or Area Chart, and insert a basic Line chart. Then use Chart Design > Select Data to replace the original values series with the two helper series as described above. This avoids relying on Excel to interpret a selection of separated columns correctly. In the chart, the horizontal labels should be the dates from column A, and the legend should identify the two colored sections.

On Excel for Mac, the chart tools and Select Data controls are broadly similar, though dialog layout can differ; use Command rather than Ctrl when a step requires selecting multiple separate ranges. Excel for the web can handle many chart edits, but its available formatting controls can vary. If a Select Data option or series setting is missing in the browser, open the workbook in the desktop app.

Choose the colors and make the join clear

Click one of the line sections once to select its series, then right-click and choose Format Data Series. In the Format Data Series pane, open Fill & Line, expand Line, and choose a color under Color. Repeat for the other series. You can also select a series and use the chart formatting controls on the ribbon. Choose colors with enough contrast against the chart background and against each other; the meaning should not depend on a subtle shade difference.

For a clear presentation, consider adding a short label or a legend entry that explains the change, such as Actual and Forecast. If the chart already has many series, a legend can become confusing; direct labels or a note near the transition may be more useful. Avoid making the shared point look like a separate observation. If both series show duplicate markers at the transition, select each series, open Format Data Series, and set Marker Options to None, or use the same marker style and color for both.

For a status change, a vertical reference line or a text label can clarify why the color changes. The two-series method changes the line’s appearance but does not add an annotation automatically. A reader should be able to tell whether the new color means forecast, warning, a new phase, or simply a different category.

Decide where the transition belongs

A color boundary needs a precise definition. If a series changes from actual to forecast after the final actual observation, the shared point is often the last actual observation. The actual-colored series reaches that point, and the forecast-colored series begins there. This makes the boundary visible without shifting the data or inventing an extra category.

For a threshold, decide whether the color changes at the first value that meets the rule or on the segment leading to it. A line segment connects two observations, so the chart cannot show the exact moment a continuously changing measurement crossed a threshold unless you have a measured crossing point or calculate one. Coloring by observation is a visual classification of points and connecting segments, not proof of the precise crossing time.

For date-based data, confirm the axis is displaying the intended order. A line chart uses a category axis, and dates may appear as evenly spaced categories even when the time intervals differ. If unequal gaps between dates matter, an XY scatter chart with straight lines may represent the spacing more faithfully. The helper-series principle still applies, but chart setup and axis handling differ, so check that the date values are being treated as numeric X values.

Other ways to color a portion of a line

Formatting an individual data point is useful when the goal is to highlight one observation, especially its marker. Select the series, select the specific point again, and open Format Data Point. This is not as convenient for recoloring a long run of segments, and the result can be difficult to maintain if the data changes. Use it for a single highlighted point; use helper series for a stable section boundary.

A second option is to build the chart with multiple separate series from the beginning, such as one series for each business phase. That can be easier when the groups are already distinct in the data. For a simple change partway through one existing line, helper columns are usually more transparent: someone inspecting the worksheet can see exactly which values feed each color.

Do not confuse this with changing the chart’s overall theme colors. A chart style or theme can recolor entire series, but it does not identify where one series should change color. Similarly, changing the line color in Format Data Series affects that whole series, which is why the split-series setup is needed.

Common problems and how to fix them

A gap appears at the boundary: Check that both helper series include the same source value at the transition row. If one starts on the following row, the two colored portions may not meet as intended.
The line drops to zero: Replace zeros used as placeholders with NA(). A zero is a real plotted value and can distort the line and the vertical scale.
The dates or categories look wrong: In Chart Design > Select Data, check that A2:A8 is assigned as the Horizontal (Category) Axis Labels and that the series values refer only to the helper columns.
The whole line changes color: You may have selected the original series or the chart area rather than one helper series. Select the Before or After series by clicking its line or its legend entry, then format that series.
The legend is cluttered: Rename the helper headings to meaningful labels, or remove redundant entries if the chart’s audience can identify the sections from labels on the chart. Keep an explanation of the colors somewhere visible.
The chart stops reflecting edits: Confirm the helper formulas were filled to every source row and the chart ranges include all those rows. If rows are added later, expand the chart data range or use an Excel Table so the source range can grow with new records.

When diagnosing a boundary, inspect the helper columns before adjusting chart formatting. If the formulas return the intended values and errors but the line still looks wrong, check the plotted ranges and category labels next. This separates a data-preparation issue from a formatting issue.

Frequently Asked Questions

Can I change just one part of a line without adding columns?
For a dependable color change across several segments, separate helper series are the clearest method. Formatting one selected data point can highlight a marker, but it is less suitable for maintaining a whole section as the data changes.

Can I use this method for more than two colors?
Yes. Create one helper series for each section, return source values only within that section, and use NA() outside it. Give neighboring series a shared transition value where you want their colored lines to meet.

Will the helper formulas update when my data changes?
They update when values in the referenced source cells change. If you move the transition point, you must also update the row boundary in the formulas, unless you design the formulas to refer to a separate boundary cell.

Why not use a blank cell instead of NA()?
Blank-cell behavior can depend on chart settings, and Excel may show gaps or connect across blanks according to those settings. NA() clearly marks values that should not be plotted as ordinary numbers; do not substitute zero.

Can I change the line color automatically when values cross a target?
Yes, by making helper series whose formulas test each value against the target instead of testing the row number. Decide how to handle values exactly equal to the target and whether color applies to points or connecting segments, since those choices affect the visible boundary.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.