Area Between Two Lines Chart in Excel: A Clear Method

Charts & pivot tables · Updated November 19, 2026

To shade the area between two lines in Excel, create helper columns for the lower value and the difference between the two lines, plot those columns as a stacked area chart, hide the lower series, then overlay the original data as line series. This works best when the lines do not cross often and the values are nonnegative; crossings and unevenly spaced x-values need extra care.

What an area-between-lines chart shows

This chart emphasizes the gap between two measurements, not just the measurements themselves. It can help compare actual results with a target, two scenarios over time, or the range between a high and a low estimate. The shaded band makes the size of the difference easier to spot than two lines alone, while the lines preserve the context of each series.

Excel does not provide a single built-in chart command called “area between two lines.” A regular area chart fills from a series down to the axis; it does not automatically fill only the space between two arbitrary lines. The helper-column method makes that space behave like a stacked area, then uses the original series to draw the visible boundaries.

Set up the worksheet and helper columns

Put the horizontal-axis values in column A and the two lines in columns B and C. Add a lower-bound column and a band-width column. For example, use this layout in rows 1 through 6: A1 is X, B1 is Line A, C1 is Line B, D1 is Lower, and E1 is Band. Enter five observations in rows 2–6. For a row where Line A is 10 and Line B is 7, the lower value is 7 and the band width is 3.

With the x-values in A2:A6 and the two line values in B2:B6 and C2:C6, put this formula in D2 to calculate the lower of the two values. It returns 7 when B2 is 10 and C2 is 7:

=MIN(B2,C2)

With the same layout, put this formula in E2 to calculate the distance between the lines. It returns 3 for values of 10 and 7:

=ABS(B2-C2)

Copy both formulas down through row 6. The helper values update for each observation: Lower identifies the bottom edge of the band, and Band gives its thickness. Using the absolute difference keeps the band width positive even when Line B is above Line A. Keep the original data columns; you will need them to add the visible lines to the chart.

Create the stacked area chart

  1. Select the helper columns D1:E6 and choose Insert > Insert Line or Area Chart > 2-D Area > Stacked Area. This creates a stacked area chart from Lower and Band.
  2. Set the horizontal-axis labels to the values in A2:A6. Right-click the chart and choose Select Data. Under Horizontal (Category) Axis Labels, choose Edit, select A2:A6, and confirm. If Excel assigned the wrong series names or ranges, correct them in the same dialog.
  3. In Select Data, add Line A and Line B as chart series. Use B1 as the Line A series name and B2:B6 as its values; then use C1 and C2:C6 for Line B. Keep the existing category labels from A2:A6.
  4. Select the chart and choose Chart Design > Change Chart Type > Combo. Set Lower and Band to Stacked Area, and set Line A and Line B to Line. Confirm the chart types and close the dialog.

The stacked area uses Lower as its starting level and Band as the amount stacked above it. The top of the filled band therefore represents the higher of the two values, while its bottom represents the lower. The line series restore the two original boundaries. If the lines do not appear above the fill, use Chart Design > Select Data to adjust the series order, or check that both line series are set to Line in the Combo dialog.

Hide the lower area and format the band

The Lower series is necessary for positioning the band, but it should not look like a second filled region. Click the Lower area in the chart, open Format Data Series, and set Fill to No fill and Border to No line. Be sure you selected Lower rather than Band; selecting the wrong series can make the shaded region disappear.

Click the Band series and choose a fill color that contrasts with the chart background. Reduce transparency if you want the gridlines and nearby chart elements to remain visible. Then format Line A and Line B with distinct colors and a visible line width. Keep the fill lighter than the line colors so the boundary lines remain easy to distinguish.

Add a useful chart title and, if needed, axis titles. A legend should identify the two lines; you may also rename the Band series to something meaningful such as “Difference.” Consider removing that legend entry if it might confuse readers, since the band is a visual aid rather than a third measured line. The Chart Design and Format tabs appear when the chart is selected in Excel for Microsoft 365 on Windows. Excel for Mac has comparable chart-formatting controls, though some dialog layouts and selection gestures differ.

What happens when the lines cross?

The formulas calculate the lower and upper value independently at each row, so the band still spans between the two values at every recorded x-value. However, if the lines cross between recorded observations, the chart only knows the values at the rows you supplied. It connects plotted points between those rows; it does not calculate the exact crossing point for you. The shaded band may therefore fail to pinch to zero at the true crossing, and it will not show which line is higher through the interval as clearly as a split band would.

