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