Skip to main content


Adaptive Insights LLC.

Shared Formulas

Describes calculations that apply to more than one level (shared formulas). Includes information on how to add, override, or delete shared formulas.

You can define calculations that apply to more than one level, called shared formulas. To add and manage shared formulas, navigate to the Home > Formulas page. The shared formula page allows you to set shared formulas for an account across several levels. A shared formula it is applied across the entire time span of the version you select. A shared formula looks the same as other formulas - but the same formula exists in every time period. For example, you might define cash flow calculations as shared formulas that apply to all levels on both the planned and actuals versions. Shared formulas can be overridden by data entry on a sheet (unless the sheet is read-only).

Before You Begin

Permission Required: Model Management and Model.

Accounts Available For Shared Formulas

You can create shared formulas for GL and custom accounts. Rollup accounts and system accounts, while visible in the shared formula account selector, cannot be selected for shared formulas. Data entry is not allowed on rollup or system accounts. 

You cannot create shared formulas for Metric, Modeled, Cube and Assumption accounts. Formulas for Metric accounts are managed in account administration. Formulas for modeled and cube accounts are managed in sheet administration. Formulas for Assumption accounts are managed on sheets as a user edit. 

Levels Available For Shared Formulas

There are no restrictions on the levels where you can add shared formulas. Even when an account is hidden for a level, you can still select the level and add a formula. 

There is a limit to the number of shared formulas you can add in each version. The limit is 200,000 entered shared formulas at the Account/Level intersections and 10 million shared formulas across Account/Level/Time intersections. 

The Formulas Page

Controls on the Formulas page include:

  • Use the drop-down Accounts menu to find GL and custom accounts. You can navigate the Accounts structure or Search to find and select accounts.

  • Use the Levels list to find and select levels. You can navigate the list or search  for a level.

    • Click  sharedFormulaExpandTree.png  to globally expand (or collapse) the level list. 
    • These columns provide additional information about the selected account on each level:
      • Edits sharedFormulasUserEdits.png : A value, formula, or data import was entered on a sheet.

      • Hidden sharedFormulaAccountHiddenOnLevel.png : The account is hidden for this level.

      • Formula:  Formulas assigned to the account, level. 

  • Search: Shows filtered search results for the level.

  • Selected tab: Further isolate your focus to the selected levels. From here, you can manage formula inputs and updates to specifically-selected levels.  Level and account selections persist so you do not have to select the levels and account again when switching versions.

Add a Shared Formula 

To add a shared formula:

  1. Navigate to Formulas.

  2. Select a Version (upper right corner of the screen).

  3. Select an account from the Accounts list. 

  4. After picking an account to update, select the level or levels where you want to add the formula.   

You can select multiple levels at once, allowing bulk operations (For example, setting formulas, clearing edits, and deleting formulas).  Keyboard short-cuts (shift click, ctrl-click, and right-click), can be used for more efficient navigation and multi-level range selections in shared formulas. 

  1. After selecting the account and level, use Detail > Set Formula to enter a formula.

Type or copy the formula into Set Formula, or use the Formula Assistant. Creating or changing a shared formula for a level will not erase any user edits on the level which override the shared formula.

  1. For User edits, choose one of the following:

  • Preserve: Allow users to make changes to formulas on sheets.

  • Remove: Clears any edits to formulas when users save the sheet. 

  1. To cancel the edits to the formula and return it to its original state, click Cancel adminCancelDetailChange.png.

  2. To set the formula (including your User edits choices), click Save adminSaveDetail.png.

Override a Shared Formula 

There are a few options to override shared formulas as follows:

  • User Edit on Sheet: If the account is available for user edit, users can override the shared formula on the sheet.
  • Master Formula: Adding a master formula on an account overrides the shared formula. Refer to Master Formula under Account Details, Data Type for more information.

Delete a Shared Formula 

  • To delete a shared formula, select the level or levels and click Delete TrashIcon.png.

  • To clear any user edits which appear on a level (force the cells in that level to display the shared formula):

    • Choose User edits > Remove in the Details panel, then click Save adminSaveDetail.png.

    • Click the Remove user edits  sharedFormulasRemoveUserEdits.png  (top left of the screen).

Manage Versions and Levels for Formulas 

When a new sub-level is created, all the formulas from the parent level are copied into the new sub-level, except on locked versions. Similarly, Create New Version copies all formulas from the source version to the new version.

  • Was this article helpful?