How to Import Filtered Account Data from a Master Excel File
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.

- 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 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.
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.
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.
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.
Select any confidential columns or formula-based columns that should not be distributed, right-click the header, and choose 'Remove Columns'.
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.
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.

Automate Data Distribution with a VBA Macro
A VBA script can automatically open the master workbook, filter rows, copy only the values (excluding formulas), and save them to separate files.
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. Open your master file: Launch WPS Spreadsheet and open your central master workbook.
- 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. 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. Save securely: Save the new, stripped-down file securely for the specific account user.

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.




