Excel User Interface

Last updated: 27 May 2025

ControlWORQ Excel User Interface¶

This topic describes some of the general interface conventions used in the ControlWORQ Excel add-in and the options that are available. The user interface is presented in the Excel ribbon:

ControlWORQ Connection¶

This section includes options to:

Connect/Disconnect: Connect to or disconnect from the ControlWORQ Server
Refresh: Refresh data from ControlWORQ, for example to update ControlWORQ Excel functions in the active spreadsheet
Settings: Edit connection settings to the ControlWORQ Server

Journal Management¶

This section includes options to:

Journal: Define journals that are in the active worksheet, mapping their details to the structure defined in ControlWORQ
Save/Approve/Post: Perform actions on one or more entries in the current worksheet, for example to Post or Repost entries

About/Help¶

Review version information and view ControlWORQ User documentation

Connecting to a ControlWORQ Server from Excel¶

Editing Connectivity Settings¶

If your ControlWORQ Server is running on a separate server, you may need to edit the URL path to that server. To do this, click on the Settings button and enter in the correct location of your server:

💡 Note: Your ControlWORQ Administrator can help you with the correct server location.

Connecting to a Planning Analytics Server¶

To access ControlWORQ, you can simply click the Connect button and the ControlWORQ Server will present you with a list of known Planning Analytics Servers (with the default server selected, if defined):

Simply enter your Planning Analytics credentials to connect to ControlWORQ. Once you have done this you will be able to view entry status information and even post entries to the ControlWORQ Server!

Defining Journal Entries in Excel¶

To edit or create new journal definitions in your Excel workbook, simply click on the "Journal Management" button and select the appropriate "Edit Entry..." or "New Entry..." option. Both options open up the same basic form, and the Edit version of that form is shown here:

Journal definitions are given a name that makes it easy to identify in the spreadsheet. Once the cube is selected, ControlWORQ can present you with the different settings that are required to define the entry based on data in the spreadsheet. You can manually enter named ranges, static values or cell references. As you type, the box on the right of that reference will reflect the value(s) that ControlWORQ will read based on that reference.

Ref edit controls can also be used to select ranges of elements in the worksheet by clicking on the button to the left of the cell reference to bring up the selector; when complete, click the button to store the selection:

In the example above, references have been made to cells on the worksheet. For all settings except "Line Dimensions", "Debits", "Credits" and "Comments" you can only specify one cell on the sheet. You may also reference cells on other worksheets in the same workbook, but not any cells outside of the current workbook.

You can Save the definition at any time. If you need to remove a definition, simply click the "Delete" button with that entry definition selected.

💡 Note: You are not required to lay out entries in any specific fashion - as long as you map the journal definition form to the data in your spreadsheet's journal information you will be able to post journal entries from Excel!

Named Ranges and Journal Definitions¶

You may find it convenient to use named ranges instead of raw cell references - ControlWORQ fully supports this and it is a good practice, in general, to avoid problems with mappings when cell references may have changed.

Reusing Session or Other Information¶

Note in the above example that there are two entries defined in the sheet, but Session dimension information is only specified once. To save time when creating multiple journal entry definitions you should click the Save As... button on an existing journal definition. This will make a copy of the same definition under a different name. You can further customize the entry definition from there.

Line Dimensions¶

Line Dimensions can either be defined as a single cell, or as a range of cells (one for each line item entry). If a single cell is defined it will be used for each line of the entry, which is driven by the Debit and Credit settings in the "Other Line Settings" section.

If a range of cells are defined for a line dimension then that range must match the same number of lines/cells as defined in the Debit and Credit settings in the "Other Line Settings" section.

The ability to specify a single cell for some line dimensions helps avoid unnecessary repetition of certain Line Dimensions for various Journal Entry scenarios, for example for a Partner Company dimension when posting a non-elimination journal entry.

Lines may be left blank in the definition of a journal entry, causing ControlWORQ to ignore them when submitting them to the ControlWORQ Server. For a line to be ignored the following must be true:

The debit and credit amounts on the line must both be zero or blank
At least one element referenced on that line must be blank

If both conditions are not met, ControlWORQ assumes that this is a valid journal entry line and will include it.

Rounding of Journal Entry Amounts¶

The Precision setting for a cube in ControlWORQ (default of 2 decimal places) is used to automatically round referenced Debit and Credit amounts from Excel before submitting them to the ControlWORQ Server. It is therefore not necessary to manually round any values in Excel to avoid ControlWORQ storing more than the desired number of decimal places, for example when calculating minority interest or similar entries.

Executing Journal Actions in Excel¶

As you save Journal Entry Definitions in Excel, the Journal Entry drop-down will populate itself with the available entries that you have defined. There are a number of ways that you can then Save, Approve, and Post those entries once defined.

Posting using Ribbon Buttons¶

Using the Journal drop-down you can select a single entry or the "" option and click one of the available buttons:

Save: Saves the entry or entries to ControlWORQ
Approve: Saves (if required) and Approves the entry or entries in ControlWORQ
Post: Saves (if required), Approves (if required) and Posts/Reposts the entry or entries in ControlWORQ

Posting using Batch Updates¶

There is another way to select a subset of entries, which is accessed via the "Batch Updates" button on the ribbon. This opens up a form with all of the entries defined in the current worksheet listed:

Use the checkboxes to select the entries that you want to post and click the appropriate action button to proceed.

💡 Tip: You may select and copy cells from this form into Excel, for example if you want to paste the results of a post operation for all journal entries in a selected worksheet.

Journal Action Status Results¶

Message boxes report the status of the selected actions when performing an action (e.g., Post) on a single entry. If you are operating on all entries in the worksheet or using the Batch Updates form, you will see the results of the operations in the "Batch Action Message" column of the form shown above.