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

6. Tables, Sorting and Filtering

6.11 Writing Formulas in a Normal Range vs in an Excel Table

By Last updated:

Example first. Shraddha Bagale has five orders with Qty and Price and needs Amount and GST at 5%. In a range she writes =C2*D2 and fills it down. In a Table she writes =[@Qty]*[@Price] once. Then a sixth order arrives. Watch which total notices it.

Formulas: range vs Table — in 30 seconds

What: In a range you write A1 references and fill them down yourself. In a Table you write one structured reference and Excel fills the whole column (a calculated column).

Why: Lists grow. A range total like SUM(E2:E6) stops at row 6 and misses the new order. A Table total like SUM(tblSales[Amount]) always covers every row.

Tiny example: Range total =SUM(E2:E6) stays at ₹684 after a sixth order. The Table total =SUM(tblSales[Amount]) becomes ₹916.

First look at the data (before → after):

Formulas: range vs Table: before and after tables, fictional Blinkit Maharashtra data

Expected result — Table tblSales: the new row got both formulas by itself

Order ID City Qty Price (₹) Amount (₹) GST 5% (₹)
BLK-1001 Pune 2 32 64 3.20
BLK-1002 Nashik 3 90 270 13.50
AMN-1003 Pune 1 110 110 5.50
BLK-1006 Pune 5 30 150 7.50
BLK-1010 Nashik 1 90 90 4.50
AMN-1011 Kolhapur 4 58 232 11.60
Total — — — 916 45.80
Before table and formula as text

Before — Range: row 7 added, but E7 is empty and E8 still says =SUM(E2:E6)

A: Order ID B: City C: Qty D: Price (₹) E: Amount (₹)
BLK-1001 Pune 2 32 64
BLK-1002 Nashik 3 90 270
AMN-1003 Pune 1 110 110
BLK-1006 Pune 5 30 150
BLK-1010 Nashik 1 90 90
AMN-1011 Kolhapur 4 58 —
Total — — — 684

Formula / click path

Range:  E2: =C2*D2   F2: =E2*$H$1   (fill down by hand; H1 = 5%)
Table:  [Amount]: =[@Qty]*[@Price]   [GST]: =[@Amount]*GST_Rate
Total:  =SUM(tblSales[Amount])

Do it step by step:

  1. In the range, click E2, type =C2*D2 and press Enter.
  2. Double-click the fill handle of E2 to copy it down.
  3. In F2 type =E2*$H$1. Press F4 after clicking H1 to add the $ signs, then fill down.
  4. Now press Ctrl + T on the same data and name the Table tblSales.
  5. In the first empty column header type Amount, then in the first row type =[@Qty]*[@Price]. The whole column fills.
  6. Type a new order in the row just below the Table and see Amount and GST appear by themselves.

Side by side

Task Normal range Excel Table
Amount for one row =C2*D2 =[@Qty]*[@Price]
Fill to all rows Double-click the fill handle Automatic (calculated column)
Fixed rate cell =E2*$H$1 (the $ stops H1 from moving) =[@Amount]*$H$1 or a named cell =[@Amount]*GST_Rate
Column total =SUM(E2:E6) =SUM(tblSales[Amount])
Formula on another sheet =SUM(Sales!E2:E6) =SUM(tblSales[Amount]) (no sheet name needed)
Sixth order added in row 7 E7 stays empty; SUM(E2:E6) still says ₹684 Amount and GST appear; the total says ₹916

Absolute references inside a Table. [@Qty] already means "this row", so you never need $ for columns of the same Table. You still need $ (or a name) for a single cell outside the Table, like the GST rate in H1. A named cell such as GST_Rate is clearer than $H$1.

Two things that surprise beginners

  1. If you type a different formula in one cell of a calculated column, Excel marks it with a small green triangle (inconsistent formula). Fix the cell or undo, so the column stays the same.
  2. If you click cells while typing a formula in a Table, Excel writes structured references for you. If you prefer A1 references, type them by hand, but then the Table loses its biggest advantage.

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

  1. Build the range version and the Table version of the five orders above.
  2. Add the sixth order AMN-1011, Kolhapur, Qty 4, Price ₹58 below both.
  3. Compare the two totals and the GST column.
Answer

Range: E7 and F7 stay empty and =SUM(E2:E6) shows ₹684. Table: Amount ₹232 and GST ₹11.60 appear in the new row and the total shows ₹916 (GST total ₹45.80).

Ravindra Bagale's Tip

When a sheet will grow every week, start with Ctrl + T before you write the first formula. Converting later means rewriting every formula you already made.

Loading practice tasks...

  1. Line total (Easy): Multiply B2 by C2.
  2. Another line (Easy): Multiply B5 by C5.
  3. Total sales (Easy): Total of D2:D7.
  4. Pieces sold (Easy): Total of C2:C7.
  5. Tax on one line (Medium): Tax on D2 using $H$2.
  6. Tax on another line (Medium): Tax on D6 using $H$2.
  7. Price with tax (Medium): Price of B3 with tax.
  8. Average price (Medium): Average of B2:B7.
  9. Total tax (Hard): Total of D2:D7 × $H$2.
  10. Total with tax, rounded (Hard): Total sales plus tax, rounded to 2 decimals.
  11. Check a total (Hard): Does D4 equal B4*C4? (TRUE or FALSE)

See all practice tasks in the Task Hub →