Data Import & Export

How to Combine Multiple Rows and Columns into One Ordered List in Excel

Steven ParkerSteven Parker Nov 13, 2026 868 views

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.

How to Combine Multiple Rows and Columns into One Ordered List in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the query editor from the data range

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.

2
Select and unpivot the month columns

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.

3
Rename and order the result columns

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.

4
Load the result to a worksheet

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 Power Query to unpivot monthly columns into an ordered list
Refreshable result: Because the ordered list is query-backed, you can add new rows to the source table and refresh to update the list without recreating the transformation.
WPS Spreadsheets data transformation

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. 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. 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. 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. 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.
Unpivot yearly/monthly tables into a flat Year-Month-Value list directly in WPS SpreadsheetsFull .xlsx Microsoft Excel format compatibility for opening and saving workbooksFree, lightweight Office suite with a familiar ribbon interfaceRefreshable Power Query workflow keeps the ordered list up to date
QA img-9

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.