# Tables and Table Formulas

> Insert, merge and edit tables in a Windows Forms TextControl, calculate cells with formulas in A1 or R1C1 style, catch FormulaException and set number formats.

- **Product:** TX Text Control .NET for Windows Forms
- **Section:** Documentation
- **Kind:** Topic
- **Path:** TX Text Control .NET for Windows Forms > Document Editor > Tables and Table Formulas
- **9349 words**
- **Web URL:** https://docs.textcontrol.com/tx-text-control-dotnet-winforms/document-editor/tables/
- **LLMs URL:** https://docs.textcontrol.com/tx-text-control-dotnet-winforms/document-editor/tables/llms.txt
- **LLMs-Full URL:** https://docs.textcontrol.com/tx-text-control-dotnet-winforms/document-editor/tables/llms-full.txt

---

Order line items, price lists and schedules are tables, and in a Windows Forms `TextControl` users build them the way they would in a word processor: they insert a table from the ribbon, add and merge rows and columns, set borders and colors, and let cells calculate totals with formulas that work like spreadsheet formulas. Your code does the same through the `Tables` collection, reacts to the table the user is working in and opens the built-in dialog boxes.

The examples are methods of *Form1.cs* in the application of [Create Your First Windows Forms Application](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/getting-started/document-editor/first-application/llms-full.txt), with a `TextControl` named `textControl1` and a ribbon. Call them from the `Click` handler of a button or a menu item, and keep the `namespace` that Visual Studio created around the class. Tables in documents that are built without a user interface, with `ServerTextControl`, are the subject of [Tables and Calculations](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/document-processing/tables/llms-full.txt) in Document Processing.

### How Tables Are Organized

