Excel Chart with Two Trendlines

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 :

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. 

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.

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 :

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 :

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.

rishabhchakrabortygfg

Follow

Improve

Article Tags :

• Excel
• ExcelGuide