Forum Discussion
Chart displaying "Trendline Error"
I have charts which plot linear data ranges with trendlines using named ranges and OFFSET to make the ranges dynamic. Sometimes the chart series data range is a pair of cells containing zeros. In the past this was not a problem; the point in those chart ranges just plot at 0,0 on the chart. Recently, however Excel also displays the word Trendline Error (presumably because there is only one data pair:
This is terrible. The plotting of the points and data values of 0 at the origin isn't too bad but I do not want the words Trendline Error displayed just because there is only a single data pair.
This is a recent problem. I have been using this technique for years and now the workbooks that have some chart ranges with empty cells do this. Below is what happens if there are two charge ranges like this.
Is there any way of switching this error display off?
24 Replies
- It's frustrating that this is still happening. The trendline error and re-appearing equation issue is happening to some users on my team (but not others) in the same template spreadsheet where manually fixing each series it's happening with is not a good option. Is there any solution to fix this permanently? I can't even effectively troubleshoot since it's not happening on my own computer.
EW_86 Frustrating indeed, but not sure if you saw my "temporary" fix which is simply to make the trendline formula transparent and then the Trendline Error will no longer show. See my previous post for screenshots.
Very annoying indeed. I found a way to hide the Trendline Error by formatting the Trendline Label and making the text completely transparent.
- Would really like to see a solution to this issue. Is there a way to configure if we want the "Trendline Error" message to show or not, regardless of there being an error?
- I found a way to turn this off. Under Chart Design, Add Chart Element, choose Trendline > None. And error goes away.
As the problem occurs when displaying trendlines I'm not surprised that removing the trendline removes the error message.
seiscons This has popped up in a regular report that I update frequently. I have narrowed it down to one series that has three negative numbers in it. If I make them positive, the error goes away. Super frustrating. It was not happening in that file before 11/17/2020.
blaughlin1992 I deleted that series causing the error and added it back. Error gone. A mystery.
I am surprised to read that the problem is still occurring. A while back the problem went away for me (Excel 365) and all of the workbooks that exhibited the problem started to work properly. I did report it by posting a "Comment" to Microsoft and the problem went away a few weeks later. I thought that they had fixed it.
- Create Excel charts with trendline, error bars, data tables, data labels in C# using Spire.XLS
Tools free us from extra work. Here I use .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.
Step 1: I use the following code to create a column clustered chart (see the screenshot) with trendline and data table using the .dll in Visual Studio.
Screenshot: (R2=0.9688, which means the trendline is very fit to the data.)
Figure 4: Chart
Code
//Create an Excel and add a sheet
Workbook workbook = new Workbook();
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
//Fill Excel cells with sample data
sheet.Name = "sample 1";
sheet.Range["A1"].Value = "Month";
sheet.Range["A2"].Value = "Jan.";
sheet.Range["A3"].Value = "Feb.";
sheet.Range["A4"].Value = "Mar.";
sheet.Range["A5"].Value = "Apr.";
sheet.Range["A6"].Value = "May.";
sheet.Range["A7"].Value = "Jun.";
sheet.Range["B1"].Value = "Porter";
sheet.Range["B2"].NumberValue = 15;
sheet.Range["B3"].NumberValue = 18;
sheet.Range["B4"].NumberValue = 24;
sheet.Range["B5"].NumberValue = 32;
sheet.Range["B6"].NumberValue = 37;
sheet.Range["B7"].NumberValue = 40;
sheet.Range["C1"].Value = "Harry";
sheet.Range["C2"].NumberValue = 38;
sheet.Range["C3"].NumberValue = 32;
sheet.Range["C4"].NumberValue = 17;
sheet.Range["C5"].NumberValue = 35;
sheet.Range["C6"].NumberValue = 45;
sheet.Range["C7"].NumberValue = 43;
sheet.Range["D1"].Value = "Chocolate";
sheet.Range["D2"].NumberValue = 30;
sheet.Range["D3"].NumberValue = 28;
sheet.Range["D4"].NumberValue = 35;
sheet.Range["D5"].NumberValue = 23;
sheet.Range["D6"].NumberValue = 33;
sheet.Range["D7"].NumberValue = 38;
//Create a columnclustered chart
Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);
chart.DataRange = sheet.Range["B1:D7"];
chart.SeriesDataFromRange = false;
chart.TopRow = 4;
chart.BottomRow = 27;
chart.LeftColumn = 2;
chart.RightColumn =11;
chart.ChartTitle = "Chart with trendline and datatable";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
Spire.Xls.Charts.ChartSerie cs1 = chart.Series[0];
cs1.CategoryLabels = sheet.Range["A2:A7"];
//Add trendline and datatable to the chart
chart.Series[0].TrendLines.Add(TrendLineType.Exponential);
chart.HasDataTable = true;
//Save the document as .xlsx file
workbook.SaveToFile("Sample1.xlsx", ExcelVersion.Version2010);
Step 2: Using the same sample data, I create another chart to show the error bars in Excel using C#. Only the different codes are listed.
Chart chart = sheet.Charts.Add(ExcelChartType.Line);
chart.DataRange = sheet.Range["C1:D7"];
chart.SeriesDataFromRange = false;
chart.TopRow = 4;
chart.BottomRow = 27;
chart.LeftColumn = 2;
chart.RightColumn =11;
chart.ChartTitle = "Chart with error bars";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
chart.Series[0].CategoryLabels = sheet.Range["A2:A7"];
chart.Series[0].ErrorBar(true, ErrorBarIncludeType.Plus, ErrorBarType.Fixed, 2);
chart.Series[1].ErrorBar(true, ErrorBarIncludeType.Both, ErrorBarType.Percentage,5); - I noticed the equations but assumed I must have displayed them. Thanks for that. There is something definitely going on here and I suspect it is a result of a recent update. Are you also using Excel 2016 and updating it regularly, Bonnie?
- having same issue. leaving this here to see if some finds solution
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. The attached workbook illustrates this. 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. (The second chart in the workbook shows all the data plotted with a single series). So:
this data plots like this >
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):
this is what happens>
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).
then produces>
So the problem occurs when the data for a Chart Range is all zero.
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. The workbooks are protected and the charts produced are provided to clients and included in technical reports. To me it looks like this behavior is the result of a recent Excel update as it only just started happening.
