How to Convert Repeating Excel Columns to Rows and Keep Comments
Question details
The user needs to transform repeating pairs of columns (e.g., Hours and Dates) into a single, normalized set of rows while preserving the original cell comments.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Normalizing a wide dataset with repeating column groups into a vertical table layout without losing annotated information.
- Observed behavior
- Standard Power Query unpivots the data perfectly but strips out the cell comments, resulting in data loss for annotated cells.
Ensure you save a backup of your original workbook, as transforming data with macros will permanently alter cell structures and comments cannot be easily recovered if overwritten.
Use a VBA Macro to Transpose Columns and Preserve Comments
The best method when you must retain cell comments from the original repeating columns.
Because Power Query handles raw data rather than cell metadata, it automatically drops comments during transformation. Using Visual Basic for Applications (VBA) allows you to loop through the data and manually copy both the cell values and their associated comments.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications editor in Excel.
Click 'Insert' from the top menu and select 'Module' to create a blank script window.
Write or paste a custom VBA script designed to loop through your repeating column pairs, copy the cell values to a new row, and extract the Comment.Text property to the destination cell.
Close the editor, go to the 'Developer' tab, click 'Macros', select your new script, and click 'Run' to generate the normalized table with comments intact.

Use Power Query to Normalize Columns (Without Comments)
Ideal for reshaping data quickly if preserving cell comments is not strictly required.
Normalize Complex Tables and Run Macros in WPS Spreadsheet
WPS Spreadsheet provides powerful data handling tools, including robust VBA/Macro support, allowing you to restructure complex tables and retain valuable cell comments effortlessly.
- 1. Open Your File: Launch WPS Spreadsheet and open the workbook containing your repeating columns.
- 2. Access the VBA Editor: Navigate to the 'Tools' tab and select 'Macros' > 'Visual Basic Editor' to input your transposition script.
- 3. Run the Transformation: Execute the script to seamlessly convert your repeating columns into rows while copying over all cell comments.

Frequently Asked Questions
Why does Power Query remove cell comments?
Power Query is designed to extract, transform, and load raw data values. It does not carry over cell metadata, formatting, or comments from the source worksheet.
Can I extract comments into a separate column instead of keeping them attached to the cell?
Yes, when writing your VBA macro, you can modify the script so that the Comment.Text property is written as plain text to an adjacent cell in the new row rather than inserted as a comment object.
Is there a built-in Excel function to transpose data and keep comments?
The standard 'Paste Special > Transpose' feature keeps comments, but it simply rotates the data. It cannot normalize repeating pairs of columns into a single vertical list. For complex normalization, a VBA macro is required.




