Hey there! Let's work through your Excel sheet restructuring needs—you’ve got two key tasks to tackle, and I’ll walk you through straightforward, reliable methods for both.

This is perfect when you’ve got a row with multiple associated values (like a single ID linked to several items) that need to become individual rows. The easiest way to do this is with Power Query (built into Excel 2016+ and available as a free add-in for older versions):

• First, select your data range (make sure it has clear headers) and go to the Data tab > From Table/Range. Confirm the prompt to load your data into the Power Query Editor.
• In the editor, select the column that contains the multiple values you want to split.
• Head to the Transform tab > Split Column > By Delimiter. Choose the delimiter separating your values (comma, space, etc.), then select the Split into Rows option in the pop-up.
• Click Close & Load from the Home tab—your data will now be expanded into separate rows, each with one of the original related values paired with its parent row data.

If you’ve got column headers that should actually be row entries (like monthly data in columns that need to become "Month" rows), Power Query is again your go-to tool:

• Load your data into the Power Query Editor the same way as above (select range > Data > From Table/Range).
• Select the columns that you want to keep as fixed identifiers (like an ID or Name column—these won’t be converted to rows).
• Go to the Transform tab > Unpivot Columns > Unpivot Other Columns. This will turn all unselected columns into two new columns: Attribute (which holds your old column headers) and Value (which holds the corresponding cell values).
• You can rename these new columns to something more meaningful (e.g., "Month" instead of "Attribute") by right-clicking the column header > Rename.
• Hit Close & Load to export the restructured data back to Excel.

Bonus: If Power Query Isn’t an Option #

For older Excel versions without Power Query, you can use a combination of formulas (like INDEX, SMALL, and MOD) to manually generate the split rows, but this is more error-prone and less scalable. Power Query is absolutely the better choice for maintaining clean, editable data.

Let me know if you hit any snags with the steps—I’m happy to clarify!

内容的提问来源于stack exchange，提问作者Abdullah Selim