Ravindra BagaleCourses & study guides मराठी Track your progress

6. Tables, Sorting and Filtering

6.10 Range vs Excel Table

By Last updated:

Example first. The same five orders can sit in a normal range (just cells) or in an Excel Table (Ctrl + T). They look almost the same, but they behave very differently when the list grows.

Range vs Excel Table — in 30 seconds

What: A range is any block of cells. An Excel Table is a range that Excel manages: it has a name, filter buttons, banded rows and grows by itself.

Why: When a new order is added, a Table pulls it into every formula, chart and PivotTable. A plain range does not, so totals quietly go wrong.

Tiny example: Five orders in A1:D6 total ₹916. After Ctrl + T the same cells become tblOrders, and =SUM(tblOrders[Amount]) keeps working when row 7 is added.

First look at the data (before → after):

Range vs Excel Table: before and after tables, fictional Blinkit Maharashtra data

Expected result — Excel Table tblOrders with a Total Row

Order ID ▾ City ▾ Qty ▾ Amount (₹) ▾
BLK-1001 Pune 2 64
BLK-1002 Nashik 3 270
AMN-1003 Pune 1 110
BLK-1004 Nagpur 2 240
AMN-1005 Kolhapur 4 232
Total — — 916

Expected result — What changed

Feature Range Table
Name A1:D6 tblOrders
Filter buttons No Yes
Grows with new rows No Yes
Before table and formula as text

Before — Normal range A1:D6 (no name, no filter buttons)

A: Order ID B: City C: Qty D: Amount (₹)
BLK-1001 Pune 2 64
BLK-1002 Nashik 3 270
AMN-1003 Pune 1 110
BLK-1004 Nagpur 2 240
AMN-1005 Kolhapur 4 232

Formula / click path

Click any cell › Insert › Table (Ctrl + T) › My table has headers › OK
Table Design › Table Name: tblOrders

Do it step by step:

  1. Type the five orders in A1:D6 with headers in row 1.
  2. Click any cell inside the data.
  3. Press Ctrl + T. Keep My table has headers ticked and click OK.
  4. In Table Design › Table Name, type tblOrders and press Enter.
  5. Tick Table Design › Total Row and choose Sum under Amount.
  6. To go back, click Table Design › Convert to Range and click Yes.

Range or Table: the full comparison

Feature Normal range Excel Table
Name An address like A1:D6 (or a defined name you create) A Table name like tblOrders
New row typed just below Not included in formulas, charts or pivots until you edit them Included automatically
Formulas A1 references such as =C2*D2, filled down by you A calculated column such as =[@Qty]*[@Price] fills itself
Header row when you scroll Scrolls away (unless you freeze panes) Header names replace the column letters
Filter buttons Add them with Data › Filter On by default
Formatting You format it A Table Style with banded rows
Total row You type =SUM(...) Table Design › Total Row (uses SUBTOTAL, so it follows filters)
Merged cells Allowed Not allowed inside a Table
Spilled formulas (FILTER, SORT, UNIQUE) Can spill Cannot spill inside a Table (#SPILL!)

When to use which

  • Use a Table for any list of records that grows: orders, staff, stock, invoices. This is the default for analysts.
  • Use a range for a small calculation block, a print layout such as a salary slip, a place where a dynamic-array formula must spill, or a layout that needs merged cells.

Converting both ways

  1. Range to Table: click inside the data and press Ctrl + T (or Insert › Table).
  2. Table to range: click inside the Table, then Table Design › Convert to Range › Yes.
  3. After converting back, Excel rewrites formulas like =SUM(tblOrders[Amount]) as normal references like =SUM(D2:D6). The colours stay; clear them with Home › Clear › Clear Formats if you want.

Practice task (try it before you open the answer):

  1. Type the five orders above in a new sheet and convert them to a Table named tblOrders.
  2. Turn on the Total Row and read the total.
  3. Type a sixth order under the Table: BLK-1006, Pune, 5, 150. Read the total again.
  4. Convert the Table back to a range and look at the Total Row formula.
Answer

Total before: ₹916. After the sixth order: ₹1,066 (the Table grew by itself). After Convert to Range, the total cell shows =SUBTOTAL(109,D2:D7) instead of a structured reference.

Ravindra Bagale's Tip

Name every Table the moment you create it: tbl plus what it holds, like tblOrders. Table1, Table2 and Table3 mean nothing in a formula six months later.

Loading practice tasks...

  1. Total stock units (Easy): Add up Stock units in D2:D11.
  2. Average unit cost (Easy): Average of E2:E11, rounded to 2 decimals.
  3. Rows in Antibiotic (Easy): How many rows have Category "Antibiotic"?
  4. Largest stock units (Easy): The largest value in D2:D11.
  5. Stock units for Antibiotic (Medium): Total of D2:D11 where B is "Antibiotic".
  6. Antibiotic in Milan (Medium): Total of D2:D11 where B is "Antibiotic" and C is "Milan".
  7. Best in Antibiotic (Medium): Largest E2:E11 value where B is "Antibiotic".
  8. Value of row 2 (Medium): D2 times E2.
  9. Look up Liam O'Brien (Medium): The Stock units for "Liam O'Brien" (use a lookup on column A).
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share of Antibiotic (Hard): Share of D2:D11 that is "Antibiotic", rounded to 3 decimals.
  12. Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
  13. Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".

See all practice tasks in the Task Hub →