Data Import & Export

How to Import Filtered Account Data from a Master Excel File

Natalie TaylorNatalie Taylor Nov 10, 2026 870 views

Question details

The user needs to distribute specific, filtered rows from a master workbook to separate account files while protecting confidential data and excluding formula cells.

How to Import Filtered Account Data from a Master Excel File
Product
Excel
Device & OS
not provided
Scenario
Distributing relevant account-specific data from a central master file to individual users or departments securely.
Observed behavior
Users require a secure and automated method to extract matching values based on an account code and save them to separate workbooks without carrying over formulas or exposing unauthorized data.
Before you start

Before splitting the master file, ensure your account codes (e.g., in column K) are standardized and that you have clearly identified all confidential columns that must be excluded from the distribution.

Solution 1Recommended

Use Power Query to Filter and Load Data Securely

Power Query is the most robust method to filter data by account code and load only the necessary columns into destination workbooks without exposing the full dataset.

Creating queries directly in the destination workbooks that connect to the master file might expose the full dataset to a knowledgeable user who knows how to edit the query.

To maintain security, it is best to create separate queries for each account within the master workbook (or an intermediate protected file), and then use those resulting tables as the secure source for the destination workbooks.

1
Load the master data into Power Query

Open the master workbook, go to the 'Data' tab, and select 'Get Data' > 'From Table/Range' to load your master data table into the Power Query Editor.

2
Filter by account code

In the Power Query Editor, locate the account code column (e.g., Column K), click the filter dropdown, and select the specific account you want to extract.

3
Remove confidential columns

Select any confidential columns or formula-based columns that should not be distributed, right-click the header, and choose 'Remove Columns'.

4
Load to an intermediate table

Click 'Close & Load To...' and load the filtered table into a new worksheet within the master file. Repeat this to create a dedicated filtered table for each account.

5
Connect destination workbooks

In the separate destination workbook for each account, use Power Query ('Get Data' > 'From File' > 'From Workbook') to connect exclusively to the specific filtered table created in the previous step. Configure the file connection properties to refresh automatically upon opening.

Use Power Query to Filter and Load Data Securely
Security Tip: This indirect connection method prevents end-users from accessing the master query and viewing other accounts' confidential data.
Powerful Data Management

Filter and Export Master Data Easily with WPS Office

WPS Spreadsheet offers robust data filtering tools and macro support, allowing you to securely distribute master file data to separate account workbooks with ease.

  1. 1. Open your master file: Launch WPS Spreadsheet and open your central master workbook.
  2. 2. Apply advanced filters: Navigate to the 'Data' tab and use the 'Advanced Filter' feature to extract rows based on your specific account criteria.
  3. 3. Copy and paste as values: Copy the filtered results, open a new blank workbook, and right-click to select 'Paste Special' > 'Values' to ensure formulas and confidential links are excluded.
  4. 4. Save securely: Save the new, stripped-down file securely for the specific account user.
Seamless compatibility with Microsoft Excel (.xlsx, .xls, .xlsm) formats.Advanced data filtering and querying tools to handle large datasets efficiently.Built-in support for macros to automate repetitive data distribution tasks without hassle.Free to download with a lightweight, user-friendly interface.
microsoft office alternative - wps office

Frequently Asked Questions

How can I prevent users from seeing hidden rows in the distributed Excel files?

When distributing data, never just hide confidential rows or columns in the master file and send it out, as users can easily unhide them. Instead, extract and copy only the visible, relevant data to a completely new workbook before sharing.

Can I automatically exclude formulas when splitting a master file?

Yes. When using a VBA macro, you can utilize the PasteSpecial method with xlPasteValues to convert all formulas to static text in the destination workbook. If you use Power Query, data is automatically loaded as static values by default.

Why shouldn't I connect destination workbooks directly to the master file using Power Query?

If you connect a destination workbook directly to the master file, a knowledgeable user could modify the Power Query connection settings to remove the account filter, potentially gaining unauthorized access to the confidential information of other accounts.

Is it possible to schedule automatic updates for the distributed account files?

Yes. If you use Power Query to pull data from a secure intermediate table, you can go to 'Data' > 'Queries & Connections', right-click the query, select 'Properties', and check 'Refresh data when opening the file' or set a specific time interval.