'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Stack Multiple Excel Columns into Person and Item Columns
Question details
The user needs to transform a wide dataset spread across multiple item columns into a two-column format (Person and Item), maintaining the correct person-to-item relationship for varying numbers of items.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Reformatting a horizontal dataset into a flat, unpivoted vertical structure for better data analysis, filtering, and database storage.
- Observed behavior
- Data is currently spread horizontally across multiple columns, making it difficult to analyze or filter items associated with specific people.
Ensure your source data is formatted as an Excel Table (by pressing Ctrl+T) to easily load it into Power Query. If you plan to use the formula method, verify that your version of Excel supports dynamic array functions.
Use Power Query to Unpivot Columns
Power Query is the most robust, dynamic, and maintainable method to transform wide data into a structured two-column layout.
This method works beautifully even if the number of item columns changes in the future, as it dynamically unpivots any additional columns you add.
Select any cell inside your source table. Go to the Data tab on the ribbon and click 'From Table/Range' to open the Power Query Editor.
In the Power Query Editor window, click on the header of the 'Person' column to select it. This is the column that will act as your anchor.
Right-click the 'Person' column header and select 'Unpivot Other Columns' from the context menu. Your data will immediately transform into a vertical layout.
Right-click the newly created 'Attribute' column (which contains the old column headers) and select 'Remove'. Rename the remaining 'Value' column to 'Item', then click 'Close & Load' to output the results to a new worksheet.
Use Dynamic Array Formulas
For users on modern spreadsheet versions, combining WRAPROWS, TEXTSPLIT, and TEXTJOIN provides an instant formula-based solution without opening a separate editor.
Organize Your Data Quickly with WPS Spreadsheet
WPS Office offers robust data handling capabilities, including advanced text functions and dynamic arrays. It makes reformatting complex tables into simple, analyzable structures incredibly easy and efficient.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing the data you need to stack.
- 2. Select the target cell: Click on a blank cell where you want the new Person and Item columns to appear.
- 3. Input the array formula: Enter the combination formula using TEXTJOIN and TEXTSPLIT to evaluate and stack your data ranges.
- 4. Spill the results: Press Enter. WPS Spreadsheet will automatically process the arrays and spill the neatly organized data into two columns.
Frequently Asked Questions
What does unpivoting data mean?
Unpivoting is the process of transforming data from a 'wide' format (spread across multiple columns) into a 'long' format (fewer columns, more rows). It makes datasets much easier to analyze using PivotTables or standard database queries.
Why am I getting a #NAME? error with the WRAPROWS formula?
The #NAME? error typically occurs if your spreadsheet software version does not support newer dynamic array functions like WRAPROWS or TEXTSPLIT. In this case, upgrading your software or using the Power Query method is recommended.
Will Power Query automatically update if I add new items?
Yes. If you add new item columns to your source table, Power Query's 'Unpivot Other Columns' step will automatically include them. You just need to right-click your output table and select 'Refresh'.
