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 |
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.