6. Tables, Sorting and Filtering
6.2 Structured References
Inside or outside a Table you can refer to columns by name.
| Reference | Means |
|---|---|
tblOrders[Amount] |
The Amount column (data rows only) |
tblOrders[@Amount] |
Amount in this row (inside the Table) |
tblOrders[#Headers] |
The header row |
tblOrders[#Totals] |
The Total Row |
tblOrders[[#All],[Amount]] |
Header + data + total of Amount |
tblOrders[[City]:[Area]] |
Columns City to Area |
Worked example. Add a calculated column Amount incl GST inside tblOrders: type in the first cell
=[@Amount]*(1+GST_Rate)
and press Enter – Excel fills the whole column. Outside the Table: Pune sales =SUMIFS(tblOrders[Amount], tblOrders[City], "Pune").
Ravindra Bagale's Tip
When you copy a structured reference sideways, the column changes (like a relative reference) – many students don't know this and get the wrong column. To fix the column, write tblOrders[[Amount]:[Amount]]. At the start, [@Column] means "of this row" – just remember that; the rest is very simple.
Ravindra Bagale's Tip – मराठी
Structured reference बाजूला (sideways) copy केल्यावर column बदलतो (relative सारखा) – बऱ्याच students ना हे माहीत नसतं आणि चुकीचा column येतो. Column fix करायचा असेल तर tblOrders[[Amount]:[Amount]] असं लिहा. सुरुवातीला [@Column] चा अर्थ "या row चा" – एवढं लक्षात ठेवा, बाकी एकदम सोपं आहे.
Ravindra Bagale's Tip – हिंदी
Structured reference को बगल में (sideways) copy करने पर column बदल जाता है (relative की तरह) – बहुत से students को यह पता नहीं होता और गलत column आ जाता है. Column fix करना हो तो tblOrders[[Amount]:[Amount]] ऐसे लिखो. शुरुआत में [@Column] का मतलब है "इस row का" – बस इतना याद रखो, बाकी बहुत आसान है.
Practice task
Add calculated columns Delivery Fee (from 4.9 using XLOOKUP on the Amount) and Net Amount = [@Amount]+[@[Delivery Fee]]. Write a SUMIFS outside the Table for Blinkit sales in Nagpur.