6. Tables, Sorting and Filtering
6.1 Excel Tables (Ctrl + T)
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
Khup students Table banvtat pan tyala nav detach nahit – Table1, Table7 ase nava formulas madhe kahich sangat nahit. Table banavlyavar lagech tbl ne suru honara arthapurna nav dya (tblOrders, tblStores). Aani Table chya aat rikami rows/columns theu naka.
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.