I created a really simple chart based on 2 columns (date and numeric value).
I click on the chart, go to Chart Tools/Layout. The Trendline drop down is greyed out.
What am I missing?
4 Answers
- Just select the chart you want to add/activate a trendline then
- click "INSERT" tab just after "home" office tab then
- click the drop down arrow of "Area" chart type, then
- select "Area" instead of "Stacked area"
One more cheat for a line chart with the problem of grayed trendline box is to change the chart to scatterplot, create the trendline, and then insert an object (line) that overlies the trendline. Then convert back to the original line plot. You lose the trendline, but you keep your inserted object. Clunky, but it works!
1You need to make sure your dates are proper Excel dates, not just text representation of dates.
An easy way to test this is select your dates in the column and press Control + 1 to format them as numbers. If all the cells turn to numbers then you have proper Excel dates. If they still show as dates then they are text representations of dates and you'll need to convert before you can create a trendline.
Excel can't add a trend line to certain types of graphs - mine for example is a stacked bar, and Excel grays out the "Trend line" option.
Here's my workaround: I added a third data series for the "total" (sum of my stacked data series); this gives me a stacked bar twice as high as I want. Then I right-click on my new "total" series and change the chart type to "line", giving me my original stacked bar plus a line connecting the tops of all the bars.
Right-click on the "Total" line, and "Add a trendline" will be active. Click on it and Excel will create the trend line for the "total" series.
Lastly, get rid of that "Total" line cluttering up your chart. Right-click on the "Total" line and select "Format Data Series". Change formatting to make the markers and fill "No Color" and line style "No Line". Hey presto! Now your stacked bar chart has a trend line, based on the now invisible "Total" line.
Hope this helps!
