How to Programmatically Add Calculated Columns to C# .NET Excel XLSX
Tags: webdev, devops, csharp, tutorial
Published: 2023-07-26 19:16:22

---
canonical_url: https://developer.mescius.com/blogs/how-to-programmatically-add-calculate-columns-to-c-sharp-net-excel-xlsx
description: Learn how to programmatically add calculated columns to C# .NET Excel XLSX documents. See more from GrapeCity today.
---
Often data from data sources are not processed and thus do not provide the immediate information business users or analysts are looking for. To derive meaningful insights from the unprocessed data, it is required to perform calculations on the available data. For example, calculating profit/loss, customer retention rate, financial ratios, and many others.

In Microsoft Excel, one of the ways to perform such calculations is using the **Calculated Columns** in Tables. Calculated columns help to enter and maintain formulas in Excel tables.

## Calculated Columns

A Calculated Column in Excel Table contains a single formula in any row of the column that adjusts for each row and automatically expands to include additional rows in that column. Thus, the formula is immediately and automatically extended to all the rows in a Table without using the **Fill** or **Copy** command.

In this blog, we'll see how to programmatically add Calculated Columns to Excel Tables using GcExcel API for .NET.

## Use-case

For every business, the sales data usually contains information directly related to sales, such as unit production & selling cost, quantity produced & sold, buyer & seller details, and so on. However, to analyze the sales situations, business users want calculated information such as the total sales amount, profit/loss, sales margin, and several others.

Let’s see how we can programmatically calculate sales amount and profit/loss in figure & percent in an Excel table as shown below:

![C# Excel Calculations](https://files.grapecity.com/gc-website-media/2xhpii2e/01-columns.png?width=500&height=104.84300159659394)

## Add Calculated Column to Excel Table

Adding a calculated column in Excel Table using GcExcel API for .NET involves the following steps:

### 1\. Create a workbook instance and open the Excel file.

```
    Workbook workbook = new Workbook();
    workbook.Open("ProductSales.xlsx");
```

### 2\. Access the table and add new columns to store the calculated data.

```
    ITable productTable = workbook.Worksheets["data"].Tables["ProductTable"];
    productTable.Columns.Add(5).Name = "Sales Amount";
    productTable.Columns.Add(6).Name = "Profit/Loss";
    productTable.Columns.Add(7).Name = "Profit/Loss Margin";
```

### 3\. Iterate through each row and use [IRange.Formula](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/GrapeCity.Documents.Excel~GrapeCity.Documents.Excel.IRange~Formula.html) to add calculation using structured reference formula as depicted in the code snippet below.

```
    foreach (ITableRow row in productTable.Rows)
      {
          row.Range[5].Formula = "=ProductTable[@QtySold]*ProductTable[@SellingPrice]";
          row.Range[6].Formula = "=ProductTable[@[Sales Amount]] - ProductTable[@QtySold]*ProductTable[@ProductionCost]";
          row.Range[7].Formula = "=(ProductTable[@[Profit/Loss]]/ProductTable[@[Sales Amount]])";
      }
```

### 4\. Format the columns and save the workbook.

```
    productTable.Columns[7].DataBodyRange.NumberFormat = "0.0%";
    workbook.Worksheets["data"].Columns.AutoFit();

    workbook.Save("ProductSales_Updated.xlsx");
```

Download the [sample](/{localLink:umb://media/59643f5a19a54b8197c92fc0d040571a} "CalculatedColumn_DOCS_07/12/23").

Check out the [GcExcel Online Demos](https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/tables) to learn more about the programmatic approach of working with Excel Tables in .NET.