Calculate and sort table data

Tables don’t just present data; they also enable you to process data. For example, you can set up a table to add and multiply columns of numbers, or to calculate results using functions and formulas.

You can also set up table cells to change their formatting automatically when their values equal a value you specify.

Calculate cell values

You can set up cells that automatically add, multiply, and make other calculations.

  1. Select the table cell in which you want the result to appear, and choose Insert > Function > Show Function Browser.

    All of the available functions are displayed in the Function Browser.

    Click Recent at the top of the window to access any functions you recently used.

  2. Select a function in the right column.

    To filter the options, select a category in the left column.

  3. In the Formula Editor, replace each argument placeholder in the function with a cell or cell range and values.

    For example, the following formula contains the function SUM with a single argument (a range of cells) that adds the values in column A, rows 2 through 10:


  4. To adjust the range of cells included in the calculation, drag the cell selection handles.

  5. When you’re done, click the Accept button (with a checkmark) in the Formula Editor.

    If you click the Cancel button (with an X) in the Formula Editor, you exit the selected cell without applying the formula.

For more information about formulas and functions, see Formulas and Functions Help.

Sort table cell values

You can arrange values in some or all the cells in a column in ascending or descending order. Header cells aren’t sorted.

  1. Do one of the following:

    • Reorder all the cells in a column: Select a cell in the column.

    • Reorder specific cells: Select the cells.

  2. If the Table inspector isn’t open, open it, and click Table.

  3. Choose Sort Ascending or Sort Descending from the Edit Rows & Columns pop-up menu.

Set up conditional formatting rules

Conditional formatting changes a cell’s appearance when cells contain a test value, which can be either a value that you supply or a value that matches another value in a specific table cell.

To apply conditional formatting, you select one or more cells and then define one or more rules. The rules specify which visual effects to associate with cells when they contain the test value.

Rules applied to multiple cells trigger conditional formatting when any of the cells contains the test value.

  1. Do one of the following:

    • Apply the same rules to an entire table: Select the table.

    • Apply rules to specific cells: Select the cells.

  2. In the Table inspector, click Format and click Show Rules.

  3. In the Conditional Format window, choose an option from the pop-up menu.

  4. To specify a test value, do one of the following:

    • Specify a number or text: Enter it in the test value field to the right of the pop-up menu.

    • Specify the value in an existing table cell: Click the blue icon at the right side of the value field, and click the table cell that contains the test value you want.

    If you choose Between or “Not between” as the logical rule, you need to supply two cell locations.

  5. To specify the formatting to apply to cells that contain the test value, click Edit, use the style buttons, and click Done.

    The Sample box displays the effect of your selections.

  6. To add another rule, click the Add button and repeat steps 3 through 5.

  7. To modify a rule, do any of the following:

    • Delete a test value that refers to a table cell: Select a cell reference in the test value field and press Delete.

    • Replace a cell reference with a different one: Select the cell reference in the test value field and click the table cell containing the value you want.

    • Replace a text test value with a cell reference: Select the text in the test value field, click the small blue icon, and click a table cell.

    Tip: To find all the cells in a table that have the same conditional formatting rules as a particular cell, select the cell and click Select All.

If you define more than one rule for a cell and the cell’s value satisfies the conditions of multiple rules, the cell uses:

  • The text color for the topmost rule that has a text color specified.

  • The font style for the topmost rule that has a font style specified.

  • The cell fill color for the topmost rule that has a fill color specified.

If you add text to a cell with conditional formatting and change the new text’s color, the existing text retains the color you set in the rule.

Tip: To apply conditional formatting rules to cells in another table, select a cell whose rules you want to reuse, choose Edit > Copy, select one or more cells in a different table, and choose Edit > Paste.