How to Automate Converting a Two-Column Excel Table into Four Columns in WPS Office
Question details
The user needs a repeatable method to convert a two-column Excel table containing group header rows and serial-number rows into a four-column layout showing group names, serial numbers, and sequential item information.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The source table has group headers and serial-number rows in two columns; the user wants to automate the transformation because it repeats almost every week.
- Observed behavior
- Manual processing is time-consuming, and the user wants a query or macro that can be refreshed or rerun instead.
Open the workbook in WPS Spreadsheets and confirm that the source range has a consistent two-column layout, with a clear marker or blank group column identifying group-header rows. If you plan to use Power Query, keep the source data in a table or named range so the query can be refreshed when new rows are added.
Use Power Query to Fill Down Group Headers and Build Four Columns
This is the recommended method when the conversion is repeated every week and you want to refresh the result instead of redoing the steps manually.
Power Query records the cleanup steps as a query. When new weekly data is added, refreshing the query repeats the same transformation without rewriting formulas or filtering cells.
Use this method when your group-header rows have a consistent marker or pattern so they can be separated automatically from serial-number rows.
In WPS Spreadsheets, open the workbook, select a cell in the source two-column range, and click Data > Get Data > From Table/Range. If the Create Table dialog appears, confirm the range and click OK.
In Power Query Editor, select the group name column, then right-click and choose Fill > Down or go to Transform > Fill > Down so every serial-number row inherits the correct group name.
Filter the original header marker column or conditional column to exclude the rows that represent group headers, leaving only serial-number rows.
Click Add Column > Index Column to create the sequential item information, then drag the columns into the final four-column order: group name, serial number, item information, and sequence.
Click Close & Load to place the transformed table in a new worksheet. When new data arrives, add it to the source table and click Data > Refresh All.
Use a VBA Macro to Rearrange, Fill, and Filter the Table Automatically
Use this method if you prefer a macro-based automation that runs directly in the worksheet instead of using Power Query.
Automate Weekly Two-Column to Four-Column Table Conversion with WPS Spreadsheets
WPS Spreadsheets can load the two-column source table into Power Query, fill group names down, remove header rows, add sequence information, and refresh the final four-column layout whenever new data is added.
- 1. Open WPS Spreadsheets and load the source range: Launch WPS Office, open the workbook, select the two-column source range, and click Data > Get Data > From Table/Range to open Power Query.
- 2. Fill the group names down: In Power Query Editor, right-click the group name column and choose Fill > Down so each serial-number row gets the correct group name.
- 3. Remove group-header rows: Filter the marker column or conditional column to exclude group-header rows, leaving only serial-number rows.
- 4. Add sequence and final columns: Click Add Column > Index Column, then reorder the columns into group name, serial number, item information, and sequence.
- 5. Load and refresh weekly: Click Close & Load to place the result in a new sheet. Next week, add data to the source and click Data > Refresh All to regenerate the four-column table.
Frequently Asked Questions
Can I refresh the four-column table after adding next week's data?
Yes. Load the source into Power Query, add the new rows to the source table or range, and click Data > Refresh All in WPS Spreadsheets. The query re-applies fill down, filtering, and index steps.
What if my group headers do not use the same marker?
Add a conditional column in Power Query or make the source marker consistent before loading it. The fill-down and filter steps depend on a reliable way to separate group-header rows from serial-number rows.
Will the transformed workbook stay compatible with Microsoft Excel?
Yes. WPS Spreadsheets saves .xlsx workbooks that remain compatible with Microsoft Excel, so you can open and edit the result in either application.
Can I automate this with VBA instead of Power Query?
Yes. You can run a VBA macro that rearranges the columns, fills down group values, converts formulas to values, and deletes header rows. Use it on a copy if the source layout changes.
