How to Automate Weekly Excel Data Processing Without VBA in WPS Office
Question details
The user needs to automate recurring weekly Excel tasks such as filtering billing data, deleting unwanted rows, creating summary sheets, building a sales PivotTable, and saving the result without using VBA.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Recurring weekly report preparation from a billing file requires cleaning, summarization, and PivotTable creation.
- Observed behavior
- The user needs a repeatable, non-VBA workflow that loads clean data and updates a sales PivotTable each week.
Open WPS Office Spreadsheets and confirm the weekly source file is saved as a stable .xlsx or .csv file. Keep the source columns and header names consistent so the Power Query transformation steps can run against each new weekly file.
Create a Refreshable Power Query Workflow in WPS Office Spreadsheets
Use this to replace manual cleanup and layout-dependent macros with a query that filters, removes rows, and loads clean data for a PivotTable.
Power Query records transformation steps based on column names and filters rather than cell positions. This makes the workflow reliable when each weekly file has a different number of rows or a slightly different layout.
In WPS Office Spreadsheets, open the Data tab, click Get Data or From File/CSV, select the new weekly workbook or CSV file, and choose the sheet or table to import into Power Query Editor.
In Power Query Editor, click the filter arrow on the billing period or date column and keep only the current week. Then select the unwanted columns or rows, right-click, and choose Remove Rows or Remove Columns to delete blanks, totals, or irrelevant records.
Click Close & Load to put the query result on a new worksheet or in the Data Model. Select the loaded table, open the Insert tab, click PivotTable, and build the sales PivotTable from the query output fields.
Save the workbook. Next week, point the query to the new weekly file if needed, then go to Data > Refresh All or right-click the query table and choose Refresh to update the clean data and PivotTable.
Refresh an Existing Power Query Workflow for a New Weekly File
Use this after the query, summary sheet, and PivotTable already exist and you only need to update the source file and refresh the outputs.
Use WPS Office Spreadsheets Power Query to Automate Weekly Excel Reports Without VBA
WPS Office Spreadsheets lets you connect to a weekly Excel or CSV file, apply filter and row-removal transformations in Power Query, load the clean result to a worksheet or data model, and build a sales PivotTable that refreshes weekly. This replaces layout-dependent VBA macros with a repeatable query workflow.
- 1. Download and install WPS Office: Go to the official WPS Office Download page, install WPS Office, and open WPS Office Spreadsheets.
- 2. Connect to the weekly file: In WPS Office Spreadsheets, select the Data tab, click Get Data or From File/CSV, and choose the weekly .xlsx or .csv source file.
- 3. Build the cleanup query: In Power Query Editor, filter the billing period or date, remove unwanted rows or columns, and click Close & Load to output the cleaned data.
- 4. Create and refresh the PivotTable: Insert a PivotTable from the query output, build the sales summary, and use Data > Refresh All after adding the next weekly file.
Frequently Asked Questions
Does WPS Office support Power Query for Excel files?
Yes. WPS Office Spreadsheets can import Excel .xlsx and .csv files and provides data connection and refresh tools for Power Query-style workflows. Use the Data tab to connect to your weekly source file and reopen the query editor to adjust steps.
Can I remove unwanted rows automatically without VBA?
Yes. In Power Query Editor, apply column filters and use Remove Rows or Remove Columns. These steps are saved in the query and run again each time you refresh.
How do I update the sales PivotTable after replacing the weekly file?
After updating the source connection, click Data > Refresh All or right-click the query table and choose Refresh. The PivotTable based on that query updates automatically.
Why is Power Query better than a recorded macro for weekly reports?
Power Query steps use column names and filters, not fixed cell references. It continues to work when the weekly file changes row count or layout, while a recorded macro often fails when positions shift.
What file formats should I use for the weekly source data?
Use a consistent .xlsx or .csv file with the same header row. WPS Office Spreadsheets opens these formats and preserves Microsoft Excel file compatibility.
