Data Extraction

Data Extraction connects to an external data source, such as an ERP system, and writes the results as a table directly in your Excel file. You pick the fields, apply filters to narrow the results, and set a refresh schedule. The table updates automatically as the source data changes.

Unlike NecAccess formula, which returns a single calculated value, a data extraction returns a full set of records, such as a transaction log, a product list, or any structured dataset you want to work with directly in Excel.

When to extract data

• Generate live tables in Excel from external systems that update as source data changes.
• Filter and sorting data subsets to retrieve only the records your report needs.
• Build dynamic lists that stay current as new records appear in the source system.
• Refresh data automatically each time the Excel file 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 and use the arrow buttons to adjust their order.
5. Configure any additional settings.
6. Select Ok to generate the extraction table.
7. To filter values using a Reporting Tree node, select the reference cell from the Reporting Tree in the Reporting Tree node field.

note

Editing the Reporting Tree node triggers an automatic refresh of all associated formulas.

Data extraction properties

Property	What it does
Data Model	The data model to extract from.
Environment	The environment for the extraction.
Dimension Settings	Adds filters to narrow the data.
Columns	The available fields you can extract.
Selected Columns	The fields to extract, arranged using the arrow buttons.

Additional data extraction settings

Setting	What it does
Refresh Style	Controls how the sheet updates during refresh: Insert and Delete Cells – (Default) insert or delete rows to match the result set while preserving surrounding content. Override Cells – replace all data in the output range, including custom formatting or values. Override Cells first time, Insert and Delete Cells after – override the range on the first refresh, then switch to insert and delete behavior on later refreshes. Insert Entire Rows – add new rows as needed without deleting existing ones.
Order By	Sort extracted records in ascending (ASC) or descending (DESC) order.
Top X	Limit the number of records returned.
Distinct Records	Return only unique values. The Order By field must be included in Selected Columns to enable this option.
Show Headers	Add a header row above the extracted data.
Refresh on File Open	Refresh the extraction automatically when the Excel file is opened.
Output Location	Where to place the extracted data: New Worksheet to insert into a new sheet, or Existing Worksheet to insert at a specified starting cell.

Apply dimension filters

Dimension filters narrow the extraction to specific values, such as a particular company, period, or product line. Filtering reduces unnecessary rows and keeps your Excel file easier to maintain as your source data changes.

1. Select a dimension to filter results for the specified field and operation.
2. Choose a filter type:
   • List to include or exclude specific values. Wildcards are supported.
   • From-To to define a numeric or string-based range.
   • Select All to include all values in the dimension (default).
3. Define filter values using a prompt, a cell reference, or manual entry.
4. Select Ok to insert the formula. The full formula appears in the Excel formula bar.

Use of wildcards for filtering

• ? matches exactly one character
• * matches one or more characters

Example	What it matches
4000	Match exactly 4000.
4*	Match any value starting with 4.
4???	Match values starting with 4 and containing exactly four characters.
4???-???-10	Match values starting with 4, followed by three characters, a dash, three characters, another dash, and ending with 10.

Refresh data extractions

The Data Extraction Refresh button in the Add-ins tab updates all data extractions in the current sheet or Excel file. Run a refresh after modifying filters, changing settings, or updating parameters to make sure your tables reflect the latest values from the source system.