Adding Formula Rows and Columns

Formula rows contain formulas that apply to data form rows. Formula columns contain formulas that apply to data form columns. For example, you can create a formula column (column D) that computes the percentage variance between the January sales (column A) and February sales (column B). The formula defined for a formula row or column applies to all row or column dimensions. You define a formula by selecting the formula row or column, and then enter the formula in the formula bar (see Creating Formulas Using the Formula Bar).

Note:

Add a formula row between two other rows to create a blank row. Blank rows are useful, for example, for visually separating subtotals and totals within a data form.

  To add formula rows and columns:

  1. Open the data form, and then click Layout.

    See Selecting and Opening Data Forms and Folders.

  2. In the Layout tab, right-click Rows or Columns.

  3. Select Insert Formula Row or Insert Formula Column.

  4. Click Formula Label in the formula row or column, and then enter the formula name.

    The formula bar is displayed at the top of the Layout tab.

  5. Click the formula row or column to display the formula row or column properties in the right pane, and then select or clear these properties:

    • Hide hides the row or column

    • Show separator displays the row or column separator

    • Display formula on data form displays the formula on the data form when you click Formula icon in the row or column header.

  6. For each dimension in Formula Data Type in the right pane, select a data type for the formula result.

    The data types are:

    • Currency

    • Non Currency

    • Percentage

    • SmartList

      If you select SmartList, select a Smart List from the drop-down list next to the data type.

    • Date

    • Text

  7. Enter the formula in the formula bar. See Editing Formulas Using the Formula Bar.

  8. Optional: In the formula bar, click Select to include one of the available mathematical functions in the formula. See Data Form Formula Functions.

  9. Click Validate icon to validate the formula.

    If the formula is valid, the flag at the right of the formula bar is green. The flag is red if the formula is not valid.

  10. Optional: If there are errors in the formula, click Formula invalid flag to display a tool tip that describes the errors. Fix the errors, and then click Validate icon to check your corrections.

    When the formula is valid, the flag at the right of the formula bar is green.

  11. Click Save to save your work and continue, or Finish to save your work and close the data form.