How to Convert Column Headers and Values into a List in Excel
Question details
The user needs to convert a wide dataset (spanning columns B through D) into a two-column list by repeating each column's header beside its respective values.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Reorganizing or unpivoting a dataset so that multi-column data is flattened into Attribute-Value pairs for easier analysis or database import.
- Observed behavior
- The goal state is a flat, vertical list where each data point is paired with its original column header, replacing the original wide format.
Ensure you have backed up your original dataset before running macros or data transformations. If using VBA, verify that the Developer tab is enabled in your ribbon.
Use Power Query to Unpivot Columns
The easiest, code-free method to convert columns into a list is using the 'Unpivot' feature in Power Query, which dynamically transforms wide data into key-value pairs.
Power Query is highly recommended for this task because it handles dynamic data ranges automatically and requires no programming knowledge.
Select your data range, navigate to the Data tab, and click 'From Table/Range' to open the Power Query Editor.
Go to the Add Column tab and click 'Index Column'. This creates a unique identifier for your rows, helping keep data organized during the transformation.
Right-click the newly created Index column header and select 'Unpivot Other Columns'. This action transforms the rest of the columns into Attribute (header) and Value pairs.
Rename the newly generated columns as needed. Finally, go to the Home tab and click 'Close & Load' to output the two-column list back into your spreadsheet.
Use a VBA Macro to Loop and Transpose Data
A custom VBA script can quickly iterate through your dataset, matching each value with its header, and outputting the result to a new range.
Easily Manage and Unpivot Data with WPS Spreadsheet
WPS Office provides robust spreadsheet tools including full VBA compatibility. Whether you prefer running macros or using built-in features, reorganizing complex data layouts is fast and straightforward.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing the wide data table you want to unpivot.
- 2. Access the VBA tools: Navigate to the Developer tab to open the VBA Editor and insert your transformation macro.
- 3. Execute the transformation: Run the code to instantly generate your new two-column list without affecting the original data layout.
Frequently Asked Questions
Can I convert columns to a list without VBA?
Yes, using the Power Query 'Unpivot' feature is an excellent, no-code alternative that dynamically transforms wide columns into a clean two-column list.
What happens to blank cells when unpivoting?
By default, Power Query ignores null or blank cells during the unpivot process, ensuring your resulting list only contains rows with actual data values.
Why is my VBA macro not running?
Ensure that your file is saved in a macro-enabled format (.xlsm) and that you have enabled macros in your Trust Center or Developer tab security settings.
