Screenshot: (R2=0.9688, which means the trendline is very fit to the data.) Step 1: I use the following code to create a column clustered chart (see the screenshot) with trendline and data table using the. dll from free Spire.XLS to create Excel charts and add all the chart tools I mentioned above if you would like to try, you could get it from E-iceblue official website or Nuget. To me it looks like this behavior is the result of a recent Excel update as it only just started happening.Ĭreate Excel charts with trendline, error bars, data tables, data labels in C# using Spire.XLS The workbooks are protected and the charts produced are provided to clients and included in technical reports. I appreciate your interest, Sergei, as this is a serious problem for us because this occurs in workbooks that are used in a professional setting. So the problem occurs when the data for a Chart Range is all zero.

Then if I then undo these changes (the 0 entries), the trendline formula appears attached to the second Chart Range trendline as one of the other contributors mentioned (the formula can be deleted - but not on a protected workbook of course). If I then ender a zero into all the cells for row 4 to 6 the words 'Trendline Error' appears over the top of the left axis (and the second series all plots at 0,0): (The second chart in the workbook shows all the data plotted with a single series). The first chart in the workbook plots data from the table in two ranges with the first Chart Series plotting data from rows 2 to 4 and the second Chart Series plots data from rows 4 to 6. I have had a closer look and it is clear that the problem occurs when a Chart Series with a Linear Trendline plots data that is all zeros.

Excel 2016 trendline equation