Trendline

Copper Contributor
Hello! I am trying to add a trendline to a histogram in Excel for Mac. I’ve looked at every tutorial, and I just don’t have the options that people are using in them. When I go to Chart Design, Add Chart Element, there is no Trendline option (not grayed out it just doesn’t exist). Could someone please help? Thanks in advance!
6 Replies

@Kendrahmilton  .... Hopefully, someone who uses Excel for Mac will respond.  Sorry, that's not me.  But in Excel 2010 for Windows, I find the Trendline option under Layout, not Design.

 

JoeUser_0-1643410096247.png

 

@Kendrahmilton As for as I know you can not add a trend line to a histogram. And that's the same for Mac and PC. Checked it on both platforms I suspect that you have been looking at videos where regular column charts were made that look like histograms. See picture below.

Screenshot 2022-01-29 at 06.51.43.png

When you right-click on the data series in a real histogram you will see the Add Trendline... option greyed out. But when you go via the Add Chart Element icon it's indeed not there, which makes sense.

Screenshot 2022-01-29 at 06.59.13.png

@Riny_van_Eekelen  wrote: ``As for as I know you can not add a trend line to a histogram. [....] I suspect that you have been looking at [...] regular column charts were made that look like histograms``

 

That seems to be a distinction without a difference.  Or I don't understand your terminology.  Or perhaps it is a limitation (!) of your version (and @Kendrahmilton's ?) of Excel.

 

First, according to the MSFT help page "Create a histogram":  ``A histogram is a column chart that shows frequency data.``  There is no difference, AFAIK.

 

I will demonstrate that below.

 

But you are correct:  __I__ created a Column Chart directly from the frequency data.

 

The following creates the "histogram" chart using the Data Analysis tool -- if that is the distinction that you are trying to make.

 

(Note: I am not adept at using the Data Analysis tool.  I usually do all this myself "manually".  So I make a few "adjustments" to the DA Histogram results in order to get the results that I want for presentation purposes.  I'm sure there is a better way.)

 

1. I enter 10 1s, 20 2s, 30 3s, 40 4s and 50 5s into A1:A150 (my data).

2. I enter 1, 2, 3, 4 and 5 into B1:B5 (my bin identifiers).

3. On the Excel "ribbon", I click Data > Data Analysis > Histogram and fill in the dialog box as shown.

 

JoeUser_0-1643443172376.png

 

4. After I click OK and make a few "adjustments" (hacks !), the result is:

 

JoeUser_5-1643444550022.png

 

Note the Chart Type -- Column -- after I right-click the series (one column in the chart) and click Change Series Chart Type.

 

5. When I right-click the series (one column in the chart), I get the following menu, which includes the Add Trendline option.

 

JoeUser_2-1643443985346.png

 

6. When I click Add Trendline and make a few "adjustments", the result is:

 

JoeUser_6-1643445016820.png

 

@Riny_van_Eekelen, do you see anything "wrong" (for the purposes of this discussion) with what I am doing?  Can you do the same thing in your version of Excel?

 

More to the point, @Kendrahmilton , can __you__ do the same thing in your version of Excel -- making adjustments for differences in the user interfaces between versions?

 

And again, there is no need to use the Data Analysis Histogram feature.  You can just the Insert > Charts > Column feature (or the equivalent in your version of Excel).

 

@Joe User ????

@Kendrahmilton 

 

I demonstrated how __I__ am able to add a (linear) trendline to a histogram (aka Column) chart.

 

But I should have noted that even if that "works" for you (i.e. you can add a trendline now), often the trendline is not what you intended, except for XY Scatter charts.

 

Consider the following example:

 

JoeUser_0-1643451152262.png

 

The "y" equation for the linear trendline in the Scatter chart on top is correct, because that is, in fact, the formula in column B.

 

The "y" equation for the linear trendline in the Column chart on the bottom is incorrect.  The reason is:  regardless of the labels that we use in the x-axis (they might not even be numeric), the treadline assumes that the "x" values ("independent variable") are 1, 2, 3 etc.

 

So a trendline for a histogram (Column) chart is valid only if the labels for your histogram columns are 1, 2, 3 etc, by coincidence.

 

To demonstrate, if we change the "x" values to 1, 2, 3 etc, the result is:

 

JoeUser_0-1643452429040.png

 

@Joe User Not sure what Excel version you are using, but as from 2016, Excel (both Mac and PC) contains a Histogram chart type that takes a single column of values from which the frequency of those values is determined and displayed in customizable bins/columns. And when you use this particular chart type you can NOT add a trendline, as it would be meaningless.

Riny_van_Eekelen_0-1643455172521.png