﻿ Set Table Formula | Documents for Excel, Java Edition Documentation
Documents for Excel, Java Edition Documentation
Features / Formulas / Set Table Formula
In This Topic
Set Table Formula
In This Topic

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.

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.

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 GcExcel 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.

GcExcel 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
#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
Copy Code
```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);