Note: The other languages of the website are Google-translated. Back to English

How to shade the area between two lines in a line chart in Excel?

When creating a line chart for two data series, do you know how to shade the area between the two lines as shown in the screenshot below? This tutorial provides two methods to help you get it done step by step.

Shade the area between two lines in a line chart by inserting helper columns
Easily shade the area between two lines in a line chart with an amazing tool


Shade the area between two lines in a line chart by inserting helper columns

Supposing you have a monthly sales table for two years and want to create a line chart for them and shade the area between the two lines in the chart. Please do as follows to handle it.

1. Firstly, you need to create two helper columns.

In the first helper column, you need the same values as in column 2018.
Give the column a header, select the cell next to the column header, enter the formula below and press the Enter key. Select the formula cell and drag its AutoFill Handle down to get all results.
=B2
In the second helper column, you need to calculate the difference between the values in two years. Please apply the following formula.
=C2-B2

2. Select the whole range, click Insert > Insert Line or Area Chart > Line to insert a line chart.

Tips: For the line chart you have already created, please skip this step and jump to the Note.

Now a line chart is created as shown in the screenshot below.

Note: If you have already created a line chart for the original series (see the following screenshot) and want to shade the area between the lines. After creating the two helper columns, you need to do as follows.

1) Right click the chart and choose Select Data from the context menu.
2) In the Select Data Source dialog box, click the Add button.
3) In the Edit Series dialog box, select the series name and series values in the first helper column separately, and then click the OK button.
4) When it returns to the Select Data Series dialog box, repeat the step 2) and 3) above to add the series name and series values of the second helper column.
5) In the Select Data Source dialog box, you can see that the newly added series are displayed in the Legend Entries (Series) box. Click OK to save the changes. See screenshot:

3. Right click on any line in the chart and select Change Series Chart Type in the right-clicking menu.

4. In the Change Chart Type dialog box, change the chart type of the Base and Difference series to Stacked Area and click the OK button to save the changes.

The chart now is displayed as follows.

5. Select the largest highlighted area in the chart, and then click Format (under the Chart Tools tab) > Shape Fill > No Fill.

6. Now you need to remove the newly added series from the legend as follows.

Click to select the entire legend, then click on the name of the series you want to delete from the legend and press the Delete key.
Here I removed the Base series and the Difference series from the legend. The chart now is displayed as follows.

Tips: If you want to remove the entire legend from the chart and display only the series names on the corresponding lines in the chart, please do as follows.

1). Select the legend and press the Delete key to remove it from the chart.
2). Click twice on any data point in the line to select it only. Click the Chart Elements button > the black triangle (next to the Data Labels) > More Options.
3). In the opening Format Data Label pane, uncheck the Value and Show Leader Lines checkboxes which are checked by default. Then check the Series Name checkbox.
4). The data label is now added to the chart. To make it easy for others to figure out which series the data label belongs to, you need to change the font color of the data label to the same color as the line of that series.
Select the data label, change the font size and color as you need under the Home tab.
5). Repeat the step 7, 8 and 9 to add another data label to the second series.

7. Select the shaded area in the chart, click Format (under the Chart Tools tab) > Shape Fill. Then choose a lighter fill color for it.

8. Change the chart title as you need.

The chart now is finished. And the area between the two lines is highlighted as shown in the screenshot below.


Easily shade the area between two lines in a line chart with an amazing tool

The above method has many steps and is very time-consuming. Here I recommend the Different Area Chart feature of Kutools for Excel to help you easily solve this problem. With this feature, you don’t need to create helper columns, just need several clicks to get it done.

1. Select the whole data range, click Kutools > Charts > Difference Comparison > Difference Area Chart.

2. In the Difference Area Chart dialog box, you can see that the fields are automatically filled with corresponding cell references, click the OK button directly.

The chart is created. See the screenshot below.

Note: You can right-click on any element of the chart to apply a different color or style as you want.


Demo: View and analyze full headers of an email in Outlook

  Kutools for Excel includes 300+ powerful features for Microsoft Excel. Free to try with no limitation in 30 days. Download now!


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL