6. Tables, Sorting and Filtering
6.2 Structured References
Structured references in Excel Tables with example — in 30 seconds
What: In a Table, formulas use column names like tblOrders[Amount] instead of G2:G11.
Why: New rows are included automatically, and the formula explains itself to the next person.
Tiny example: =SUMIFS(tblOrders[Amount], tblOrders[City], "Pune") gives 544. Add an 11th Pune row and the answer updates without editing the formula.
Structured references in Excel Tables with example — 30 सेकंदात
काय: Table मध्ये formulas G2:G11 ऐवजी tblOrders[Amount] सारखी column नावं वापरतात.
का: नव्या rows आपोआप धरल्या जातात, आणि formula पुढच्या माणसाला स्वतःच समजावतो.
छोटं उदाहरण: =SUMIFS(tblOrders[Amount], tblOrders[City], "Pune") उत्तर 544 देतो. 11वी Pune row add केली तरी formula न बदलता उत्तर update होतं.
Structured references in Excel Tables with example — 30 सेकंड में
क्या: Table में formulas G2:G11 की जगह tblOrders[Amount] जैसे column नाम इस्तेमाल करते हैं.
क्यों: नई rows अपने आप शामिल होती हैं, और formula अगले व्यक्ति को खुद समझा देता है.
छोटा उदाहरण: =SUMIFS(tblOrders[Amount], tblOrders[City], "Pune") जवाब 544 देता है. 11वीं Pune row add करने पर भी formula बदले बिना जवाब update होता है.
First look at the data (before → after):
Expected result — Total for Pune
| Formula | Answer |
|---|---|
| =SUMIFS(tblOrders[Amount], tblOrders[City], "Pune") | 544 |
About this data: Teaching sample for a fictional shop “NovaCraft Tools”. Figures and names (e.g. Vikram Rao) are made up for this lesson on structured references.
Expected result — Calculated column fills every row by itself
| Order ID | Amount | Amount incl. GST |
|---|---|---|
| BLK-1001 | 64 | 67.20 |
| BLK-1002 | 270 | 283.50 |
| AMN-1003 | 110 | 115.50 |
About this data: Demo table only — not real customer or company data. Built to practise structured references.
Before table and formula as text
Before — Range A1:I11 converted to Table tblOrders (first rows)
| Order ID | City | Amount |
|---|---|---|
| BLK-1001 | Pune | 64 |
| BLK-1002 | Nashik | 270 |
| AMN-1003 | Pune | 110 |
About this data: Made-up rows so you can copy the pattern for structured references. Currencies may use ₹, £, € or $.
Formula / click path
K2: =SUMIFS(tblOrders[Amount], tblOrders[City], "Pune")
New column J2: =[@Amount]*(1+GST_Rate) (GST_Rate = 5%)
Do it step by step:
- Click inside the data and press Ctrl + T, then OK.
- On Table Design, type
tblOrdersin Table Name. - In an empty cell, type
=SUMIFS(tblOrders[Amount], tblOrders[City], "Pune"). Click the columns instead of typing names. - In the first empty column of the table, type
=[@Amount]*(1+GST_Rate). It fills the whole column. - Add a new row at the bottom and watch both formulas include it.
स्टेप बाय स्टेप करून बघा:
- Data मध्ये click करा आणि Ctrl + T दाबा, मग OK.
- Table Design मध्ये Table Name ला
tblOrderstype करा. - एका रिकाम्या cell मध्ये
=SUMIFS(tblOrders[Amount], tblOrders[City], "Pune")type करा. नावं type करण्याऐवजी columns वर click करा. - Table च्या पहिल्या रिकाम्या column मध्ये
=[@Amount]*(1+GST_Rate)type करा. पूर्ण column आपोआप भरतो. - खाली नवी row add करा आणि दोन्ही formulas ती धरतात ते बघा.
स्टेप बाय स्टेप करके देखें:
- Data में click करें और Ctrl + T दबाएँ, फिर OK.
- Table Design में Table Name में
tblOrderstype करें. - किसी खाली cell में
=SUMIFS(tblOrders[Amount], tblOrders[City], "Pune")type करें. नाम type करने की जगह columns पर click करें. - Table के पहले खाली column में
=[@Amount]*(1+GST_Rate)type करें. पूरा column अपने आप भर जाता है. - नीचे नई row add करें और देखें कि दोनों formulas उसे शामिल करते हैं.
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 |
About this data: Fictional worksheet used in class. Safe to edit; nothing here is live production data.
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 FreshGo sales in Nagpur.
Loading practice tasks...
- Total quantity (Easy): Add up the "Quantity" column.
- Average unit price (Easy): Average of the Unit price ($) column, rounded to 2 decimals.
- Rows in Website (Easy): How many rows have Channel "Website"?
- Largest quantity (Easy): The largest value in the "Quantity" column.
- Quantity for Website (Medium): Total of the Quantity column where Channel is "Website".
- Website in Denver (Medium): Total of the Quantity column where Channel is "Website" and City is "Denver".
- Best in Website (Medium): Largest value in the "Unit price ($)" column where "Channel" is "Website".
- Value of row 2 (Medium): Multiply the first-row Quantity by the first-row Unit price ($).
- Look up Ananya Nair (Medium): The Quantity for "Ananya Nair" (use a lookup on the "Name" column).
- Total value (Hard): Sum of "Quantity" times "Unit price ($)" for all rows.
- Share of Website (Hard): Share of the "Quantity" column that is "Website", rounded to 3 decimals.
- Share of the total "Unit price ($)" with a locked range (Hard): Find what share of the total "Unit price ($)" the first row has. Round it to 3 decimals.
- Above average? (Hard): "Above" if the "Unit price ($)" value in the first row is above the average of the "Unit price ($)" column, else "Below".
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
