This blog is the second and last part of a multi-part series that highlights the Excel 2016 charts and shows how to create a Sales Dashboard using GcExcel API.

In today’s world, where the competition is at an all-time high, most businesses rely on analyzing their data periodically to devise efficient strategies and stay on top of their game. One of the best ways to analyze huge amounts of data is to visualize it through graphical representation, such as charts. Charts are one of the most popular and efficient tools to represent data graphically and used widely to create dashboards.

With the Microsoft Excel 2016 release, seven new charts are introduced: Waterfall, Histogram, Pareto, Box and Whisker, Treemap, Sunburst, and Funnel chart, which can be used for financial and statistical analysis. All these charts are also supported by GrapeCity Documents for Excel library, referred to as GcExcel. Refer to this quick tutorial on how to Get Started with GcExcel.

In this article, we will implement the above-mentioned charts in a C# application using GcExcel to create a Sales Dashboard, which will be saved and viewed in Excel.

Try GcExcel free for 30 days

Download the latest version of GrapeCity Documents for Excel, .NET

Download Now!

Use Case: Sales Dashboard

Let’s assume that we have an Excel workbook containing the sales data of an e-commerce company. The workbook has seven worksheets containing sales data based on different parameters as shown below:

gif

This data will be used to create a Sales Dashboard where the Excel 2016 charts will be rendered using GcExcel, as shown below:

image1

Let’s move on to the next section to understand each chart, its usage, and its creation in detail.

Waterfall Chart

A waterfall chart can be used to visualize the cumulative effect of a series of positive and negative values. Along with the series of values, it displays the resultant value after calculation which helps to understand the outcome. The values are represented as color-coded columns so that the positive and negative values can be easily distinguished.

We will add a Waterfall chart in our dashboard to display the yearly profit of the company after taking into consideration various company costs. Here is a snapshot of the data:

image2

Here is the sample code for creating a Waterfall chart in a C# application using the GcExcel library:

public void Waterfall(Workbook workbook)
    {
      IWorksheet worksheet = workbook.Worksheets["Dashboard"];
      //Add Waterfall chart in Dashboard sheet
      IShape waterfallChartShape = worksheet.Shapes.AddChart(ChartType.Waterfall, 0, 28, 390, 225);
      //Add chart series and bind it to data
      waterfallChartShape.Chart.SeriesCollection.Add(workbook.Worksheets[0].Range["A4:B8"]);
      //Add chart title
      waterfallChartShape.Chart.ChartTitle.Text = "Profit Analysis";
      //Add axis title
      IAxis value_axis = waterfallChartShape.Chart.Axes.Item(AxisType.Value);
      value_axis.HasTitle = true;
      value_axis.AxisTitle.Text = "Cost (in $)";
    }

The image below depicts the Waterfall chart created using the above code:

image3

Box and Whisker Chart

Box and Whisker chart is used for statistical analysis. It displays the distribution of data using a five-way summary, namely, median, lower quartile, upper quartile, minimum, and maximum.

We will add a Box and Whisker chart in our dashboard to display the quarterly sales of different categories of products for the past four years. Here is a snapshot of the data:

image4

Here is the sample code for creating a Box and Whisker chart in a C# application using the GcExcel library:

public void BoxWhisker(Workbook workbook)
    {
      IWorksheet worksheet = workbook.Worksheets["Dashboard"];
      //Add BoxWhisker chart in Dashboard sheet
      IShape boxwhiskerChartShape = worksheet.Shapes.AddChart(ChartType.BoxWhisker,
           0, 253, 390, 225);
      //Add chart series and bind it to data
      boxwhiskerChartShape.Chart.SeriesCollection.Add(workbook.Worksheets[3].Range["A4:E20"]);
      //Add chart title
      boxwhiskerChartShape.Chart.ChartTitle.Text = "Sales by Quarters";
      //Add axis title
      IAxis value_axis = boxwhiskerChartShape.Chart.Axes.Item(AxisType.Value);
      value_axis.HasTitle = true;
      value_axis.AxisTitle.Text = "Sales (in $)";
    }

The image below depicts the Box and Whisker chart created using the above code. Color-coded box plots represent the product categories. Each box plot represents the five-way summary for the corresponding quarter across four years.

image5

Treemap Chart

A Treemap chart is used to display the data in a hierarchical manner where each item is represented by a rectangle, and a smaller rectangle represents each sub-item. The color and size of rectangles are correlated with the tree-like structure, making it easier to see the groups and spot patterns.

We will add a Treemap chart in our dashboard to display the region-wise sales data of different product categories. Here is a snapshot of the data:

image6

Here is the sample code for creating a Treemap chart in a C# application using the GcExcel library:

public void Treemap(Workbook workbook)
    {
      IWorksheet worksheet = workbook.Worksheets["Dashboard"];
      //Add Treemap chart in Dashboard sheet
      IShape treemapChartShape = worksheet.Shapes.AddChart(ChartType.Treemap,
              390, 28, 390, 225);
      //Add chart series and bind it to data
      treemapChartShape.Chart.SeriesCollection.Add(workbook.Worksheets[2].Range["A4:C15"]);
      //Add chart title
      treemapChartShape.Chart.ChartTitle.Text = "Sales by Region";
    }

The image below depicts the Treemap chart created using the above code:

image7

Sunburst Chart

A Sunburst chart is used to display data in a hierarchical manner where a ring or circle represents each level of the hierarchy. The innermost ring represents the highest-level hierarchy, whereas the outer rings represent the lower-level hierarchy.

