How to Use Excel Power Query to Filter Data for Word Templates
Question details
The user needs to extract and filter specific columns and rows from an Excel dataset to populate a Word template.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Preparing specific data subsets from a large Excel dataset to use in document generation or Word templates.
- Observed behavior
- The user is looking for a structured workflow using Power Query to isolate required data fields and load them into a clean table for Word export.
Ensure your source data is formatted as an official Excel Table (press Ctrl+T) before attempting to load it into Power Query. Verify that your target Word template is ready to receive the pasted data or mail merge fields.
Filter Data using Power Query and Export to Word
Load your table into Power Query, select the necessary columns, apply filters to specific values, and output the clean table for document generation.
Select any cell within your existing Excel table, navigate to the Data tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.
In the Power Query Editor, click 'Choose Columns' on the Home tab. Check the boxes only for the detail fields you need for your Word template and click OK.
Click the drop-down arrow on your target 'Items' column header. Uncheck 'Select All' and choose the required values (e.g., 1, 5, 10, 12) to filter the rows.
Click 'Close & Load' on the Home tab. Excel will process your query and create a new worksheet containing your cleanly filtered table.
Select the newly created filtered table, copy it (Ctrl+C), and paste it into your Word template using 'Keep Source Formatting', or link the filtered data sheet to your document-generation workflow.
Filter Data and Create Documents Easily in WPS Office
WPS Office provides a seamless workflow between WPS Spreadsheet and WPS Writer. You can easily filter large datasets, utilize the Mail Merge feature, or dynamically link tables directly into your text documents without complex query setups.
- 1. Open your spreadsheet in WPS Office: Launch WPS Spreadsheet and open your existing data file.
- 2. Apply AutoFilter to your data: Go to the Data tab and click 'AutoFilter'. Use the column dropdowns to filter the exact items and columns you need for your document.
- 3. Export or Mail Merge to WPS Writer: Copy the visible filtered cells and paste them directly into your WPS Writer document, or use the Mail Merge feature in Writer to map the filtered spreadsheet fields to your document template automatically.
Frequently Asked Questions
Why is 'From Table/Range' grayed out in Excel?
This option may be disabled if you are actively editing a cell, if multiple sheets are grouped, or if your document is protected. Ensure you only have one cell selected, press Enter to finish editing, and turn off sheet protection.
Can I link the Power Query table to Word so it updates automatically?
Yes. When pasting the table into Word, choose 'Link & Keep Source Formatting' from the Paste Options. When the Power Query table refreshes in Excel, the linked table in Word can be updated by right-clicking it and selecting 'Update Link'.
Does Power Query alter my original data when I filter it?
No, Power Query never modifies your original source data. It extracts the data, applies the steps you defined, and loads the transformed result into a completely new table or data model, leaving your source untouched.
