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.
How to Insert Trendline in an Excel Cell: 2 Easy Ways
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 for inserting 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.
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.
Step 2:
- Secondly, we will select the data range from C5 to C8.
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.
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.
Step 5:
- Finally, you will see the trendline in cell C10 for the yearly population of City A.
- Similarly, go through the previous steps to insert trendlines for the population of the other two cities as well.
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.
Step 2:
- Secondly, go to the Insert tab of the ribbon, and from there select the Recommended Charts command.
Step 3:
- After that, a new box named Insert Chart will open.
- From the box, select the chart of your choice.
- In our procedure, we will choose the Clustered Column chart like in the below figure.
- After that, you will see a column chart.
- Then, name the chart as Yearly Population Trend.
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.
Step 5:
- Then, a new box named “Add Trendline” will appear.
- From there, select the first city and then press OK.
- After that, you will see the trendline for City A in the chart.
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.
Step 7:
- Here, select City B and then press OK.
- Finally, you will see the trendline in the chart for the second city.
- Similarly, repeat the above steps to insert trendlines for all the bars.
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, and you will change the color.
- 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.
Step 2:
- Finally, you will see that the color of your trendline will change.
- Similarly, you can change the color of any trendline you want.
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.
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.
Step 2:
- Finally, the trendline will show all the data points in it.
- Similarly, repeat these steps for making data points in other trendlines.
Read More: How to Create Equation from Data Points in Excel
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. To modify 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.
Step 2:
- Finally, the appearance of the trendline will change after selecting these criteria.
- Similarly, follow the above steps again to change the trendlines in the other two bars.
Download Practice Workbook
You can download the free Excel workbook from here and practice on your own.
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.
