How to Insert Trendline in an Excel Cell (Create and Customization)

A trendline is a useful feature to analyze data in Excel. Excel users use this feature mainly for forecasting by analyzing the data. In this article, we will show you how to insert a trendline in an Excel cell.


Download Practice Workbook

You can download the free Excel workbook from here and practice on your own.


2 Easy Ways to Insert Trendline in an Excel Cell

To illustrate this article, we will use the following data set. Here, we have the data on the population of three big cities for four years. In this article, you will see two different procedures to insert a trendline in an Excel cell. In our first procedure, we will use the sparklines feature of Excel, and in our second method, we will add Excel column bar charts to accomplish the task.

Easy Ways to Insert Trendline in Excel Cell

1. Using Sparklines to Insert Trendline in an Excel Cell

In our first approach to inserting a trendline in an Excel cell, we will use a special feature of Excel called sparklines. To use this feature, go through the following steps.

Step 1:

  • First of all, we need to prepare our data set.
  • For that, we will make a table below our original data set.
  • Then, in the table, we will add three extra cells in C10, C11 and C12 to show the trendline.

Easy Ways to Insert Trendline in Excel Cell

Step 2:

  • Secondly, we will select the data range from C5 to C8.

Easy Ways to Insert Trendline in Excel Cell

Step 3:

  • Thirdly, go to the Insert tab of the ribbon.
  • Then, from there, go to the Sparklines command from the Sparklines group.
  • Next, select the Line Sparkline command from the three types of sparklines.

Easy Ways to Insert Trendline in Excel Cell

Step 4:

  • Fourthly, you will see a new dialogue box named “Create Sparklines” after choosing the previous command.
  • Here, you will notice that the Data Range type box is already filled up.
  • Then, select the location of the trendline in the Location Range type box.
  • For our example, we will choose cell C10 as the location of the first trendline.
  • Lastly, press OK.

Easy Ways to Insert Trendline in Excel Cell

Step 5:

  • Finally, you will see the trendline in cell C10 for the yearly population of City A.

Easy Ways to Insert Trendline in Excel Cell

  • Similarly, go through the previous steps to insert trendlines for the population of the other two cities as well.

Easy Ways to Insert Trendline in Excel Cell

Read More: How to Calculate Trend Analysis in Excel (3 Easy Methods)


2. Adding Column Bar Charts to Insert Trendline in an Excel Cell

There is another way to insert a trendline in an Excel cell. That is adding the  Excel column bar charts. You will find the whole procedure in the following.

Step 1:

  • First of all, select the data range from B4 to E8 for inserting into the chart.

Easy Ways to Insert Trendline in Excel Cell

Step 2:

  • Secondly, go to the Insert tab of the ribbon, and from there select the Recommended Charts command.

Easy Ways to Insert Trendline in Excel Cell

Step 3:

  • After that, a new box named Insert Chart will open.
  • From the box, select the chart as your choice.
  • In our procedure, we will choose the Clustered Column chart like in the below figure.

Easy Ways to Insert Trendline in Excel Cell

  • After that, you will see a column chart.
  • Then, name the chart as Yearly Population Trend.

Easy Ways to Insert Trendline in Excel Cell

Step 4:

  • Fourthly, to add the trendline to the chart, click on the chart.
  • Then, you will see three icons right beside the chart.
  • From those icons, select the Chart Elements command.
  • From there click on the Trendline option.

Easy Ways to Insert Trendline in Excel Cell

Step 5:

  • Then, a new box named “Add Trendline” will appear.
  • From there, select the first city and then press OK.

Easy Ways to Insert Trendline in Excel Cell

  • After that, you will see the trendline for City A in the chart.

Easy Ways to Insert Trendline in Excel Cell

Step 6:

  • To add trendlines to other bars of the graph, again click on the Chart Elements command.
  • Then, click on the More Options command from the menu.

Easy Ways to Insert Trendline in Excel Cell

Step 7:

  • Here, select City B and then press OK.

Easy Ways to Insert Trendline in Excel Cell

  • Finally, you will see the trendline in the chart for the second city.

Easy Ways to Insert Trendline in Excel Cell

  • Similarly, repeat the above steps to insert trendlines for all the bars.

Easy Ways to Insert Trendline in Excel Cell

Read More: How to Create Trend Chart in Excel (4 Easy Methods)


Similar Readings


Customizing Trendlines

If you want to make your trendlines more user-friendly and understandable, then you can customize them. It will add more value to your report and help you understand the analysis easily. Some of the ways of customizing these kinds of trendlines are as follows.

1. Altering Colors and Styles of Trendlines in an Excel Cell

In Excel, you have the option to alter the colors and styles of the trendline as per your preferences. You can also customize the trendline in this way by understanding the following steps.

Step 1:

  • First of all, choose the trendline, that’s color you will change.
  • Then, go to the Sparkline tab of the ribbon, which you will only see after choosing the trendline.
  • From there, go to the Sparkline Color command.
  • Now, under the Theme Colors option, select any color of your choice.

Easy Ways to Insert Trendline in Excel Cell

Step 2:

  • Finally, you will see that the color of your trendline will change.

Sample Data Set

  • Similarly, you can change the color of any trendline you want.

Sample Data Set

Step 3:

  • Again, if you wish to change the style of your trendline, then first of all choose the trendline.
  • Then, go to the Sparkline tab and from there choose the Style command.
  • Here, you will find many pre-existing styles and can choose any of them for your trendlines as per your preference.

Sample Data Set

Read More: How to Add Multiple Trendlines in Excel (With Quick Steps)

2. Adding Data Points in Trendlines in an Excel Cell

To make your trendlines more meaningful you can add data points to trendlines after creating them in Excel. For that see the following steps.

Step 1:

  • Firstly, select any trendline to add data points.
  • Then, go to the Show group in the Sparkline tab of the ribbon.
  • You will see many commands there. For our purpose, we will mark the Markers command.

Sample Data Set

Step 2:

  • Finally, the trendline will show all the data points in it.

Sample Data Set

  • Similarly, repeat these steps for making data points in other trendlines.

Sample Data Set

3. Modifying Trendlines after Adding from Excel Column Bar Charts

Like customizing trendlines using Sparklines features, you can also modify trendlines in bar charts in Excel. For modifying the sparklines, follow the following steps.

Step 1:

  • Firstly, double-click on any of the trendlines in the chart.
  • Then, you will see a window pane named “Format Trendline” at the right of the chart.
  • Here, under the Line label mark the Solid Line option.
  • Then, change the width of the chart with the Width command.
  • Lastly, change the Dash type of the trendline.

Sample Data Set

Step 2:

  • Finally, the appearance of the trendline will change after selecting these criteria.

Sample Data Set

  • Similarly, follow the above steps again to change the trendlines in the other two bars.

Sample Data Set

Read More: How to Add a Trendline to a Stacked Bar Chart in Excel (2 Ways)


Conclusion

That’s the end of this article. I hope you find this article helpful. After reading this article, you will be able to insert a trendline in an Excel cell by using any of these methods. Please share any further queries or recommendations with us in the comments section below.


Related Articles 

Araf

Araf

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo