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
Structured reference sideways copy kelyavar column badalto (relative sarkha) – khup students la he mahit nasta aani chukicha column yeto. Column fix karaycha asel tar tblOrders[[Amount]:[Amount]] asa liha. Suruvatila [@Column] cha arth "hya row cha" – evdha lakshat theva, baki ekdum simple aahe.
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.