# 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 a worksheet instead of an explicit cell reference.
While creating a table formula, users must apply structured reference (the combination of table and column names in a spreadsheet) along with the syntax rules.
For example, let's refer to the table formula in a worksheet as shown below.

![Table Formula](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/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/java-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 Java, you can use reference operators in order to combine column specifiers in a table formula.
Refer to the following 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 Java 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.

```Java
Object[][] data = new Object[][] 
{ 
        { "SalesPerson", "Region", "SalesAmount", "ComPct", "ComAmt" },
        { "Joe", "North", 260, 0.10, null }, 
        { "Robert", "South", 660, 0.15, null }, 
};
worksheet.getRange("A1:E3").setValue(data);
worksheet.getTables().add(worksheet.getRange("A1:E3"), true);
worksheet.getTables().get(0).setName("DeptSales");
worksheet.getTables().get(0).getColumns().get("ComPct").getDataBodyRange().setNumberFormat("0%");

// Use table formula in table range.
worksheet.getTables().get(0).getColumns().get("ComAmt").getDataBodyRange().setFormula("=[@ComPct]*[@SalesAmount]");

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