Formulas are the foundation of how data is extracted into Excel using the Datarails add-in. By placing a formula in the formula bar, you connect your Excel environment to your organization's core systems and retrieve real-time data for analysis, so you can build dynamic, up to date reports directly within Excel.

Formulas live under Get data > Formulas in the task pane.

Formula structure

To bring data into Excel, a formula consists of three main components.

DR formula - Initiates the connection to the database and specifies the type of data retrieval. It is placed at the beginning of the formula. Different DR formulas support various data structures and aggregation methods. 
See DR Formulas.

Function - Defines what data to extract, where it comes from, and how it should be calculated. For example, the Value function can sum amounts from a financial table, or retrieve specific values based on your criteria. See Working with functions below.

Conditions - The fields and values that customize your extraction, by specifying criteria such as timeframes, scenarios, account IDs or other data points to filter and tailor the extracted data. We call these conditions.

You can reference specific cells instead of typing values, which makes your formulas more dynamic, and you can drag a formula across rows or columns to adapt it efficiently. You can also use date ranges within your conditions for broader or more specific periods. 
See Date Ranges in Formulas.

Example formula

• DR formula (DR.GET) initiates data extraction and ensures all data from the database is accessible
• Function (Value) specifies the sum of the Amount field from the Financials table
• Conditions filter the data on specific criteria such as the reporting month and account group

Formula builder

The formula builder is an interactive tool that makes formula creation easier. You choose a function and drag in the relevant fields, so you do not have to type complex formulas by hand. The builder assembles an accurate formula for you with minimal effort.

Create a formula using the formula builder

1. Go to Get data > Formulas > Formula builder to open it.
2. Choose the function that matches your data needs. For example, select the Value function to retrieve numerical values from your dataset.
3. Add the conditions you need. For example:
   • Specify a time period, such as a reporting month
   • Add fields such as scenarios, accounts or departments to refine the extraction
4. Click the cell that matches the conditions you added.
5. Click Show cell formula to reveal the formula, or Copy to clipboard.
6. The formula builder generates the corresponding DR.GET formula. Copy it from the builder into your Excel cell.

building a formula end to end in the formula builder

Working with functions

A function specifies the value, the source table, and the type of calculation that defines the data you need. For example, the Value function sums the Amount field from the Financials table.

Create a new function

1. Select the cell in Excel where you want to add the function.
2. Go to Get data > Formulas > Manage functions, then click Add function.
3. Give the function a unique name. This name appears in the formula bar when the function is used.
4. In the function interface that opens:
   • Under Table, choose the table where your data resides from the dropdown. All its fields are then listed, and you can use the Search box to find one quickly.
   • Drag the relevant numeric field, for example Amount, into Value.
   • Optional: Drag fields into Filters, and apply criteria using the filter icon. Note: you won’t see which filters are applied in the formula bar.
   • Set a Default Aggregation Field, for example Reporting Month, to structure your data. Use Configure aggregation for more control over how it is aggregated.
5. Click Save at the top right. The function is added to the selected cell, and you can now use it within a formula.

the Add Function interface, with a table selected, a field in Values, and a filter applied

Edit the conditions on a formula

Edit conditions changes the conditions on the formula in the cell you have selected, not the function itself.

1. Click the cell containing the formula you want to change.
2. Go to Get data > Formulas > Edit conditions.
3. Each condition on the formula is listed. For any condition, use Choose from Excel to point it at a cell instead of a fixed value.
4. Click Add Condition to add another, hover on a condition and you’ll see edit/delete options are available as well.
5. Click Finish. The formula updates in the cell.

To change the function itself, its table, value or aggregation, use Manage functions below.

Functions are added to your file automatically

You do not need to add functions to a file by hand.

• When you connect a file to a Filebox, all the functions in that Filebox are added to the file.
• When you submit, any functions created since the last time are added too.

So a function a colleague created is available to you without anyone having to add it, and your file stays current with your organization's function list.

One consequence worth knowing. When a function is added to your file, the table it is based on comes with it. That means the filters library shows every table in your organization, not only the ones you have used in this file.

Manage functions

Manage functions is the central place for every function available to you. Open it from Get data > Formulas > Manage functions.

From here you can:

• Browse the full list, and use the search bar to find a specific function
• See the source table each function references
• Edit or delete a function
• Click Add function to create a new one

The same interface is available in the Datarails web application. Go to the Excel Add-in section in the left panel to manage your functions there.

Aspose functions

Datarails supports Aspose functions. The Aspose Cells formula calculation engine allows you to set, read and calculate results using a wide range of formulas and functions. See the Aspose documentation for the full list.

Working across platforms

Formulas behave identically on Windows desktop, Mac desktop and Excel on the web. A workbook built on one opens and recalculates the same way on the others.

Updated September 15, 2026 07:17