Transpose linking sheets updating data

Hi Matthew,

Thank you for the forum question.

You can fully automate this in Excel—no copy/paste needed—by combining Power Query (best option) or formulas depending on your version.
________________________________________
Best approach: Power Query (fully automated, refreshable)
This is ideal since:
• Handles text → numbers safely
• Deals with "n/a" cleanly
• Automatically updates when data changes
• Performs the transpose + reshape in one flow
________________________________________
Step-by-step
1. Convert your raw data into a Table
• Select your data (including headers)
• Press Ctrl + T
• Name it e.g. RawData
Example:
Proposal_A Proposal_B Proposal_C
Assessor_1 1 2 3
Assessor_2 1 3 2
Assessor_3 2 1 3
________________________________________
2. Load into Power Query
• Go to Data → From Table/Range
________________________________________
3. Transform the structure
In Power Query editor:
A. Unpivot the proposals
• Select all proposal columns (Proposal_A, etc.)
• Click: Transform → Unpivot Columns
Result becomes:
Assessor Attribute Value
Assessor_1 Proposal_A 1
Assessor_1 Proposal_B 2
... ... ...
Rename:
• Attribute → Proposal
• Value → Score
________________________________________
B. Pivot back into your target layout
• Select Assessor column
• Click: Transform → Pivot Column
• Values column = Score
• Aggregation = “Don’t Aggregate” (or Min if forced)
Now you have:
Proposal Assessor_1 Assessor_2 Assessor_3
Proposal_A 1 1 2
Proposal_B 2 3 1
Proposal_C 3 2 3
________________________________________
4. Handle "n/a" values
Before pivoting:
• Select Score column
• Replace values:
o "n/a" → null
• Then set column type → Whole Number
________________________________________
5. Load result
• Click Close & Load
• Output goes to your Analysis sheet
________________________________________
Result
Whenever new data is pasted or refreshed:
• Click Refresh
• Everything updates automatically
________________________________________
Alternative: Formula-only solution (Excel 365)
If you prefer formulas:
Step 1 – Reference raw data
Assume raw data in A1:D4
Step 2 – Use TRANSPOSE
You can flip axes:
=TRANSPOSE(A1:D4)
But this alone won’t restructure headers properly.
________________________________________
Better formula solution (dynamic array)
Use INDEX to re-map:
=INDEX($B$2:$D$4, COLUMN()-1, ROW()-1)
Place in output grid starting at Proposal_A row.
________________________________________
Handle "n/a"
Convert safely:
=IFERROR(--B2, "")
________________________________________
Recommendation
Method Best for
Power Query Automation, refresh, error handling
Formulas Simpler, but less robust
________________________________________
Extra tip (important)
If your file is downloaded regularly:
• Store it in a fixed location
• Use Data → Get Data → From File
• Then your model refreshes without manual intervention
________________________________________
Summary
• Use Power Query
• Unpivot → Pivot to reshape
• Replace "n/a" with null
• Load to analysis sheet
• Hit Refresh for new datasets
________________________________________





Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector