To create an overlapping area chart in Excel for Microsoft 365 on Windows, select your category labels and data series, then choose Insert > Insert Line or Area Chart > 2-D Area > Area. Because later series can cover earlier ones, make the fills partly transparent: right-click a series, choose Format Data Series, then use Fill & Line > Fill > Transparency. Adjust the series order and colors until all the areas are readable.
What an overlapping area chart shows
An area chart displays values across categories or time and fills the space between each series and the chart’s baseline. In a regular, or non-stacked, area chart, each series is plotted from that baseline rather than added to the preceding series. This makes it possible to compare separate trends while also emphasizing the size of each value.
“Overlapping area chart” is a description of the visual effect, not a separate chart type in Excel’s Insert menu. The chart type is Area. Its series share the same plot area, and the fill belonging to a series drawn later may obscure the fill beneath it. Excel does not automatically make all the fills transparent, so adjusting transparency is usually the key step.
For example, imagine monthly visits to three pages: Home, Products, and Support. A non-stacked area chart can show each page’s visits against the same monthly axis. The areas overlap wherever their values occupy the same vertical range. With opaque fills, the front series can hide much of the others; with transparency, color blends reveal where those ranges coincide.
Arrange the source data first
Excel needs one column or row for category labels and one for each series. Put the categories in the first column and series names in the top row, with no blank header cells. For instance:
- Column A: Month, followed by January, February, March, and so on.
- Column B: Home visits, with one value per month.
- Column C: Product visits, with one value per month.
- Column D: Support visits, with one value per month.
Use consistent units and a consistent time interval. If the chart compares monthly counts, make sure every series is a monthly count rather than mixing counts, percentages, or rates. Keep the values numeric; text that looks like a number may not plot as expected. A clear source table also makes it easier to add a new month or series later.
Area charts are most natural when the categories have a meaningful sequence, such as dates, stages, or ordered ranges. If the horizontal labels are unrelated names, a bar or column chart may make comparisons easier. A standard area chart generally spaces category labels evenly. If actual time intervals vary—for example, measurements taken one day apart and then a month apart—an area chart may not communicate the spacing accurately. Consider an XY scatter chart when the horizontal position must reflect numeric intervals.
Create the chart in Excel
On Windows in Microsoft 365, select the entire source range, including the headers and category labels. Then follow these steps:
- Open the Insert tab.
- In the Charts group, choose Insert Line or Area Chart.
- Under 2-D Area, select Area. Avoid Stacked Area if you want each series to retain its own values against the baseline.
- Check that the horizontal axis shows the intended categories and that the legend names match the series.
- Use Chart Design > Add Chart Element to add or change a chart title, axis titles, or legend if needed.
If Excel puts months in the legend and series names on the horizontal axis, select the chart and choose Chart Design > Switch Row/Column. This switches how Excel interprets the selected data; it does not change the worksheet itself. To correct an incomplete or oversized selection, use Chart Design > Select Data and check the chart data range and the entries under Legend Entries (Series) and Horizontal (Category) Axis Labels.
On Excel for Mac, select the data and use the Insert tab’s chart controls to insert an Area chart; labels and ribbon placement can differ slightly from Windows. Excel for the web also offers chart insertion and formatting, but some chart-formatting controls may be more limited or arranged differently. If a transparency control is unavailable in your version, opening the workbook in the desktop Excel app is a practical option.
Make the filled areas transparent
Transparency lets the color of a rear series show through a front series. It does not change the underlying values or make the chart stacked; it changes only the appearance of the fill. Apply it separately to each series whose fill blocks information.
- Click a colored area once to select the series. If you select only one point, click the series again or use the chart’s formatting controls to select the whole series.
- Right-click the selected series and choose Format Data Series. The Format Data Series pane opens.
- Choose Fill & Line, shown as a paint-bucket icon, then expand Fill.
- Choose Solid fill if necessary, select a color, and adjust the Transparency control. Start around 35% to 50%, then inspect the chart and revise it.
- Repeat for the other series. Use distinct hues and keep enough opacity that each area remains identifiable.
The best setting depends on the number of series, the colors, and the background. A low transparency percentage means a more opaque fill; a high percentage means more of the background and underlying colors show through. If transparency is too high, the areas can become faint, especially on a projector or when printed. If it is too low, the front series can still conceal the others. There is no single percentage that works for every chart.
Consider adding an outline to each area so its boundary remains visible where fills blend. In the same pane, expand Border and choose Solid line, then select a line color and width. A darker outline related to the fill color can help distinguish neighboring series without making the whole chart look like a tangle of heavy lines. If outlines cross frequently, use thinner lines or omit some of them.
Control which series appears in front
Transparency is not the only way to improve readability. The plotting order affects which series is visually on top. Select the chart and choose Chart Design > Select Data. In the Select Data Source dialog, use the up and down controls in Legend Entries (Series) to change a selected series’ order. In a typical area chart, earlier series are drawn before later ones, so a later series can appear in front. Check the result after changing the order, because the most important series may need to remain prominent.
Put a series that should be easy to see near the front, but do not rely on order alone when the fills are opaque. A broad, high-valued series plotted on top may cover smaller series for much of the chart. A useful combination is to place the main series later in the list, give it a recognizable color, and retain enough transparency to reveal the other shapes underneath.
Series order also affects the legend order, which may matter to readers. If the order that looks best visually makes the legend confusing, explain the visual priority in the chart title or a short note, or use a different chart design. Do not reorder a chart merely to make the colors look attractive if that makes the legend difficult to match to the areas.
Choose between area, stacked area, and line charts
These chart types answer different questions, even though they can all show multiple series over time:
- Area: Each series is shown against the baseline using its own values. Overlapping transparent fills help compare trends and approximate magnitudes, but the intersections can be visually busy.
- Stacked Area: Series are added to one another, so the upper boundary represents a running total. This is useful when the components make up a meaningful total, but the vertical position of most individual series no longer starts at zero.
- 100% Stacked Area: Each category is normalized to 100%. It emphasizes how the share of a whole changes, not the original totals. A category with 100 visits and one with 10,000 can look equally tall.
- Line: Lines emphasize direction and changes over time without filled regions hiding one another. Use a line chart when comparing exact trends matters more than emphasizing area or volume.
A common misconception is that making a regular area chart transparent turns it into a stacked chart. It does not: transparency affects color blending, while stacking changes how Excel calculates and positions the series in the chart. Choose the chart type based on the question the data should answer, then format it for legibility.
Common problems and practical fixes
- One series disappears: It may be behind an opaque series, have values much smaller than the others, or have the same color as a neighboring area. Increase transparency on the covering fill, change the series order, and check the value scale.
- The chart shows the wrong categories: Use Chart Design > Select Data to confirm the horizontal axis label range. If series and categories are reversed, try Chart Design > Switch Row/Column.
- The visual impression is misleading: A non-stacked area chart can make a large value seem dominant because its filled region occupies substantial space. Include a clear axis scale and units, and consider a line chart when the task is to compare precise values.
- Colors become muddy: Overlapping transparent colors blend. Reduce the number of series, choose more distinguishable hues, increase opacity slightly, or use lines instead of fills.
- Gaps or unexpected drops appear: Check the source cells for blanks, text, or error values. Excel’s treatment of empty cells can be reviewed through Chart Design > Select Data > Hidden and Empty Cells. The available options can vary by chart and Excel version.
- Labels are crowded: Avoid adding a data label to every point when there are many months. Label selected points, use a clear legend, or add a small data table beside the chart.
Also consider accessibility and output size. Do not distinguish series by color alone: use descriptive legend labels, contrasting outlines, and a title that states what is being compared. Check the chart at the size readers will actually see it. A chart that looks clear on a large monitor may lose its distinctions when pasted into a small report or printed in grayscale.
Frequently Asked Questions
Can Excel make an area chart transparent?
Yes. Select a series, open Format Data Series, and adjust Fill & Line > Fill > Transparency. Transparency is set for each series’ fill, so repeat the adjustment for other areas as needed.
How do I put one area series in front of another?
Use Chart Design > Select Data and change the series order under Legend Entries (Series). Later series are generally drawn over earlier ones in a regular area chart. Review the legend after reordering, since its order may change too.
What is the difference between an area chart and a stacked area chart?
A regular area chart plots each series from the baseline. A stacked area chart adds each series to the preceding one, making the top edge show a cumulative total. Use stacking only when that cumulative interpretation is appropriate.
Why can’t I see the smaller area in my chart?
A larger or later opaque series may cover it. Try increasing the covering series’ transparency, changing the plotting order, or switching to a line chart. Also confirm that the smaller series uses the same units and is not on a scale that makes it hard to see.
Can I make an overlapping area chart in Excel for Mac?
Yes. Excel for Mac supports area charts and series formatting, though ribbon labels and control placement can differ from Windows. Insert an Area chart, select a series, and look in its formatting pane for fill transparency.
Related
- Microsoft Excel Custom Number Formats: A Practical Guide
- Microsoft Excel Custom Number Format Codes Explained
- Microsoft Excel Contact List Template: Setup and Tips
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table Row Labels: How to Change the HeaderIn Excel for Microsoft 365 on Windows, click the PivotTable cell that says Row Labels, type the heading…
- Excel to Visio Org Chart: Import, Map, and TroubleshootYes. In Visio for Windows, choose File > New > Organization Chart > Create, select employee information stored…
- How to Create an Org Chart in Excel: Steps and TipsTo create an org chart in Excel for Microsoft 365 on Windows, select Insert > SmartArt > Hierarchy…
- Schedule Gantt Chart in Excel: A Step-by-Step GuideTo make a schedule Gantt chart in Excel, place each task, its start date, and its duration in…
- Manually Rearrange a Pivot Chart in ExcelYes. In Excel, manually rearrange a PivotChart by changing the item order in its associated PivotTable: drag a…
- Excel Stacked Bar Chart Next to Non-Stacked: How ToYes—but Excel does not offer a one-click chart type that reliably places a stacked bar and a regular,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.