We will add a Sunburst chart in our dashboard to display product sales region-wise and state-wise. Here is a snapshot of the data:

image8

Here is the sample code for creating a Sunburst chart in a C# application using the GcExcel library:

public void Sunburst(Workbook workbook)
    {   
      IWorksheet worksheet = workbook.Worksheets["Dashboard"];
      //Add Sunburst chart in Dashboard sheet
      IShape sunburstChartShape = worksheet.Shapes.AddChart(ChartType.Sunburst,
          390, 253, 390, 225);
      //Add chart series and bind it to data
      sunburstChartShape.Chart.SeriesCollection.Add(workbook.Worksheets[1].Range["A4:C16"]);
      //Add chart title
      sunburstChartShape.Chart.ChartTitle.Text = "Sales by Region and State";
    }

The image below depicts the Sunburst chart created using the above code:

image9

Histogram Chart

A histogram chart resembles a column chart and is used to display the frequency data, that is, the frequency of occurrence of a value in a distribution. The columns in the chart are called bins whose width, intervals, etc., are configured as required.

We will add a Histogram chart in our dashboard to display the frequency of the age of customers who had placed orders on the website in the past six months. Here is a partial snapshot of the (otherwise huge) data:

image10

Here is the sample code for creating a Histogram chart in a C# application using the GcExcel library:

public void Histogram(Workbook workbook)
    {
      IWorksheet worksheet = workbook.Worksheets["Dashboard"];
      //Add Histogram chart in Dashboard sheet
      IShape histogramChartShape = worksheet.Shapes.AddChart(ChartType.Histogram,
         780, 28, 374, 127);
      //Add chart series and bind it to data
      histogramChartShape.Chart.SeriesCollection.Add(workbook.Worksheets[4].Range["B4:B139"]);
      //Configure bins type and count
      histogramChartShape.Chart.ChartGroups[0].BinsType = BinsType.BinsTypeBinCount;
      histogramChartShape.Chart.ChartGroups[0].BinsCountValue = 5;
      //Add chart title
      histogramChartShape.Chart.ChartTitle.Text = "Sales by Customers' Age";
      //Add axis title
      IAxis value_axis = histogramChartShape.Chart.Axes.Item(AxisType.Value);
      value_axis.HasTitle = true;
      value_axis.AxisTitle.Text = "Frequency";
      IAxis category_axis = histogramChartShape.Chart.Axes.Item(AxisType.Category);
      category_axis.HasTitle = true;
      category_axis.AxisTitle.Text = "Age Group";
    }

The image below depicts the Histogram chart created using the above code:

image11

Pareto Chart

A Pareto Chart is used to display a histogram where the columns are sorted in descending order. A line is displayed at the top, representing the cumulative total percentage. In other words, the individual values are represented by bars, and the line represents the cumulative total.

Pareto charts are generally used to highlight the significant factors in a data set, and hence, we will use the Pareto chart in our dashboard to display the type of customer complaints and their frequency. Here is a snapshot of the data:

image12

Here is the sample code for creating a Pareto chart in a C# application using the GcExcel library:

public void Pareto(Workbook workbook)
  {
   IWorksheet worksheet = workbook.Worksheets["Dashboard"];
   //Add Pareto chart in Dashboard sheet
   IShape paretoChartShape = worksheet.Shapes.AddChart(ChartType.Pareto,
     780, 155, 374, 196);
   //Add chart series and bind it to data
   paretoChartShape.Chart.SeriesCollection.Add(workbook.Worksheets[5].Range["A4:B11"]);
   //Add chart title
   paretoChartShape.Chart.ChartTitle.Text = "Customer Complaints";
   //Add axis title
   IAxis value_axis = paretoChartShape.Chart.Axes.Item(AxisType.Value);
   value_axis.HasTitle = true;
   value_axis.AxisTitle.Text = "No. of People";
  }

The image below depicts the Pareto chart created using the above code:

image13

Funnel Chart

A funnel chart is used to visualize data in a process as it passes from one stage to another. In general, the data decreases with each subsequent stage, making the chart resemble a funnel shape. The example below would help to understand this better.

The e-commerce company hosts a festive marketing campaign under which the marketing emails are sent to potential buyers. The various stages in this marketing campaign are the users to whom these emails are sent, get delivered, are opened, leads to website visits, and purchase products.

We will display the sales conversion data using a Funnel chart in our dashboard. Here is a snapshot of the data:

image14

Here is the sample code for creating a Funnel chart in a C# application using the GcExcel library:

public void Funnel(Workbook workbook)
  {
   IWorksheet worksheet = workbook.Worksheets["Dashboard"];
   //Add Funnel chart in Dashboard sheet
   IShape funnelChartShape = worksheet.Shapes.AddChart(ChartType.Funnel, 780, 351, 374, 127);
   //Add chart series and bind it to data
   funnelChartShape.Chart.SeriesCollection.Add(workbook.Worksheets[6].Range["A4:B8"]);
   //Add chart title
   funnelChartShape.Chart.ChartTitle.Text = "Email Marketing Campaign Analysis";
  }

The image below depicts the Funnel chart created using the above code:

image15

The Excel 2016 charts are now added to the described Sales Dashboard, using the GcExcel library. You can also try this use case by downloading the sample, including all the code snippets described above.

Refer to the demos and documentation for more details. This concludes the multi-part series of creating Excel files with Excel 2016 features using GcExcel, to implement the real-world scenarios.

Try GcExcel free for 30 days

Download the latest version of GrapeCity Documents for Excel, .NET

Download Now!