How to Convert Wide Payment Data into a Two-Column Excel Table
Question details
The user needs to transform wide payment data containing multiple columns into a normalized format with one Pledge ID and one Payment ID per row.
- Product
- Microsoft Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Consolidating flat, wide payment reports into a structured, relational two-column format for easier database import and analysis.
- Observed behavior
- Payment data is spread horizontally across multiple columns instead of being listed vertically in a normalized, two-column table.
Ensure your dataset has clear headers without merged cells, and remove any entirely blank columns between the Pledge ID and Payment ID columns to prevent query or formula errors.
Use Power Query to Unpivot Columns
The most robust and visually intuitive method for normalizing wide data, especially for large datasets or older Excel versions.
Power Query allows you to easily unpivot columns without writing complex formulas. It also automatically handles varying row sizes and updates with a single click when your source data changes.
Select any cell inside your data table, navigate to the Data tab on the ribbon, and click 'From Table/Range'.
In the Power Query Editor, select the 'Pledge ID' column. Right-click the column header and select 'Unpivot Other Columns'.
Delete the newly created 'Attribute' column by right-clicking its header and selecting 'Remove'. You should now only have the Pledge ID and the Payment ID (Values) columns remaining.
Click the 'Close & Load' button in the Home tab. Excel will output the new normalized two-column table onto a new worksheet.
Use a Dynamic Array Formula (Microsoft 365)
Ideal for users on Microsoft 365 who want a live, automatically updating output table without needing to refresh queries.
Use a VBA Macro for Automated Conversion
Best for power users handling highly repetitive tasks who want a customizable, one-click automated solution.
Easily Normalize and Analyze Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas and VBA macros, allowing you to seamlessly unpivot wide datasets into clean two-column tables just as you would in Microsoft Excel.
- 1. Open your wide dataset: Launch WPS Spreadsheet and open your document containing the wide payment columns.
- 2. Identify your data ranges: Locate your Pledge ID column and define the range covering your horizontal Payment ID columns.
- 3. Apply dynamic formulas or VBA: Use standard dynamic array functions in a blank sheet, or run a VBA normalization script directly within the WPS Developer tab.
- 4. Format as a table: Select the newly generated two-column output, navigate to the Home tab, and select 'Format as Table' to finalize your data for analysis.
Frequently Asked Questions
Why are there blank rows in my converted two-column table?
Blank rows appear if your formula or VBA script does not filter out empty cells in the wide payment columns. Adding a condition to skip blanks, or using the TOCOL function with the ignore blanks parameter (set to 1), will prevent blank records from appearing.
Can I unpivot columns in Excel without using formulas or VBA?
Yes, you can use the Power Query tool. Go to the Data tab, click 'From Table/Range', right-click the ID column in the editor, and select 'Unpivot Other Columns'. This provides a visual interface without requiring coding knowledge.
What does the REDUCE and TOCOL formula do?
TOCOL combines the horizontal wide data into a single vertical column while ignoring blanks. REDUCE then iterates through this combined array, and TEXTSPLIT divides the concatenated pairs of Pledge and Payment IDs into a neat two-column format.
Will this dynamic array formula work on older versions of Excel?
No, functions like REDUCE, TOCOL, and VSTACK are exclusive to Microsoft 365 and Excel for the Web. If you are using older versions like Excel 2016 or 2019, you should use the Power Query method or VBA macros instead.
