Reshaping Data for Visualization
Swipe to show menu
Charts work best with simplified and structured datasets. In this chapter, reshape dynamic summary outputs into a chart-ready format using TRANSPOSE.
TRANSPOSE Structure
=TRANSPOSE(array)
array: range or dataset being flipped;- Vertical ranges become horizontal;
- Horizontal ranges become vertical.
Formula used in this chapter:
=TRANSPOSE(Summary!A2:B7)
This converts the vertical summary table into a horizontal chart-ready structure.
Spill References in Aggregation Formulas
=A2#
A2#: full spill range connected to the anchor cell.
Dynamic aggregation formulas used in this chapter:
=SUMIFS(Sales_Data[Revenue], Sales_Data[Region], A2#)
=COUNTIFS(Sales_Data[Region], A2#)
The formulas automatically expand to match the size of the spilled region list.
Inside the Summary sheet, replace the static region values with:
=UNIQUE(Sales_Data[Region])
The region list now updates automatically when new regions are added.
Inside B2, type:
=SUMIFS(Sales_Data[Revenue], Sales_Data[Region], A2#)
Each region now receives its own dynamic Revenue total.
Inside C2, type:
=COUNTIFS(Sales_Data[Region], A2#)
The order counts now update dynamically together with the region list.
Copy the following row into Sales_Data:
14/03/2026,1073,C020,Laptop,Southwest,Sofia,3
Confirm that the Summary calculations update automatically.
Create a new worksheet named:
Charts
Inside A2 of the Charts sheet, type:
=TRANSPOSE(Summary!A2:B7)
The summary table is now converted into a horizontal structure suitable for charting.
Modify values inside Sales_Data.
Confirm that updates flow automatically through:
Sales_Data → Summary → Charts
Select:
A2:F3
Insert a clustered column chart.
Confirm that:
- Regions become axis labels;
- Revenue values become column heights.
Add additional rows into Sales_Data.
Confirm that:
- The Summary sheet updates automatically;
- The Charts sheet updates automatically;
- The chart visualization updates automatically.
1. Why is TRANSPOSE used in data visualization workflows?
2. What does the A2# reference represent in dynamic formulas?
3. Why are Excel Tables important in dynamic reporting systems?
Thanks for your feedback!
Ask AI
Ask AI
Ask anything or try one of the suggested questions to begin our chat
