How to Copy and Filter Grouped Data Between Excel Workbooks
Question details
The user needs to extract specific group records from a dynamic master workbook and import them into separate destination report workbooks.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Distributing segmented data (e.g., Group One, Group Two) from a central source workbook to individual report workbooks while capturing ongoing changes like newly added stations.
- Observed behavior
- Without Power Query, users can copy all data but struggle to isolate specific groups automatically. Destination workbooks need a reliable method to filter assigned groups and refresh updated data.
Ensure both your source workbook and the destination report workbooks are saved in accessible, stable folder locations so the data connection remains active during refreshes.
Filter and Extract Data Using Power Query
Utilize Excel's Power Query feature to connect to the source workbook, apply filters for specific groups, and load the refined data into separate reports.
Power Query allows you to create an active connection to an external workbook. By loading the data and applying filters before the data hits your worksheet, you ensure that one report receives Group One data, and another report receives only Group Two data.
Open a new blank workbook for your first report. Navigate to the Data tab on the ribbon, click 'Get Data', select 'From File', and then choose 'From Workbook'. Locate and import your master source file.
In the Navigator window, select the sheet containing your grouped data and click the 'Transform Data' button to open the Power Query Editor.
In the Power Query Editor, click the drop-down arrow on the header of your Group column. Uncheck 'Select All', then select only the specific group (e.g., 'Group One') designated for this report workbook, and click OK.
Once the data is filtered, click 'Close & Load' from the Home tab. The filtered dataset will now populate in your new report workbook.
Whenever the station list in the master source workbook changes, simply right-click the imported data table in your report workbook and select 'Refresh' to pull in the newly added stations.
Manage Spreadsheet Data Seamlessly with WPS Office
While Power Query setups are specific to Microsoft Excel, WPS Spreadsheet offers robust data filtering, pivot tables, and cross-workbook referencing in a lightweight, completely free package.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your device in minutes.
- 2. Open Your Spreadsheets: Double-click your existing Excel workbooks to open them instantly in WPS Spreadsheet with zero formatting loss.
- 3. Filter and Analyze: Use the Data tab to apply advanced filters, consolidate sheets, and manage your grouped data effectively.
Frequently Asked Questions
Can I filter multiple groups into a single report workbook?
Yes. In the Power Query Editor, when you click the drop-down arrow on the Group column, you can check multiple group names before loading the data into your workbook.
What happens to the queries if the source workbook is moved to a new folder?
The Power Query connection will break. You will need to navigate to Data > Get Data > Data Source Settings, select the old file path, click 'Change Source', and browse to the new file location.
Does the destination workbook automatically refresh its data when opened?
By default, you must refresh the data manually. To automate this, go to Data > Queries & Connections, right-click your query, select 'Properties', and check the box for 'Refresh data when opening the file'.
