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.
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:
This data will be used to create a Sales Dashboard where the Excel 2016 charts will be rendered using GcExcel, as shown below:
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:
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:
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:
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.
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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.
MESCIUS inc.