Create Excel Charts With Trendline, Error Bars And Data Labels Using C#

Using charts, we are able to understand huge data and their relationships easily for charts can visualize and present raw data via line, pie, bar, area, scatter. Excel provides numerous charts solution. However, the default charts created by Excel might not contain those excellent tools such as trendline, error bars, and data tables in chart, which are often ignored by most of users though those analysis tools are boosts to display data. And those chart elements are also widely ignored by programmers when they create charts. So in this article, I’m going to give you a detailed introduction about trendline, error bars and then I’ll show you how to use free Spire.XLS to create excel charts with trendline, error bars, data table and data labels in C#.

The following three parts are included in this article:

• Trendline types, features and usage.
• Error bar types and settings in Excel.
• Create charts with trendline, error bars, data table, and data labels in C# using Spire.XLS.

Part 1

Detailed Introduction Of Trendline

Trendline in Excel has the following six types: Linear, Logarithmic, Polynomial, Power, Exponential, and Moving average. We should choose different type of trendline based on the type of the data and we could check the reliability of trendline by the R-squared value (near or at 1 is excellent). Please note that trendline can be only added to 2-D, area, bar, bubble, column, line, stock, scatter and unstacked chart.

• A linear trendline is usually used to show something is increasing or decreasing at a steady rate. When the data is simple, try it to show the trend. 
  
  
• A logarithmic trendline is appropriate to show the rate of change in the data increases or decreases quickly and then levels out for both positive values and negative values. 
  
  
• A polynomial trendline is a curved line to show the fluctuations of data. We should choose different order of polynomial trendline based on how many bends (hills and valleys) the curve has. 
  
  
• A power trendline is the best-fit curved line to show whether data increase at a specific rate. It can only be used for positive data (not zero or negative).
  
  
• An exponential trendline is a curved line used mostly to show data rise or fall at increasingly higher rates. It also can’t be used for zero or negative data. 
  
  
• A moving average trendline consists of average data points with lines. Its data points are decided by the period option we set. The moving average trendline works fine to smooth the fluctuation and illustrate the trend more clearly. 

Trendline Options In Excel


                                                   Figure 1: Trending Options

Part 2

Detail Of Error Bars In Excel Charts

Error bars are a graphical representation of the variability of data and are used on graphs to indicate the error, or uncertainty in a reported measurement (quoted from Wikipedia). Using error bars in Excel chart, we could easily see margins of error and standard deviation. There are 5 options in Excel to set error type: fixed value, percentage, standard deviation, standard error and custom specified value. The difference between standard deviation and standard error in Excel charts lies in different equations, please refer to the following picture from Microsoft Office official website.



                                                            Figure 2: Standard Error


                                                                Figure 3: Standard Deviation

Besides the type, we could also set the direction of error bars: both, minus and plus. Please note that only 2-D area, bar, column, line, scatter, and bubble charts support error bars.

Since it’s not difficult to understand the meaning of data tables and data labels in Charts, here I’m not going to give more introduction about them in detail. Once you see the charts with data tables and data labels in the part 3, you’ll know them clearly.

Part 3

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);

Please see the effect


                                                            Figure 5: Chart with Error

Step 3: In the final part, I’m going to create a chart with data labels, by which the value, legend key, percentage (pie chart), series name, and category name can be added to the plot area of the chart.

Effect screenshot


                                                            Figure 6: Effect Screenshot

Namespace in this part

using Spire.Xls;
using System.Drawing;

Codes

Workbook workbook = new Workbook();
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
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 = 8;
sheet.Range["B3"].NumberValue = 15;
sheet.Range["B4"].NumberValue = 3;
sheet.Range["B5"].NumberValue = 9;
sheet.Range["B6"].NumberValue = 13;
sheet.Range["B7"].NumberValue = 17;
Chart chart = sheet.Charts.Add(ExcelChartType.LineMarkers);
chart.DataRange = sheet.Range["B1:B7"];
chart.SeriesDataFromRange = false;
chart.TopRow = 4;
chart.BottomRow = 27;
chart.LeftColumn = 2;
chart.RightColumn =11;
chart.ChartTitle = "Chart with data labels";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
Spire.Xls.Charts.ChartSerie cs1 = chart.Series[0];
cs1.CategoryLabels = sheet.Range["A2:A7"];

// Set the displayed elements for data labels.
cs1.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
cs1.DataPoints.DefaultDataPoint.DataLabels.HasLegendKey = false;
cs1.DataPoints.DefaultDataPoint.DataLabels.HasPercentage = false;
cs1.DataPoints.DefaultDataPoint.DataLabels.HasSeriesName = true;
cs1.DataPoints.DefaultDataPoint.DataLabels.HasCategoryName = true;
cs1.DataPoints.DefaultDataPoint.DataLabels.Delimiter = ". ";

//Set the font and fill effect for data labels.
cs1.DataPoints.DefaultDataPoint.DataLabels.Size = 9;
cs1.DataPoints.DefaultDataPoint.DataLabels.Color = Color.Red;
cs1.DataPoints.DefaultDataPoint.DataLabels.FontName = "Calibri";
cs1.DataPoints.DefaultDataPoint.DataLabels.Position = DataLabelPositionType.Center;
cs1.DataPoints.DefaultDataPoint.DataLabels.FrameFormat.Fill.Texture = GradientTextureType.BlueTissuePaper;

workbook.SaveToFile("Sample1.xlsx", ExcelVersion.Version2010);

Conclusion

Thanks to Spire.XLS, I’m able to show you how to add trendline, error bars, data labels and data tables for Excel charts programmatically in C#. I hope you could have a deeper understanding of chart tools in Excel after reading my article. Thank you for reading.