Open In App

Excel Chart with Two Trendlines

Improve
Improve
Like Article
Like
Save
Share
Report

A line that bounds a particular chart and shows the behavior as it propagates is known as a trend line. It is generally used for analytics purposes to get a close approximate idea from the chart. The chart can be of any type like Bar Chart, Scattered Chart, Line Chart, etc.

In this article, we are going to see how to add two trendlines in a chart using Excel. For this, we will consider a suitable example shown below.

Example: Consider a scenario of a country having a population of more than 50 crores. People in this country are suffering from a pandemic and the number of positive cases due to a deadly virus is increasing day by day. Finally, after six months vaccines were made and the government started a vaccine jab for the people after a lot of trials.  

The government ordered the officials to keep a record of data for the first ten days of the vaccine jab. The record should contain two columns :

  1. The first column consists of the number of vaccines jab to the people.
  2. The number of positive cases.

The moto behind is to check by mass jabbing of vaccines how the number of positive cases are changing during the first ten days. The experts from health centers made the following analysis :

               Pandemic Analysis
Day Vaccinated Number of  Positive Cases
1 20000 120000
2 24345 111000
3 27555 100555
4 35000 92950
5 46345 81950
6 57900 69690
7 60000 54990
8 71200 39632
9 78955 19389
10 91366 7895

Implementation :

Step 1: Insert the data in the cells of Excel. Now select all the data by dragging and then go to the Insert Tab from the top of the Excel window.

Step 2: Now select “Scattered or Bubble Chart set” and from the pop down select “Scattered Chart” as shown below. 

Inserting Chart

Step 3: Now to add “Trend Lines” use the “+” button and select Trend Line and select the series for which trend line needs to be added. Initially, we will add a trend line for the chart showing Vaccines. 

Another way is : 

  • Select the particular series for which a trend line needs to be added.
  • Right-click on it and select “Add Trendline”.

Now, to add the trend line for the second series “Cases” again click on the “+” button, and under Trendline select “More Options” and then select the series “Cases” and click OK.

The second Trendline Added

Step 4: Now various modifications can be done on the above chart to make it more insightful.

1. Changing Chart Style

After changing the chart style, we can add a suitable “Chart Title“.

2. Formatting Trend Line: Select the trend line to be formatted and right-click on it. From the pop-down select “Format TrendLine”. Now, we can change the line type, color, width according to our needs.

Also, by default, the Trendline that would be added by Excel will be of Linear type. So, to show the equation of the line and R-squared value check the box in the Format Trendline window as shown below :

Chart with two Trendlines

3. Changing the style of Trendline: We can change the Trendline from Linear to various other types of Trendlines Excel offers. Follow the steps :

  • Select the Trendline.
  • Right-Click.
  • Select “Format Trendline.

Now change the Trendline type from the Format Trendline window as shown below :

Formatting Trendline Style

Finally, the chart will look like this:

4. Changing the axis: We can change the scale of the axes. The steps are :

  • Select the axis.
  • Right-click.
  • Select Format Axis.

Now make necessary changes in the values of Bounds and Units. We have solved the “Days” axis and made the following changes. You can try for the other axis and make the necessary changes.

Finally, after all the modifications the chart looks like this:

From the above chart, we can infer that as the number of days is increasing more people are getting vaccinated and there is a huge decline in the number of positive cases as we can see in the green color Trendline. The yellow color Trendline depicts there is a tremendous increase in the number of people getting vaccinated.


Last Updated : 27 May, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads