We can easily create a shaded target range in a line, column, area, or stepped area chart in Google Sheets. This tutorial explains how.

When you shade a target range, the chart becomes easy to read.

It will help you analyze whether the data points are within or outside the required range.

Here is a real-life example that shows the purpose of the shaded target range in a line chart in Google Sheets.

Example to Shaded Target Range in a Line Chart

Let me start with a finished graph containing a shaded target range.

The above chart visualizes monthly traffic (monthly visitors) to one blog from January to December in a year.

The webmaster has set a monthly target range and that is from 1.5 to 3 lacs (One Hundred Fifty Thousand to Three Hundred Thousand) visitors.

The line graph shows the actual traffic (number of visitors) from Jan to Dec.

From the graph, we can easily understand that the traffic is as per the webmaster’s expectation and even crossed it at some point, i.e., in July and August.

There are three shaded ranges in the above chart.

• Bottom – Lower the target (0 to 1.5 lacs).
• Middle – Target range (1.5 to 3 lacs).
• Top – Above the target (3 lacs and above).

Note:- There is one drawback and that is we can’t change the color of the lower limit to white/transparent in Google Sheets. If you set that aside, the graph serves the required purpose.

How to create a shaded target range as above in a line chart in Google Sheets?

I have the answer to it. If you wish, you can even change the Line (chart type) to the Column, Area, or Stepped Area graph.

I prefer the Line and Stepped area. Switching the chart type is very simple in Google Sheets. We will come to it later.

How to Create a Shaded Target Range in a Line Graph in Google Sheets

We will follow the above example to create a line graph with a shaded target range in Google Sheets.

But you can replace the data with sales figures, test results, etc., which have lower and upper limits.

We require well-formatted data, and that is the first and foremost thing to plot the said chart correctly.

The present data that we have is as follows in columns A and B in Google Sheets.

Data Preparation

Open a Google Sheet and copy-paste the above values in A1:B13.

We require three more columns, and they are for the lower limit, upper limit, and above the upper limit.

We know the lower limit is 1.5 and the upper limit is 3 as we want a shaded range in the line graph from 1.5 lacs to 3 lacs scale.

What about above the upper limit?

It depends on the maximum scale on the Y-axis. For the time being, you just fill that column with the values 0 (zero).

The above screenshot may help you format the data for creating a shaded or color-filled target range in a line, column, area, or stepped area chart in Google Sheets.

Steps to Create a Line Graph with Shaded Target Range in Google Sheets

First of all, we require to insert a blank chart that shows “No data.”

For that, click on cell G1 (a blank cell). Then go to the Insert menu and click on Chart. That’s it!

You can now optionally drag and place the blank (No data) chart to the required position.

Here are the must-to-follow settings under the Chart Editor Setup tab.

Setup Tab Settings

1. Chart Type – Combo chart.
2. Stacking – None.
3. Data Range – A1:E13.
4. X-axis – Months.
5. Series – Traffic, Lower Limit, Upper Limit and Above.

Customize Tab Settings

Now, you can see that the “No data” graph is filled with a combo chart.

To create a shaded target range for the line graph, we must customize it.

Here are the settings under the Chart Editor Customize Tab.

1. Click Series > Apply to all series.
2. Select “Traffic” and set the type to “Line.”
3. Select other series (Lower Limit, Upper Limit, and Above) and set the type to “Stepped Area.”
4. Now check the max scale value on the Y-axis on the chart. As per my above chart, it’s 4. So fill the cell range E2:E14 with 4.

We have created a shaded target range in a line chart in Google Sheets.

You can explore additional settings under the Chart Editor Customize tab to make the chart looks neat and clean.

If you have any doubt, you can copy my sample sheet below.

Example Sheet 26122

If you want a shaded column chart, do as follows.

In the above second customization step, select Column instead of Line. Here I would prefer Stepped Area instead.

That’s all. Thanks for the stay. Enjoy.

Related Resources

1. Get a Target Line Across a Column Chart in Google Sheets.
2. Average Line in Charts in Google Sheets – Line and Column.
3. How to Include Filtered Rows in a Chart in Google Sheets.
4. How to Use Slicer in Google Sheets to Filter Charts and Tables.
5. Add Legend Next to Series in Line or Column Chart in Google Sheets.
6. How to Move the Vertical Axis to Right Side in Google Sheets Chart.
7. How to Create a Multi-category Chart in Google Sheets.
8. Enabling the Horizontal Axis (Vertical) Gridlines in Charts in Google Sheets.
9. How to Get Dynamic Range in Charts in Google Sheets.