How to Combine Multiple Rows and Columns into One Ordered List in Excel
Question details
The user needs to reshape a yearly and monthly cross-tab report into a single ordered three-column list containing each Year, Month, and Value combination.

- Product
- WPS Spreadsheets / Microsoft Excel
- Device & OS
- not provided
- Scenario
- Data is currently arranged with years down the rows and months across the columns. The user wants a flat ordered list for analysis, reporting, or further processing.
- Observed behavior
- The current layout is a cross-tab matrix. The desired state is one row per year-month-value combination in the order Year, Month, Value.
Make sure the source data is in a contiguous Excel Table or named range with a Year column and separate month columns, and remove any totals, blank rows, or merged cells. This keeps both Power Query and dynamic array formulas from misreading the source range.
Use Power Query to unpivot monthly columns into an ordered list
Use this method when the data is large or changes often because Power Query creates a refreshable output table that updates when the source data changes.
Power Query is the most reliable option for larger or frequently updated data because the result table remains connected to the source and can be refreshed in one click.
Click any cell inside the yearly/monthly table. In Excel, go to Data > From Table/Range. In WPS Spreadsheets, open the Data tab and choose From Table/Range, then confirm the range and select My table has headers.
In the Power Query Editor, select all month columns such as January through December, then click Transform > Unpivot Columns. The month names move into a single Attribute column and the values move into a Value column.
Double-click the Attribute column header and rename it to Month, then rename the Value column if needed. Use the column sort buttons on Year and Month, or add a custom month number column to keep January-to-December order.
Click Home > Close & Load and choose a new worksheet or table destination. When the source data changes, refresh the output table via Data > Refresh All.

Use dynamic array formulas to build a Year, Month, Value list
Use this one-shot formula approach for smaller tables when your Excel or WPS version supports VSTACK, HSTACK, and TOCOL.
Create a Refreshable Ordered List in WPS Spreadsheets
WPS Spreadsheets lets you reshape a cross-tab report into one ordered Year, Month, Value list using a refreshable query or dynamic-array formulas, while keeping full Excel workbook compatibility.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the .xlsx file that contains the yearly and monthly data.
- 2. Load the cross-tab table into Power Query: Click a cell inside the data, go to Data > From Table/Range, and confirm the table range in the Create Table dialog.
- 3. Unpivot and rename the month columns: In the Power Query Editor, select all month columns, choose Transform > Unpivot Columns, then rename the Attribute column to Month and the Value column to Value.
- 4. Load and save the ordered list: Click Home > Close & Load, choose a new worksheet, and save the file as .xlsx via Menu > Save As.

Frequently Asked Questions
Can I combine rows and columns into one ordered list without Power Query?
Yes. If your Excel or WPS version supports dynamic arrays, use the VSTACK, HSTACK, and TOCOL formula shown above. Otherwise, Power Query is the most reliable method for unpivoting the data.
How do I refresh the ordered list after adding new yearly data?
With the Power Query method, click the result table and choose Data > Refresh All or right-click the table and select Refresh. Formula results recalculate automatically when the source cells change.
Why does my formula return #NAME? or #SPILL!?
#NAME? means the dynamic array functions are not available in your version, so use Power Query. #SPILL! means something is already in the destination spill range, so clear those cells before entering the formula.
How can I keep months in January-to-December order instead of alphabetical order?
In Power Query, add a helper column with month numbers and sort by that column. For formulas, you can create a lookup list that assigns each month name a number and sort the result by that order.
Does WPS Spreadsheets save the result as an Excel file?
Yes. After creating the ordered list, go to Menu > Save As and choose .xlsx to keep the workbook fully compatible with Microsoft Excel.




