Formula Builder

Home/Formula Builder

Formula Builder

The Formula Builder tool provides several available PlaidXL functions. The Formula Builder is extremely useful because it allows the creation of PlaidXL formulas through a point-and-click interface. See Model Builder Functions for more description.

Important Vocabulary #

  • Source cell: This shows the Excel cell or cell range. You can either type it, or use the source cell icon button to select the cell or range.
  • Value: Shows the value that represents the current cell. Type the values between double quotes to define a constant (value not presented in any cell).
  • Cell anchoring: PlaidXL is smart enough to guess if a range needs anchoring in the column or in the row. If you would like to change this, simply click on the fixed row or column checkbox.

Choosing a Destination #

To place a formula in a specific cell:

  1. Click “Formula Builder”
  2. The current selected cell will appear in the box next to “Configure Formula for”
  3. To change the selected cell destination, select this button:  change destination icon

Functions and Formulas #

PlaidXL allows you to either select a function or build your own formula.

Functions #

To view functions:

  1. Click the drop down menu
  2. Select the desired function

Formulas #

A formula is built by selecting parameters or by manually typing it into the formula box.

To select parameters:

  1. Select a cell by typing it in the “Source Cell” column by clicking the brackets button and then selecting a cell
  2. If a parameter does not exist within a cell, go to the “Value” column
  3. In the “Value” column manually type the desired parameter

Note: Be sure to type the parameter between double quotes (“Parameter”)

Anchoring Rows and Columns #

Just like native excel functions, PlaidXL allows you to anchor rows and columns by putting a ‘$’ ahead of the desired anchor. PlaidXL can predict the anchor based on the report section, but this can be changed by the user. See PlaidXL Report Design for more information.

Remember these rules while anchoring:

  • When a cell belongs to “Page Section,” fix both the row and column ($B$3)
  • When a cell belongs to “Column Section,” fix just the row (E$7)
  • When a cell belongs to “Row Section,” fix just the column ($B8)
  • If you would like to change the anchoring, you can either type it into the “Source Cell” column or the “Row & Column Locking” depending on your preference.

Excel Calculations #

Excel Calculation buttons are shortcuts for the native Excel features.

  • “Calculate Automatically” is the same as “Formulas / Calculation Options / Automatic” in Excel.
  • “Calculate Manually” is the same as “Formulas / Calculation Options / Manual” in Excel.
  • “Calculate Now” is the same as “Formulas / Calculate Now” or F9 in Excel.

Fields With Default Values #

Some fields within PlaidXL have a default value.

Fields with default values:

  • Currencies which default to “NON” when the selected cell is empty.

Fix Formula #

“Fix Formula” corrects the PlaidXL location path. This may be required when a workbook containing PlaidXL functions is shared between users. PlaidXL automatically tries to adjust the local user path to PlaidXL after the workbook has opened, but in some rare cases, it may not function properly. In those cases, execute the process manually by pressing “Fix Formula”.

Note: Fix Formula runs on all Excel tabs

Remove PlaidXL Function #

When a PlaidXL workbook is shared with other users, make sure they have PlaidXL installed. They will not be able to see the report properly if PlaidXL is not installed. PlaidXL adds formulas to the standard Excel formula library, which will not exist for users without PlaidXL, causing reference errors in the worksheet.

If a user receives a PlaidXL report and does not need to update it, then the “Remove PlaidXL Function” tool comes in handy. This provides a convenient way to distribute PlaidXL reports to others without requiring all recipients to have PlaidXL installed. This process removes all the PlaidXL formulas and replaces the cell contents with the value instead. All native Excel formulas are retained, and only the PlaidXL-based formulas are replaced with values.

Note: Use “Remove PlaidXL Functions” to distribute PlaidXL reports to people who do not have PlaidXL installed.

 

Go to Top