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

6. Tables, Sorting and Filtering

Practice and Answer Key: Ranges, Tables and Slicers

By Last updated:

Goal of this practice: Turn a range into a Table, add a calculated column, filter it, slice it and watch the totals.

Suppose Rani runs two fictional dark stores, Kolhapur and Solapur. She pastes eight orders into a sheet (A1:E9) and wants a sheet she can filter with clicks. All data is fictional practice data.

A: Order ID B: City C: Platform D: Qty E: Price (₹)
AMN-3001 Kolhapur Amazon Now 2 120
BLK-3002 Solapur Blinkit 1 450
BLK-3003 Kolhapur Blinkit 3 95
AMN-3004 Solapur Amazon Now 4 60
BLK-3005 Kolhapur Blinkit 1 380
AMN-3006 Solapur Amazon Now 2 210
BLK-3007 Kolhapur Blinkit 5 40
BLK-3008 Solapur Blinkit 2 175

Exercise (one under the other):

  1. Convert A1:E9 to a Table named tblP6.
  2. Add a calculated column Amount = Qty × Price.
  3. Turn on the Total Row and show the Sum of Amount.
  4. Filter Amount with Number Filters › Greater Than 300 and read the Total Row. Clear the filter.
  5. Insert a City slicer and a Platform slicer. Click Kolhapur, then add Blinkit on the Platform slicer. Read the Total Row.
  6. Clear the slicers and add the order AMN-3009, Kolhapur, Amazon Now, 3, 150 in the row below the Table.
  7. Convert the Table back to a range and look at the Amount formula in F2.

Expected result (check your numbers):

Step What you should see
Amount column 240, 450, 285, 240, 380, 420, 200, 350
Total Row (all 8 rows) ₹2,565
Amount > 300 4 rows (BLK-3002, BLK-3005, AMN-3006, BLK-3008), total ₹1,600
Slicer Kolhapur 4 rows, total ₹1,105
Slicer Kolhapur + Blinkit 3 rows, total ₹865
After the 9th order Amount 450 appears by itself; total ₹3,015
After Convert to Range F2 shows =D2*E2 (structured references become A1 references)
Answer key (open after you try) 
F2 (calculated column):  =[@Qty]*[@Price]
Total Row:               =SUBTOTAL(109,[Amount])
Filter:                  Amount ▾ › Number Filters › Greater Than… › 300
Slicers:                 Table Design › Insert Slicer › City, Platform
Back to range:           Table Design › Convert to Range › Yes

Common mistakes:

What you see Why Fix
Total Row does not change when filtered You typed =SUM(F2:F9) instead of using the Total Row Use the Total Row or SUBTOTAL(109, …)
New row not inside the Table A blank row was left between the Table and the new order Type in the row directly below the last row
Insert Slicer is greyed out The active cell is outside the Table Click a cell inside tblP6 first
Amount column shows a green triangle in one cell A different formula was typed in that cell Click the warning and choose Restore to Calculated Column Formula

Ravindra Bagale's rule: if a list will grow, it is a Table from the first minute.

Thodkyaat sangaycha tar (quick recap)

Data milala ki pahila Ctrl + T aani Table la nav; structured references vachayla sope; Total Row filter pramane badalto; sort kartana poora data ekatra; AutoFilter jhatpat, Advanced Filter AND/OR sathi; filtered totals sathi SUBTOTAL, errors sathi AGGREGATE; Subtotal command aadhi sort; lapvaycha asel tar Hide nako, Group. Aata pudhe jaauya – PivotTables!

Loading practice tasks...

  1. Total units (Easy): Total of D2:D8 with SUBTOTAL 109.
  2. Count orders (Easy): Count A2:A8 with SUBTOTAL 103.
  3. Order value (Easy): Value of row 2 (D2*E2).
  4. Highest price (Easy): Highest price in E2:E8.
  5. Laptop units (Medium): Units of "Laptop".
  6. Shipped orders (Medium): How many orders are "Shipped"?
  7. East shipped units (Medium): Units in East that are Shipped.
  8. Average price (Medium): Average of E2:E8 with SUBTOTAL.
  9. Total sales value (Hard): Total of D2:D8 × E2:E8.
  10. Value not cancelled (Hard): Total units × price where Status is not "Cancelled".
  11. Pending laptop units (Hard): Units of Laptop that are Pending.

See all practice tasks in the Task Hub →