6. Tables, Sorting and Filtering
6.1 Excel Tables (Ctrl + T)
In short: An Excel Table is a range that Excel manages for you: it grows automatically, keeps formatting, copies formulas down, has filter buttons and a name.
An Excel Table is a range that Excel manages for you: it grows automatically, keeps formatting, copies formulas down, has filter buttons and a name.
Steps in Excel
- Click any cell in the Orders data (no blank rows or columns inside).
- Press Ctrl + T (or Insert › Tables › Table, or Home › Styles › Format as Table).
- Keep My table has headers ticked › OK.
- Table Design › Properties › Table Name › type
tblOrders› Enter. (The tab is called Design in some older versions – may vary by version.) - Pick a style in Table Design › Table Styles; keep Banded Rows on.
- Type a new order in the first empty row below – the Table expands and formulas and formats extend automatically.
- To go back to a normal range: Table Design › Tools › Convert to Range.
| Plain range | Excel Table |
|---|---|
| Formulas must be copied manually | Calculated columns fill automatically |
| Charts/Pivots miss new rows | Charts, PivotTables, validation lists grow with the Table |
$A$2:$A$500 references |
Readable tblOrders[Amount] |
| Header scrolls away | Column letters replaced by header names while scrolling |
About this data: Teaching sample for a fictional shop “NovaCraft Tools”. Figures and names (e.g. Neha Shah) are made up for this lesson on excel tables ctrl t.
Ravindra Bagale's Tip
Many students create a Table but never name it – names like Table1 and Table7 tell you nothing in a formula. Right after creating a Table, give it a meaningful name starting with tbl (tblOrders, tblStores). And don't leave empty rows/columns inside a Table.
Ravindra Bagale's Tip – मराठी
बरेच students Table बनवतात पण त्याला नावच देत नाहीत – Table1, Table7 अशी नावं formulas मध्ये काहीच सांगत नाहीत. Table बनवल्यावर लगेच tbl ने सुरू होणारं अर्थपूर्ण नाव द्या (tblOrders, tblStores). आणि Table च्या आत रिकाम्या rows/columns ठेवू नका.
Ravindra Bagale's Tip – हिंदी
बहुत से students Table बना लेते हैं पर उसे नाम ही नहीं देते – Table1, Table7 जैसे नाम formulas में कुछ नहीं बताते. Table बनाते ही tbl से शुरू होने वाला मतलब वाला नाम दो (tblOrders, tblStores). और Table के अंदर खाली rows/columns मत छोड़ो.
Practice task
Convert your Orders, Stores and Products data into Tables named tblOrders, tblStores and tblProducts. Add two new orders and check that a chart based on tblOrders includes them.
Loading practice tasks...
- Total units sold (Easy): Add up Units sold in the "Units sold" column.
- Average price (Easy): Average of the Price (₹) column, rounded to 2 decimals.
- Rows in Snacks (Easy): How many rows have Category "Snacks"?
- Largest units sold (Easy): The largest value in the "Units sold" column.
- Units sold for Snacks (Medium): Total of the Units sold column where Category is "Snacks".
- Snacks in Delhi (Medium): Total of the Units sold column where Category is "Snacks" and City is "Delhi".
- Best in Snacks (Medium): Largest value in the "Price (₹)" column where "Category" is "Snacks".
- Value of row 2 (Medium): Multiply the first-row Units sold by the first-row Price (₹).
- Look up Aarav Shah (Medium): The Units sold for "Aarav Shah" (use a lookup on the "Name" column).
- Total value (Hard): Sum of "Units sold" times "Price (₹)" for all rows.
- Share of Snacks (Hard): Share of the "Units sold" column that is "Snacks", rounded to 3 decimals.
- Share of the total "Price (₹)" with a locked range (Hard): Find what share of the total "Price (₹)" the first row has. Round it to 3 decimals.
- Above average? (Hard): "Above" if the "Price (₹)" value in the first row is above the average of the "Price (₹)" column, else "Below".
Worked example — excel tables ctrl t
Why
Rohan Patil in Pune and James O'Neill abroad both need a clear, repeatable way to use excel tables ctrl t on a Maple Bakery Co sheet without breaking totals.
What
Excel Tables Ctrl T is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for excel tables ctrl t.
- Enter a small made-up value for Rohan Patil using ₹ where money appears.
- Add a second value for James O'Neill using ₹ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once excel tables ctrl t is correct on one row, copy the same pattern down the column.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