For a more faithful crossing, insert a row at the estimated intersection, calculate both line values at that x-value, and ensure the two values match there. The band width at that added row will then be zero. For straight-line interpolation between two observations, the crossing can be found from the two line segments; if the data are measured or nonlinear, treat an interpolated crossing as an estimate rather than an observed fact.

If the fill should change color depending on which line is higher, one continuous Band series is not enough: its color cannot change automatically at each crossing. Split the gap into separate helper series for the intervals where A is above B and where B is above A, using blanks or unavailable values outside each interval, then format those series with different fills. This takes more setup and requires careful handling of crossing rows.

Important limits: axes, negative values, and missing data

A stacked area chart uses a category axis. Excel places categories at regular intervals, so it is a natural fit for equally spaced observations such as consecutive weeks or months. If the x-values are dates with irregular gaps, the area chart may give those observations equal visual spacing instead of spacing them by elapsed time. A line or XY scatter chart can represent numeric spacing differently, but shading between two scatter lines is not as direct as the stacked-area method and may require a more involved construction.

The method also relies on the stacked series behaving as a lower base plus a positive band. It is most straightforward when both line values are nonnegative. With negative values, Excel’s stacked area behavior separates positive and negative contributions, so the plotted band may no longer sit between the intended boundaries. Do not assume that the same helper formulas guarantee a correct visual result in that case; check the plotted geometry and consider a different chart construction.

Missing values deserve attention too. A blank or error in either source series can produce a gap, an unexpected segment, or a band that appears to continue differently from the lines. Decide whether a missing observation should break the display or be interpolated, and handle it consistently. Avoid replacing unknown values with zero unless zero is genuinely the measurement: doing so can create a misleading spike or filled region.

Choose a chart type that fits the question

  • Use the shaded band when the distance or range between two series is central to the message, such as actual versus target or two forecast scenarios.
  • Use two ordinary lines when exact values and crossings matter more than the size of the gap. This is often the clearest choice for irregular x-values or frequent crossings.
  • Use a conventional range chart when the data naturally have a lower and upper bound, such as a measured interval. In that case, name the bounds clearly rather than implying they are two independent lines.
  • Use a column chart of differences when the main question is the gap at each discrete observation. A difference column can make positive and negative changes easier to compare, but it does not show a continuous filled region between two curves.

These choices answer different questions. A shaded band emphasizes the space between boundaries; it is not automatically a confidence interval, uncertainty range, or statistical significance display. Use those labels only when the data and method justify them.

Common problems and fixes

  • The fill begins at zero: The chart may be a regular Area chart instead of a Stacked Area chart, or the Lower series may not be part of the stacked area. Recheck the Combo settings.
  • The whole chart looks filled: Format the Lower series as No fill and No line. Leave the Band series filled.
  • The band is between the wrong values: Check that Lower uses the smaller value in each row and Band uses the absolute difference, and confirm that the chart references the intended columns.
  • The x-axis shows 1, 2, 3 instead of dates or labels: In Select Data, edit Horizontal (Category) Axis Labels and select the correct range in column A.
  • The lines are hidden: Verify that both source series are included and assigned the Line chart type. Then inspect series order and line color against the fill.
  • The band appears to jump: Check for sorted x-values, missing observations, duplicate categories, or a crossing that needs an added intersection row.

Frequently Asked Questions

Can Excel fill the area between two lines automatically?
Not with a single “between two lines” chart option. Use helper columns for the lower value and the difference, plot them as a stacked area, hide the lower fill, and add the original series as lines.

Can the shaded area change color when the lines cross?
Not with one continuous filled series. Divide the data into intervals according to which line is higher, create separate helper series for those intervals, and format each series with its own fill. Add rows at intersections for a cleaner transition.

Does the method work with dates?
Yes, when dates are regular categories such as one value per month. A standard area chart spaces categories evenly, so it does not preserve irregular time gaps. If elapsed time between dates matters, consider a different chart approach.

Can I use negative values?
The helper formulas can calculate negative lower values, but stacked area charts handle positive and negative portions separately. The resulting fill may not match the space between the original lines, so verify the chart carefully or use another construction.

Can I do this in Excel for the web?
Excel for the web supports many chart and formatting tasks, but available chart controls can differ from the Microsoft 365 desktop app. If the Combo chart or series-formatting controls you need are unavailable in your browser, open the workbook in Excel for Windows or Mac and make the chart there.

Related

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.