[TextControl.Tables](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/textcontrol/tables-property/llms-full.txt) returns the [TableCollection](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tablecollection/llms-full.txt) of the text part with the input focus: the main text, or the header, footer or text frame the user is editing. [Document Object Model](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/document-processing/object-model/#the-input-focus-decides-what-a-collection-contains) explains this rule. A [Table](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/table/llms-full.txt) has [Rows](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/table/rows-property/llms-full.txt), [Columns](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/table/columns-property/llms-full.txt) and [Cells](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/table/cells-property/llms-full.txt), numbered from 1, and the formatting of a cell, a row or a column is a [TableCellFormat](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tablecellformat/llms-full.txt).

![Class diagram of the table classes: TextControl, WPF.TextControl and ServerTextControl lead to TableCollection, Table, the row, column and cell collections, TableCell, TableRow, TableColumn and TableCellFormat, with the version each member was introduced in.](https://docs.textcontrol.com/assets/img/tx-text-control-dotnet-winforms/tables-class-diagram.5760f9e2.webp)

Two things decide which table your code works with:

- **An ID.** A table that your code adds gets an ID between 10 and 32767 (`short.MaxValue`), and [TableBaseCollection.GetItem](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tablebasecollection/getitem-method/llms-full.txt) returns it by that ID. Tables that users insert or paste have no ID of their own.
- **The input position.** [TableCollection.GetItem](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tablecollection/getitem-method/llms-full.txt) without a parameter returns the table that contains the input position, or `null`. Methods such as `Rows.Add` work on the row, column or cell at the input position or on the selected cells.

Widths, heights, border widths and distances are measured in twips, 1/1440 inch.

### Work With Tables in the Ribbon

The **Insert** tab of the ribbon inserts a table at the input position: click **Table** in the **Tables** group and drag over the grid to choose the number of rows and columns, or click **Insert Table...** to enter them in a dialog box.

Everything else is on two tabs that work on the table at the input position:

| Tab | Class | Groups |
|---|---|---|
| **Table Layout** | [RibbonTableLayoutTab](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol-windows-forms-ribbon/ribbontablelayouttab/llms-full.txt) | **Table** (**Select**, **View Gridlines**, **Properties**), **Rows & Columns** (**Delete**, **Insert Above**, **Insert Below**, **Insert Left**, **Insert Right**), **Merge** (**Merge Cells**, **Split Cells**, **Split Table**), **Frames & Background**, **Alignment** and **Properties**, with the table's ID and descriptive text |
| **Formulas** | [RibbonFormulaTab](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol-windows-forms-ribbon/ribbonformulatab/llms-full.txt) | **Formulas**, **Number Format** and **Formula Settings**, described [below](#calculate-with-formulas) |

**Properties** opens the **Table Properties** dialog box with the tabs **Frame and Color**, **Size and Formatting** and **Formulas**:

![TX Text Control Words with the Table Layout tab of the Table Tools group and the Table Properties dialog box open on its Frame and Color tab, with frame presets, line width and color, background color and cell margins.](https://docs.textcontrol.com/assets/img/tx-text-control-dotnet-winforms/table-properties-dialog.b1f624b2.webp)

The two tabs are usually shown as contextual tabs that appear only while the input position is in a table, like **Table Tools** in the screenshot. [Create a Ribbon Application with Sidebar Support](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/getting-started/document-editor/ribbon-sidebar/#add-a-table-tools-tab) adds this group to a `RibbonForm`, and [Built-in Ribbon Tabs](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/document-editor/ribbon-ui/ribbon/#show-tabs-only-in-context) shows other ways to display them. The gridlines that **View Gridlines** shows or hides are [TableCollection.GridLines](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tablecollection/gridlines-property/llms-full.txt) in code; they mark cell borders on screen and aren't printed.

### Insert a Table From Code

The following method inserts the line item table of an order at the input position, with a heading row that is repeated on every page:

```csharp
using System;
using System.Windows.Forms;
using TXTextControl;

public partial class Form1 : Form
{
    private void InsertLineItemTable()
    {
        // No table can be added at the input position, or the text there is protected
        if (!textControl1.Tables.CanAdd || !textControl1.CanEdit)
        {
            return;
        }

        // A heading row and three item rows in three columns, with the ID 10
        textControl1.Tables.Add(4, 3, 10);
        Table table = textControl1.Tables.GetItem(10);

        string[] headings = { "Description", "Quantity", "Price" };

        for (int column = 1; column <= headings.Length; column++)
        {
            table.Cells.GetItem(1, column).Text = headings[column - 1];
        }

        // Repeat the first row at the top of each page
        table.Rows.GetItem(1).IsHeader = true;

        // The first column is 3 inches wide
        table.Columns.GetItem(1).Width = 3 * 1440;

        // The text screen readers and tagged PDF files use for the table
        table.DescriptiveText = "Line items of the order";
    }
}
```

The code works as follows:

- [TableCollection.CanAdd](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tablecollection/canadd-property/llms-full.txt) tells whether a table can be inserted at the input position. It doesn't check protected text fields and document sections; [CanEdit](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/textcontrol/canedit-property/llms-full.txt) does.
- [TableCollection.Add](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tablecollection/add-method/llms-full.txt) inserts a table with a number of rows and columns and an ID at the input position. Without parameters, `Add` opens the **Insert Table** dialog box instead.
- The cell texts, the header row that is repeated on each page and the column width work as in [Fill a Table From Data](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/document-processing/tables/#fill-a-table-from-data), which explains `Cells.GetItem`, `IsHeader` and `Width`.
- [Table.DescriptiveText](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/table/descriptivetext-property/llms-full.txt) is the alternative text of the table, which [Accessibility](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/integration/accessibility/llms-full.txt) describes.

Borders, background colors and text distances are properties of `TableCellFormat`, which you assign to a cell, a row or a column. [Format Cells, Rows and Columns](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/document-processing/tables/#format-cells-rows-and-columns) lists them, and the blog article [A Step-by-Step Guide to Formatting Table Cells in TX Text Control Programmatically Using C#](https://www.textcontrol.com/blog/2024/06/19/a-step-by-step-guide-to-formatting-table-cells-in-tx-text-control-programmatically-using-c-sharp/) goes through the attributes one by one.

### Work With the Table at the Input Position

Commands of your own, such as buttons in a custom toolbar, usually act on the table the user is working in. The following methods add a row below the current row, merge the first row into one cell and open the **Table Properties** dialog box:

```csharp
using System;
using System.Windows.Forms;
using TXTextControl;

public partial class Form1 : Form
{
    private void AddRowBelow()
    {
        // The table that contains the input position, or null
        Table table = textControl1.Tables.GetItem();

        if (table != null && table.Rows.CanAdd)
        {
            // One row after the row with the input position
            table.Rows.Add(TableAddPosition.After, 1);
        }
    }

    private void MergeFirstRow()
    {
        Table table = textControl1.Tables.GetItem();

        if (table == null)
        {
            return;
        }

        // Select row 1 from the first to the last column and merge it
        table.Select(1, 1, 1, table.Columns.Count);

        if (table.CanMergeCells)
        {
            table.MergeCells();
        }
    }

    private void ShowTableProperties()
    {
        // The dialog box works on the table at the input position
        if (textControl1.Tables.GetItem() != null)
        {
            textControl1.TableFormatDialog();
        }
    }
}
```

The code works as follows:

- `Tables.GetItem()` returns `null` when the input position isn't in a table, so each method checks it first. `table.Cells.GetItem()` without parameters returns the cell with the input position, whose [Row](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tablecell/row-property/llms-full.txt) and [Column](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tablecell/column-property/llms-full.txt) tell where the user is.
- [TableRowCollection.Add](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tablerowcollection/add-method/llms-full.txt) adds rows before or after the row with the input position, with `Before` or `After` from [TableAddPosition](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tableaddposition/enumeration/llms-full.txt). [CanAdd](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tablerowcollection/canadd-property/llms-full.txt) tells whether that is possible. Columns work the same way through `Columns`.
- [Table.Select](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/table/select-method/llms-full.txt) with a first and last row and column selects a range of cells. [MergeCells](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/table/mergecells-method/llms-full.txt) merges the selection if [CanMergeCells](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/table/canmergecells-property/llms-full.txt) is `true`, and [SplitCells](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/table/splitcells-method/llms-full.txt) splits merged cells again. [Split](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/table/split-method/llms-full.txt) divides the whole table in two.
- [TableFormatDialog](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/textcontrol/tableformatdialog-method/llms-full.txt) opens the **Table Properties** dialog box. An overload takes the zero-based index of the tab to show: 0 for frames and colors, 1 for size and alignment, 2 for formulas and number formats.

To move the input position into a cell from code, set [InputPosition](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/textcontrol/inputposition-property/llms-full.txt). [TableCell.Start](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tablecell/start-property/llms-full.txt) is one-based and the [InputPosition](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/inputposition/constructor/llms-full.txt) constructor takes a zero-based text position, so pass `cell.Start - 1`.

To enable such commands only while they can work, check `Tables.GetItem()` in a handler of the [InputPositionChanged](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/textcontrol/inputpositionchanged-event/llms-full.txt) event. [CanTableFormat](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/textcontrol/cantableformat-property/llms-full.txt) tells whether the selected text can get table formatting, which depends on the document's permissions and edit mode.

Tables arrive in the document in other ways too. [TableCreated](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/textcontrol/tablecreated-event/llms-full.txt) occurs when a table without an ID is pasted or loaded, and [TableFormatChanged](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/textcontrol/tableformatchanged-event/llms-full.txt) when the user changes the design of a selected table. Both pass a [TableEventArgs](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tableeventargs/llms-full.txt) with the table. [Events](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/document-editor/events/llms-full.txt) lists all events of the editor.

### Calculate With Formulas

A table cell can calculate its text from other cells of the same table with a formula, as in a spreadsheet. When the text of a referenced cell changes, the cell with the formula is updated. References to other tables aren't possible.

Users write formulas on the **Formulas** tab. They place the input position in a cell, choose a function from **Functions** and add it to the **Formula** box, type the cell range, for example `SUM(B1:B4)`, and confirm it with the check mark next to the box. **Select Cell References** picks the range with the mouse instead. The **Number Format** group sets how the result is displayed and whether the cell text is a number, and **Formula Settings** switches the calculation on and off, chooses the reference style and shows the references of a formula.

![TX Text Control Words with the Formulas tab of the Table Tools group: the formula SUM(R[-3]C[0]:R[-1]C[0]) in the Formula box, the number format $#,##0.00 and a cash flow table whose green rows are calculated.](https://docs.textcontrol.com/assets/img/tx-text-control-dotnet-winforms/formulas-tab-cash-flow.bfd1dc13.webp)

The following method fills the prices of the table from the first example and adds a row with their sum:

```csharp
using System;
using System.Windows.Forms;
using TXTextControl;

public partial class Form1 : Form
{
    private void AddTotal()
    {
        // Address cells with a column letter and a row number, such as C2
        textControl1.FormulaReferenceStyle = FormulaReferenceStyle.A1;
        textControl1.IsFormulaCalculationEnabled = true;

        Table table = textControl1.Tables.GetItem(10);

        if (table == null)
        {
            return;
        }

        // Numbers with two decimal places
        TableCellFormat amountFormat = new TableCellFormat()
        {
            TextType = TextType.Number,
            NumberFormat = "#,##0.00"
        };

        string[] prices = { "199.99", "99.98", "49.50" };

        for (int row = 2; row <= 4; row++)
        {
            TableCell price = table.Cells.GetItem(row, 3);
            price.CellFormat = amountFormat;
            price.Text = prices[row - 2];
        }

        // A row for the total below the last row
        table.Rows.GetItem(4).Select();
        table.Rows.Add(TableAddPosition.After, 1);

        table.Cells.GetItem(5, 1).Text = "Total";

        TableCell total = table.Cells.GetItem(5, 3);
        total.CellFormat = amountFormat;
        total.Formula = "SUM(C2:C4)";

        // Show cell references in the ruler bar while a formula cell is edited
        rulerBar1.FormulaMode = RulerBarFormulaMode.Auto;
    }
}
```

The code works as follows:

- [FormulaReferenceStyle](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/textcontrol/formulareferencestyle-property/llms-full.txt) sets how formulas address cells: `A1` with a column letter and a row number, `R1C1` with a row and a column number.
- [IsFormulaCalculationEnabled](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/textcontrol/isformulacalculationenabled-property/llms-full.txt) turns on the calculation that updates formula cells when an input cell changes.
- [TableCellFormat.TextType](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tablecellformat/texttype-property/llms-full.txt) with `Number` makes the text of a cell a number that formulas can calculate with. The text is read with the period as decimal separator and the comma as thousands separator, which is why the prices in the code are written `199.99`. [NumberFormat](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tablecellformat/numberformat-property/llms-full.txt) `#,##0.00` shows the result with a thousands separator and two decimal places.
- [TableCell.Formula](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tablecell/formula-property/llms-full.txt) sets the formula of the total cell.
- [RulerBar.FormulaMode](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/rulerbar/formulamode-property/llms-full.txt) `Auto` shows the cell references in the ruler bar while the input position is in a cell with a formula. `rulerBar1` is the horizontal ruler on your form, and [Rulers and Status Bar](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/document-editor/ribbon-ui/rulers-status-bar/llms-full.txt) describes the other modes.

Run the form, insert the table and add the total: the cell shows 349.47. Change a price, and the total follows. On screen, the separators are those of the system language, as [Number Formats](#number-formats) explains.

#### Check a Formula the User Enters

A formula with a syntax error throws a [FormulaException](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/formulaexception/llms-full.txt), whose [CharacterIndex](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/formulaexception/characterindex-property/llms-full.txt) is the one-based position of the first invalid character. An invalid number format throws a [NumberFormatException](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/numberformatexception/llms-full.txt) in the same way. When a text box of your own takes formulas, catch the exception:

```csharp
using System;
using System.Windows.Forms;
using TXTextControl;

public partial class Form1 : Form
{
    private void ApplyFormula(string formula)
    {
        Table table = textControl1.Tables.GetItem();
        TableCell cell = table?.Cells.GetItem();

        if (cell == null)
        {
            return;
        }

        try
        {
            cell.Formula = formula;
        }
        catch (FormulaException exc)
        {
            MessageBox.Show($"The formula is invalid at character {exc.CharacterIndex}.",
                "Formula Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }
}
```

The *Spreadsheet* how-to of the samples that the setup installs, in *Howto\\Spreadsheet*, builds a formula bar this way, with a function list, a formula box, the reference style and the number format above the editor; see [Document Editor Examples](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/examples/document-editor/llms-full.txt).

![The Spreadsheet how-to sample: a toolbar with the reference styles R1C1 and A1, Enable Calculation, the SUM function, the formula SUM(B4:B7), the cell text type and the number format above a cash flow report whose green rows are calculated.](https://docs.textcontrol.com/assets/img/tx-text-control-dotnet-winforms/spreadsheet-formulas-sample.431fd09e.webp)

The user selects a function from the function list and clicks **Add function**:

![The function list of the Spreadsheet how-to sample opened over the Cash Flow Report, with SUM selected among functions from ODD to TYPE.](https://docs.textcontrol.com/assets/img/tx-text-control-dotnet-winforms/formula-function-list.549e8c8f.webp)

The sample then asks for the cell range of the formula and passes the text the user typed to `Formula`, catching the exception as above:

![The formula box of the Spreadsheet how-to sample with SUM() and a Next Step tip asking for the cell range, for example SUM(A1:A5).](https://docs.textcontrol.com/assets/img/tx-text-control-dotnet-winforms/formula-cell-range-tip.63ad725f.webp)

Excel formulas in tables are part of the Enterprise and Professional editions; see [Editions and Feature Availability](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/overview/editions/llms-full.txt).

### Formula Syntax

Formulas follow the syntax of SpreadsheetML formulas in Office Open XML (ISO/IEC 29500-1, section 18.17.2), which spreadsheet applications such as Microsoft Excel use as well. A formula has four kinds of elements: constants, cell references and functions stand for values, and operators combine them. In the formula `PI()*R[-2]C[-1]^2`, written in R1C1 style:

![The formula PI()*R[-2]C[-1]^2 with its parts numbered: 1 the constant 2, 2 the operators ^ and *, 3 the function PI(), 4 the cell reference R[-2]C[-1].](https://docs.textcontrol.com/assets/img/tx-text-control-dotnet-winforms/formula-elements.b830642d.webp)

1. `2` is a numerical constant.
2. `^` raises the value of the referenced cell to the power of 2, and `*` multiplies the result by π.
3. `PI()` is a function that returns π, 3.14159265...
4. `R[-2]C[-1]` is a cell reference: the value of the cell two rows up and one column to the left of the formula cell.

#### Constants

A constant is a value written into the formula itself. Formulas know five kinds: numerical, logical, string, error and array constants.

| Constant | Written as | Formula | Result |
|---|---|---|---|
| Integer | `4` | `9+7` | 16 |
| Decimal number, with a period | `3.937` | `4*5.5` | 22 |
| Engineering notation | `5e4` | `4*3e5` | 1200000 |
| Number with a leading minus | `-7.6` | `-9+7` | -2 |
| Logical | `TRUE`, `FALSE` | `15>7=TRUE` | TRUE |
| Logical | `TRUE`, `FALSE` | `15>7=FALSE` | FALSE |
| String, in double quotes | `"Text"` | `"TX"&"Text"&"Control"` | TXTextControl |
| Error | `#N/A` | `NA()` returns the same value | \#N/A |
| Array, in braces | `{1,2,3;4,5}` | `SUM(10,{1,2,3;4,5})` | 25 |

An array constant is a matrix of numbers, logical values and strings. Commas separate the values of a row and semicolons separate the rows, so `{1;2;3}` is a column of three values. Strings can be compared: `"Two"="One"` returns FALSE, and `"FALSE"="FALSE"` returns TRUE.

#### Operators

Arithmetic operators return a number, comparison operators return `TRUE` or `FALSE`, and the concatenation operator joins strings:

| Operator | Meaning | Formula | Result |
|---|---|---|---|
| `+` | Addition | `8+6` | 14 |
| `-` | Subtraction | `14-6` | 8 |
| `*` | Multiplication | `4*3` | 12 |
| `/` | Division | `17/2` | 8.5 |
| `%` | Percent, after a number | `290%` | 2.9 |
| `^` | Exponentiation | `3^2` | 9 |
| `=` | Equal to | `2=2` | TRUE |
| `>` | Greater than | `4>6` | FALSE |
| `<` | Less than | `4<6` | TRUE |
| `>=` | Greater than or equal to | `6>=4` | TRUE |
| `<=` | Less than or equal to | `26<=4` | FALSE |
| `<>` | Not equal to | `2<>2` | FALSE |
| `&` | Concatenation | `"TX"&"Spell"` | TXSpell |

#### Functions

A function starts with its name in uppercase letters, followed by its arguments in parentheses, separated by commas. The parentheses are there even when the function takes no argument, as in `PI()`:

![The function POWER(1.234,5.1) with its function name, its two arguments and the parenthesis area labeled.](https://docs.textcontrol.com/assets/img/tx-text-control-dotnet-winforms/formula-function-parts.6541df41.webp)

An argument is a number, a logical value, a string or an array. It can be written as a constant, taken from a cell reference or a range, or be the result of another function. A function returns a number, a logical value, a string or an array of them.

| Formula | Result |
|---|---|
| `ABS(-7.765)` | 7.765 |
| `PI()` | 3.141592654 |
| `POWER(3,2.5)` | 15.5884573 |
| `SUM(1,2,3)` | 6 |
| `SUM({1;2;3})` | 6 |
| `SUM(A1:A3)` | 6, when cells A1, A2 and A3 hold 1, 2 and 3 |

Formulas can use 85 mathematical and trigonometric, statistical, logical, lookup and reference and information functions. [Formula Functions](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/formula-functions/llms-full.txt) lists each one with its arguments and defaults, the limits of functions such as `COUNT`, which doesn't count dates, and the Excel functions of loaded worksheets. [TableCollection.SupportedFormulaFunctions](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tablecollection/supportedformulafunctions-property/llms-full.txt) returns their names at runtime.

#### Cell References

`FormulaReferenceStyle` decides whether a formula refers to other cells of its table in R1C1 style or in A1 style.

**R1C1 style.** `R` with a row number and `C` with a column number address a cell. A number in square brackets is relative: it counts the rows and columns from the formula cell, with negative numbers going up and to the left and `[0]` staying in the same row or column. Text Control recommends relative references. For a formula in row 4 and column 3, the references below point at these cells of the table:

![A table of five rows and four columns, labeled R1 to R5 and C1 to C4, with a formula with a cell reference in the cell at R4 and C3 and numbers in the other cells.](https://docs.textcontrol.com/assets/img/tx-text-control-dotnet-winforms/formula-r1c1-table.44608763.webp)

| Relative reference | Refers to | Value in the picture |
|---|---|---|
| `R[-3]C[-1]` | Row 1, column 2 (R1C2) | 3 |
| `R[1]C[-2]` | Row 5, column 1 (R5C1) | 14 |
| `R[0]C[1]` | Row 4, column 4 (R4C4) | -5 |
| `R[-2]C[1]` | Row 2, column 4 (R2C4) | 5 |

A number without brackets is absolute: `R5C4` is the cell in row 5 and column 4, wherever the formula is. Absolute references work, but when the table is edited or its cells are pasted as a new table, they return wrong or invalid values. Use relative references in tables that users edit.

**A1 style.** A column letter and a row number address a cell. The columns are named A to Z, then AA to AZ, BA to BZ and so on, so `B3` is the cell in the second column of the third row. A plain A1 reference is relative, the same as a relative R1C1 reference. A `$` before the column, the row or both makes that part absolute: `$B$3`, `$B3` or `B$3`.

**Ranges.** Two references separated by a colon form a range from the upper left to the lower right cell, which is how a function such as `SUM` takes many cells at once. In a formula in row 3 of column 3, the ranges below add up the numbered areas:

![A table of five rows and five columns with four marked areas: 1 SUM(R[-1]C[-2]:R[0]C[-1]), 2 SUM(R[-2]C[0]:R[-1]C[0]), 3 SUM(R[-2]C[2]:R[1]C[2]) and 4 SUM(R[2]C[-1]:R[2]C[1]).](https://docs.textcontrol.com/assets/img/tx-text-control-dotnet-winforms/formula-cell-ranges.38fceb7e.webp)

| Formula | Adds up | Result in the picture |
|---|---|---|
| `SUM(R[-1]C[-2]:R[0]C[-1])` | R2C1 to R3C2 | 16 |
| `SUM(R[-2]C[0]:R[-1]C[0])` | R1C3 to R2C3 | 14 |
| `SUM(R[-2]C[2]:R[1]C[2])` | R1C5 to R4C5 | 20 |
| `SUM(R[2]C[-1]:R[2]C[1])` | R5C2 to R5C4 | 19 |

In A1 style, the first range is `SUM(A2:B3)`.

**LEFT, ABOVE, RIGHT and BELOW** stand for all cells next to the formula cell in one direction:

![A table with a formula cell SUM(...) in row 3, column 3 and the four areas that LEFT, ABOVE, RIGHT and BELOW refer to, numbered 1 to 4.](https://docs.textcontrol.com/assets/img/tx-text-control-dotnet-winforms/formula-left-above-right.dc8142f4.webp)

| Formula | Adds up | Result in the picture |
|---|---|---|
| `SUM(LEFT)` | The cells to the left of the formula cell | 5 |
| `SUM(ABOVE)` | The cells above it | 14 |
| `SUM(RIGHT)` | The cells to the right of it | 8 |
| `SUM(BELOW)` | The cells below it | 15 |

### Number Formats

A number format decides how the number of a cell is displayed, whether the user typed it, your code set it or a formula calculated it. It applies when `TableCellFormat.TextType` is `Number`, and `TableCellFormat.NumberFormat` holds the format string. Users choose one in the **Number Format** group of the **Formulas** tab.

Without a format, `NumberFormat` is an empty string and a number is shown as it is: a whole number without decimal places and a decimal number with all of its decimal places. A format string is made of the placeholders `#` and `0`, the separators `.` and `,`, the percent sign `%` and other characters or text, as described in Office Open XML (ISO/IEC 29500-1, section 18.8.31). [TableCollection.SupportedNumberFormats](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tablecollection/supportednumberformats-property/llms-full.txt) returns a list of common formats, the same list that the **Number Format** box offers.

#### Decimal and Thousands Separators

In the text of a cell whose type is `Number`, and in a format string, the period is the decimal separator and the comma the thousands separator, whatever the language of the system. The number on screen uses the separators of the system language instead: on a German system, the decimal separator is displayed as a comma and the thousands separator as a period. Your code writes `12.5` and `#,##0.00`, and a German user sees `12,50`.

#### Placeholders and Decimal Places

`#` and `0` stand for the digits of the number. `#` shows only the digits the number has, and `0` shows a zero where the number has no digit:

| Number | Format | Displayed |
|---|---|---|
| 7 | `#` | 7 |
| 1234 | `#` | 1234 |
| 7 | `000` | 007 |
| 789 | `000` | 789 |

A period after the placeholders shows decimal places. The number of placeholders after it is the maximum number of decimal places, and a `0` there shows the place even when it is zero. Without a period, the number is rounded to an integer:

| Number | Format | Displayed |
|---|---|---|
| 12.6 | `#.##` | 12.6 |
| 5 | `#.00` | 5.00 |
| 12.6 | `#.00` | 12.60 |
| 0.543 | `#.00` | 0.54 |
| 17.548 | `#` | 18 |

#### Thousands Separator and Scaling

A comma with placeholders after it inserts a thousands separator after every third digit. A comma with no placeholder after it divides the number by 1,000 instead, once for each such comma, so that large amounts are shown in thousands or millions:

| Number | Format | Displayed |
|---|---|---|
| 9876543210.1234 | `,###.##` | 9,876,543,210.12 |
| 9876543210 | `,###` | 9,876,543,210 |
| 12200000 | `#,,` | 12 |
| 12200000 | `#.0,,` | 12.2 |

#### Percentages

A `%` together with one or more placeholders multiplies the number by 100 and shows the percent sign where it stands in the format, before or after the digits:

| Number | Format | Displayed |
|---|---|---|
| 0.52 | `#%` | 52% |
| 1 | `#%` | 100% |
| 0.00321 | `0.##%` | 0.32% |
| 0.52 | `%#` | %52 |

#### Symbols and Text

A number can be combined with characters and text in three ways:

- **Characters written as they are:** `$`, `-`, `+`, `/`, `(`, `)`, `:`, `!`, `^`, `&`, `'`, `~`, `{`, `}`, `<`, `>`, `=` and the space.
- **Any other single character after a backslash,** such as a currency symbol, a letter, a double quote or a digit that isn't a placeholder, as in `\3`.
- **Words and longer text in double quotes.**

| Number | Format | Displayed |
|---|---|---|
| 500 | `#$` | 500$ |
| 1 | `-#.00` | -1.00 |
| 12.345 | `+.##` | +12.35 |
| 4 | `\1/#` | 1/4 |
| 7 | `-(#+\3)` | -(7+3) |
| 3 | `-(\7+#)` | -(7+3) |
| 50 | `#:"50"` | 50:50 |
| 6 | `#!` | 6! |
| 2.5 | `#.00^\2` | 2.50^2 |
| 0 | `#&\1` | 0&1 |
| 9 | `'#'` | '9' |
| 7.654 | `~#.0` | ~7.7 |
| 67 | `{-#}` | {-67} |
| 17 | `{+#}` | {+17} |
| 5 | `#<"10"` | 5<10 |
| 7.4 | `#.00>\6` | 7.40>6 |
| 5.6 | `#>=\6` | 6>=6 |
| 12 | `#.00 $` | 12.00 $ |
| 12.99 | `#.00\€` | 12.99€ |
| 42 | `\"#\"` | "42" |
| 18 | `#:\X` | 18:X |
| 29.99 | `"EUR"#.00` | EUR 29.99 |
| 4.50 | `#.00"€"` | 4.50€ |
| 76 | `# "Units"` | 76 Units |

An invalid format string throws a `NumberFormatException`, which [Calculate With Formulas](#calculate-with-formulas) introduces together with `FormulaException`. The blog article [Formatting Numbers in Table Cells](https://www.textcontrol.com/blog/2022/06/21/formatting-numbers-in-table-cells/) shows number formats while the user types.

### Remove a Table

[TableBaseCollection.Remove](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tablebasecollection/remove-method/llms-full.txt) removes the table with an ID, and [TableCollection.Remove](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol/tablecollection/remove-method/llms-full.txt) without a parameter the table at the input position. In the ribbon, **Delete** on the **Table Layout** tab offers **Delete Cells**, **Delete Columns**, **Delete Rows** and **Delete Table**.

### Next Steps

- [Text and Paragraph Formatting](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/document-editor/formatting/llms-full.txt): format the text inside table cells.
- [Merge Blocks](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/mail-merge/blocks/llms-full.txt): repeat a table row for each record in a template.
- [Sections and Page Layout](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/document-editor/sections/llms-full.txt): give a wide table a page in landscape orientation.
- [Tables and Calculations](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/document-processing/tables/llms-full.txt): create and read tables with `ServerTextControl`.

---

## Nearby

- **Previous:** [Bullets and Numbered Lists](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/document-editor/lists/llms-full.txt)
- **Next:** [Images](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/document-editor/images/llms-full.txt)