To show a shaded area between two lines in Excel, use a combo chart with a stacked-area helper series for the band and line series for the boundaries. For a simpler chart with vertical bars between two lines, use Up/Down Bars instead; the best choice depends on whether you want a continuous-looking band or point-by-point comparisons.
What “chart area between lines” means
Excel uses “Chart Area” for the full chart object, including its title, legend, and axes. The region where data is drawn is the Plot Area. Neither term is a special command for coloring the space between two data series, so a chart’s ordinary fill settings won’t create that effect.
Instead, you need a chart feature or a chart structure that represents the gap. The distinction matters because a regular line chart draws each series as a line, while an area chart normally fills from a series down to the horizontal axis. To fill only the space between two values, Excel needs a way to account for the lower boundary as well as the size of the gap.
Choose the right method for your chart
There are two common interpretations of “between lines,” and they produce different visuals:
- Continuous band: Use a stacked-area helper series when you want a filled region following the gap between two boundaries, such as a target range or an upper and lower estimate.
- Vertical intervals: Use Up/Down Bars when you want a separate bar between the two values at each category, as in a comparison of two line series over time.
A continuous band emphasizes the range as a whole; Up/Down Bars make individual observations easier to compare. Neither method is ideal for every chart. For example, a band can become visually confusing when the two lines cross often, while bars can look crowded when there are many closely spaced dates.
Prepare the data and calculate the gap
For the stacked-area method, arrange the data with one category per row and the lower and upper boundaries in separate columns. For example, put dates in A2:A6, lower values in B2:B6, and upper values in C2:C6. Add a fourth column called Gap.
With lower values in B2:B6 and upper values in C2:C6, enter this in D2: =C2-B2. If the lower value is 12 and the upper value is 18, the result is 6. Fill the formula down the Gap column so each row calculates the difference between its two boundaries.
Check the data before making the chart. The upper value should be at least as large as the lower value at every point for this straightforward setup. Missing values, text stored as numbers, or inconsistent categories can cause unexpected blank sections or misaligned lines. If the boundaries cross, the Gap becomes negative; a basic stacked-area band is not a reliable way to display that crossover, as described below.
Create a shaded band with a combo chart
In Excel for Microsoft 365 on Windows, create a combo chart from the prepared columns. A practical setup uses the lower series as a hidden stacked-area base, the Gap as the visible stacked-area series, and the original lower and upper series as lines.
- Select the category, lower, upper, and Gap columns, including their headers.
- Choose Insert > Insert Combo Chart > Create Custom Combo Chart. If the chart already exists, select it and choose Chart Design > Change Chart Type > Combo.
- In the combo chart settings, set the lower series and Gap series to Stacked Area. Set the original lower and upper series to Line. Leave Secondary Axis unchecked for all four series so they share a scale.
- Confirm the chart. If the lines are hidden behind the filled area, open Chart Design > Select Data and adjust the series order so the line series are plotted after the area series.
- Select the lower stacked-area series, open Format Data Series, and set its fill and line to No fill and No line. It remains part of the stack mathematically, but its color is hidden.
- Format the Gap series with the band color and a suitable transparency. Keep the upper and lower line series visible, using contrasting line colors or weights.
The lower area contributes the vertical position of the band, and the Gap area adds the distance from the lower line to the upper line. Hiding the lower fill leaves the visible color between those boundaries. The two line series are still present, so the reader can see the exact edges of the band.
Menu names can vary slightly with ribbon layout and updates. In Excel for Mac, the combo-chart options are also available from the Insert tab or through Chart Design > Change Chart Type after selecting a chart; the dialog layout may differ. Excel for the web has more limited chart-formatting options than the desktop app, so finish detailed combo-chart formatting in Excel for Windows or Mac if a setting is unavailable.
Use Up/Down Bars for point-by-point gaps
If your chart is a two-series line chart and you want a vertical bar connecting the values at each category, Up/Down Bars may be more direct. Select the chart, then choose Chart Design > Add Chart Element > Up/Down Bars > Up/Down Bars. Excel adds bars between the two line values at each category; you can format the up and down bars separately.
This feature is useful for showing which of two series is higher at each date, such as actual versus forecast. It is not a continuous shaded ribbon: the bars are discrete and do not connect horizontally. The option is intended for suitable line or stock charts and may be unavailable or behave differently for other chart types. If the menu item is disabled, check that the chart has two appropriate line series and try changing its type to a line chart first.
Format the band so it stays readable
A filled region can dominate a chart if its color is too dark. Use a pale fill or transparency so both boundary lines remain clear, and choose line colors that are distinct from the band. If the band represents uncertainty or a target range, explain that meaning in the title, legend, or a note rather than relying on color alone.
Other useful refinements include:
- Use a concise title that identifies what the boundaries represent, such as “Monthly sales range: low to high.”
- Label the vertical axis with units. A band from 10 to 20 means something different when the unit is dollars than when it is thousands of units.
- Reduce unnecessary gridlines and keep the category axis readable. For long date series, consider showing fewer date labels rather than shrinking them.
- Keep the two boundary lines visible and use direct labels or a clear legend if readers might confuse them.
For accessibility, do not communicate an important distinction through color alone. Use line styles, labels, or a short chart note as well. If the chart will be printed in grayscale, preview it that way or use patterns and contrasting line styles where available.
Common problems and how to fix them
- The fill runs down to zero: The chart is probably a regular area chart, or the lower base series is missing. Use a stacked-area band setup with the lower series hidden, rather than filling the Gap as an ordinary area series.
- The band sits at the wrong height: Confirm that the hidden base uses the lower boundary and the visible area uses upper minus lower. Check the category order and make sure the chart’s series refer to the intended columns.
- The band hides one or both lines: Reorder the series so the line series render after the areas, then format the lines with a visible color and weight. Also check that all series use the same axis.
- The chart has a blank or odd-looking section: Look for blank cells, text values, or errors in the source data. Check whether the category axis is treating dates as dates or as text categories; this changes spacing between observations.
- The two boundaries cross: The simple Gap calculation produces negative values where the upper boundary falls below the lower one. A stacked area is not designed to make a single clean band that switches sides this way. Consider splitting the data into non-crossing segments, using Up/Down Bars, or showing the two lines without a fill.
Also remember that a stacked-area chart changes how values are visually accumulated. The transparent lower series is a structural helper, not a claim that values are additive. This is why labeling the chart and verifying the plotted boundaries against the source table are important.
Alternatives when a combo chart is not suitable
For a range that is more important than the individual boundary lines, a box-and-whisker chart may be appropriate if the data represents a statistical distribution and the chart’s quartiles match the story you need to tell. It is not a direct substitute for two custom line series.
You can also leave the two lines unfilled and add a descriptive note, or use Up/Down Bars for discrete comparisons. For a one-off graphic with complex crossings or custom shapes, a manually drawn shape may work, but it will not adjust reliably when the data changes. The helper-series combo chart is generally more maintainable because its band follows the worksheet values.
Frequently Asked Questions
Can Excel automatically shade between two line series?
Not as a general continuous fill through a single line-chart setting. Use a stacked-area helper series for a band, or Up/Down Bars for separate intervals at each category.
Why does an area chart fill below the line instead of between lines?
A standard area series fills toward the axis baseline. To place a visible fill between two values, use a stacked series for the lower boundary and the difference between the boundaries, then hide the lower fill.
Can I shade the area between lines that cross?
A simple stacked-area setup is not dependable when the boundaries cross because the calculated gap becomes negative. Split the data into separate sections or choose a line-only chart or point-by-point bars.
Can I use this method for a confidence interval?
Yes, if the upper and lower limits are correctly paired for each category and the limits do not cross. Label the interval clearly, and avoid implying that the filled region has a statistical meaning beyond what the underlying analysis supports.
Does the shaded band update when the data changes?
It updates when the chart’s source range includes the changed cells and the Gap formulas recalculate. If new rows are added outside the source range, expand the chart data range or use an Excel Table so added rows can be included more conveniently.
Related
- Excel Conditional Formatting Number Formats Explained
- Excel Conditional Formatting With Dates: A Practical Guide
- Excel Conditional Formatting for Date Ranges Explained
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Gauge Chart in Excel: Build and Format OneTo make a gauge chart in Excel for Microsoft 365 on Windows, create a doughnut chart from three…
- Excel Pivot Table Sort in Data Source Order: How It WorksExcel does not automatically keep PivotTable row labels in the order they first appear in the source data.…
- Excel Area Chart: How to Create and Format OneTo create an area chart in Excel for Microsoft 365 on Windows, select your data, then choose Insert…
- Pivot Table Excel: How to Create and Use OneTo create a PivotTable in Excel for Microsoft 365 on Windows, select a cell in a clean data…
- Excel Pie Charts With Text Data: What Works and Why?Excel cannot use words alone to set the sizes of pie-chart slices: slice sizes must come from numeric…
- Excel Pivot Table Field List Not Showing? Fixes in ExcelIf the PivotTable Field List is missing in Excel for Microsoft 365 on Windows, click any cell inside…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.