Data Extraction

The Data Extraction feature provides real-time access to external data sources—such as ERP systems—allowing you to create live reports directly in Excel. It lets you select fields to extract, apply filters, set auto-refresh options, and build dynamic lists that stay in sync with your data.

When to use the Data Extraction

• Generating live tables in Excel: Insert real-time data into your sheet from external sources.
• Filtering and sorting data subsets: Apply conditions to pull only the data you need.
• Building dynamic lists: Keep reports updated automatically as new records appear in the source system.
• Auto-refreshing data on file open: Update your report automatically when the workbook is opened.

Create a data extraction

1. In the Add‑ins tab, select Data Extraction.
2. In the dialog, configure the Data Model and Environment.
3. Under Dimension Settings, add filters to narrow the data as needed.
4. Under Columns, select the fields to extract. Use the arrow buttons to set their order.
5. Adjust additional settings.
6. Select OK to generate the extraction table.
7. To filter values using a Reporting Tree node, select the reference cell from Reporting Tree in the Reporting Tree node field.
   Note: When you edit this field, the formulas are automatically refreshed.

Data Extraction properties

Property	Description
Data Model	Specifies the data model from which to extract data.
Environment	Specifies the environment for the data extraction.
Dimension Settings	Adds filters to narrow down the data. See Apply dimension filters for more information.
Columns	Lists the available fields that can be selected.
Selected Columns	Lists the fields to extract. Use the up and down arrow buttons to adjust their order.

Additional data extraction settings

Property	Description
Refresh Style	Defines how data is refreshed in the worksheet: Insert and Delete Cells – Default behavior. Inserts or deletes rows to match the result set while preserving surrounding content. Override Cells – Replaces all data in the output range, including any custom formatting or values. Override Cells the first time, Insert and Delete cells after – Initially overrides the range, then uses insert/delete behavior on subsequent refreshes. Useful for converting static reports. Insert Entire Rows – Adds new rows as needed without deleting existing ones.
Order By	Sorts the extracted records in ascending (ASC) or descending (DESC) order.
Top X	Limits the number of records displayed in the data extraction.
Distinct Record	Returns unique values only. The Order By field must be included in Selected Columns to enable this option.
Show Headers	Adds a header row above the extracted data.
Refresh on Workbook Open	Automatically refreshes the data when the workbook is opened.
Output Location	Defines where to place the extracted data: New Worksheet – Inserts data into a new sheet. Existing Worksheet – Inserts data at a specified starting cell.

Apply dimension filters

1. Select a dimension to filter results for the specified field and operation.
2. Choose one of the following filter types:
   • List – Include or exclude specific values. Wildcards are supported.
   • From–To – Define a numeric or string-based range.
   • Select All – Include all values in the dimension (default).
3. Use a prompt or cell reference to define filter values, either by typing them manually or linking to another cell.
4. Select OK to insert the formula. The complete formula appears in the Excel formula bar.

Use of wildcards for filtering

• ? replaces one character
• * replaces multiple characters

Wildcard example	Description
4000	Must match 4000.
4*	Must start with 4.
4???	Must start with 4 and contain exactly four characters.
4???-???-10	Must start with 4, followed by -, any three characters, another -, any three characters, and finish with 10 (total length: 11 characters).

Refresh a data extraction

The Data Extraction Refresh button, available in the Add-ins tab, updates all extractions in the current worksheet or workbook.

Use this feature after changing filters or settings to ensure your data reflects the latest available information.