Make a nicer trendline in bar chart in Excel

[Jyggalag]

Hi All,

I currently have a setup similar to this:

I want to create a trendline for the 2022 bar charts. My issue is that no matter which one I pick (exponential, linear, logarithmic etc.) it doesnt touch the top of the bar charts for 2022 and it seems to just go through them.

Does anybody have a nice solution for how to make a trendline that touches the top of each yellow bar for 2022 and ends on the final one at 200?

I hope that my question makes sense - otherwise, please let me know and I will respond immediately!

Kind regards,
Jyggalag

 

[KenU]

I don't know that I would call it a *nice* solution, but it should give the result you want.

The trendline is behaving correctly. What you actually need is a combination plot.

Add an additional series to your existing chart (duplicate the data you are showing the line for and name it LINE so you can easily identify it). With the existing chart selected, go to Chart Design tab in the ribbon and select change chart type. There should be an option for a Custom type that will allow you to select the scatter straight line plot type (with or without markers) for the new LINE series you added. With the LINE series selected, you should see the SERIES formula in the formula bar. Edit that formula to include the X data for the series between the two adjacent commas. You can simply insert the cursor and select the X value range. Since this is a clustered column chart, you will have to make some adjustments to either the X values or the X-axis min and max to get it to align with the correct columns. You can select the extra axes and set their Label position to None so that they will not be displayed.

Maybe someone else has a better solution.

Hope that helps.

Regards,
Ken

 

[Jyggalag]

Thank you Ken! I will try and work on your solution tomorrow morning.

In the meantime, for anybody else stumbling upon this, it would be nice to have a trendline that touches the top of each bar until the end without going through them, if that is possible

 

[Jyggalag]

Update:

I found a nice solution for my problem (which I may have formulated very badly, apologies Ken and everybody else!).

My solution was to create two sets of the data that you want to insert (identical!). So one for the bar chart and one for the scatter line. I then called the bar chart data "2019, 2020, 2021 etc." and the scatter line data "2019 trendline, 2020 trendline, 2021 trendline".

Once I graphed it, I got the result that I was looking for At least for now :D

 

[Jyggalag]

Solution: