# Set Table Formula

This topic describes the reference operators and special item specifiers needed to set a table formula using DsExcel.

## Content

Table formula refers to a formula that is used as a structured reference in the worksheet instead of using it as an explicit cell reference. Structured reference in a table formula is the combination of table and column names in a spreadsheet with syntax rules that must be applied while creating a table formula.
For instance, let us consider an example of a table formula in a spreadsheet.
![Table Formula](https://cdn.mescius.io/document-site-files/images/e333ad47-35da-43f9-a389-25433765f491/images/tableformula.png)
The structured reference components in the above table formula are described below.

| **Components** | **Description** |
| ---------- | ----------- |
| Table Name | References the table data, without any header or total rows. You can use a default table name, such as Table1, or change it to use a custom name. Example: DeptSales is a custom table name in the table formula. For more information on how to add custom names, see [Defined Names](/document-solutions/dot-net-excel-api/docs/online/Features/DefinedNames). |
| Column Specifier | Column specifiers use the names of the columns they represent. They reference column data without any column header or total row. Column specifiers must be enclosed in [] square brackets when they are written in the table formula. Example: [SalesAmount] and [ComAmt] |
| Item Specifier | Refers to a specific portions of the table such as total row. Example: [#Totals] and [#Data] |
| Table Specifier | Represents the outer portions of the structured reference. Outer references follow table names and are enclosed within the square brackets. Example: [[#Totals],[SalesAmount]],[[#Data],[ComAmt]] |
| Structures Reference | Represented by a string that begins with the table name and ends with the column specifier. Example: DeptSales[[#Totals],[SalesAmount]] and DeptSales[[#Data],[ComAmt]] |

## Reference operators

In DsExcel .NET, reference operators are used to combine column specifiers in a table formula.
Shared below is a table that describes the reference operators along with structured reference components and cell range corresponding to the table formula.

| **Operators** | **Description** | **Example** |
| --------- | ----------- | ------- |
| :(colon) range operator | All of the cells in two or more adjacent columns. | =DeptSales[[SalesPerson]:[Region]] |
| ,(comma) union operator | A combination of two or more columns. | =DeptSales[SalesAmount],DeptSales[ComAmt] |
| (space) intersection operator | The intersection of two or more columns. | =DeptSales[[SalesPerson]:[SalesAmount]]DeptSales[[Region]:[ComPct]] |

## Special item specifier

Special item specifier refers to a particular area in a table formula which is identified either with a # prefix or with an @ prefix.
DsExcel .NET supports the following types of special item specifiers:

| **Special Item Specifier** | **Description** |
| ---------------------- | ----------- |
| #All | To the entire table including column headers, data and totals (if any). |
| #Data | Only the data rows |
| #Headers | Only the header rows |
| #Totals | Only the total row. If there is none, it returns null. |
| #This Row | Cells in the same row as the formula |
| @ | Cells in the same row as the formula |

Refer to the following example code to set table formula in your spreadsheets.

```csharp
// Define Data
worksheet.Range["A1:E3"].Value = new object[,]
    {
        {"SalesPerson", "Region",  "SalesAmount", "ComPct", "ComAmt"},
        {"Joe", "North", 260, 0.10, null},
        {"Robert", "South", 660, 0.15, null},
    };

worksheet.Tables.Add(worksheet.Range["A1:E3"], true);
worksheet.Tables[0].Name = "DeptSales";
worksheet.Tables[0].Columns["ComPct"].DataBodyRange.NumberFormat = "0%";

//Use table formula in table range.
worksheet.Tables[0].Columns["ComAmt"].DataBodyRange.Formula = "=[@ComPct]*[@SalesAmount]";

//Use table formula out of table range.
worksheet.Range["F2"].Formula = "=SUM(DeptSales[@SalesAmount])";
worksheet.Range["G2"].Formula = "=SUM(DeptSales[[#Data],[SalesAmount]])";
worksheet.Range["H2"].Formula = "=SUM(DeptSales[SalesAmount])";
worksheet.Range["I2"].Formula = "=SUM(DeptSales[@ComPct], DeptSales[@ComAmt])";
```