Chapter 6: Working with Tables

Working with Tables — one of the most powerful and time-saving features in modern Excel (Microsoft 365 / Excel 2021+ / 2025–2026 versions).

An Excel Table is not just a formatted range of cells. It’s a smart, dynamic object that:

• Automatically expands when you add rows/columns
• Keeps formatting consistent
• Lets you write cleaner formulas with structured references (no more A1:B100!)
• Adds built-in totals, filters, sorting
• Makes your data much easier to analyze

I’ll explain everything very slowly, step by step, like a patient teacher sitting next to you. We’ll use a real-life example: a small tea shop’s daily sales log with columns: Date, Item, Quantity, Price, Total, Customer City.

Let’s make your data smart!

1. Creating and Formatting Excel Tables

Step-by-step to create a table:

1. Enter your data with clear headers in the first row (very important!).
   • No blank rows/columns inside the data.
   • Headers should be unique and descriptive (e.g., “Date”, “Item”, not “A”, “B”).

Example data (type this in a new sheet called “Sales”):

2. Select any cell inside your data (or the entire range A1:F10).
3. Go to Home tab → Styles group → Format as Table (or Insert tab → Table button)
4. Choose any style you like (light blue, green, etc.) — you can change later.
5. In the dialog box:
   • My table has headers → check this (almost always yes!)
   • Click OK

→ Magic happens!

• Your data now has filter arrows in headers
• Alternating row colors (banded rows)
• Table gets a name (default: Table1, Table2…)
• Borders and formatting apply automatically

Change table style anytime:

• Click anywhere in the table → Table Design tab appears (very important new tab!)
• Table Styles group → pick a new design (e.g., Medium 9, Dark style)
• Check/uncheck Banded Rows, Banded Columns, Header Row, Total Row

Rename the table (very useful for formulas):

• Table Design tab → Properties group → Table Name box
• Change “Table1” to something meaningful → e.g., DailySales

2. Table Features (Structured References, Total Row)

A. Structured References – The biggest advantage!

Instead of =SUM(E2:E100), you write clean, readable formulas like:

• =SUM(DailySales[Total])
• =AVERAGE(DailySales[Qty])

Parts of structured reference:

• TableName → DailySales
• [ColumnName] → [Total], [Qty], [Price]
• Special: [@ColumnName] → current row only (like “this row’s Total”)

Example – Fix Total column:

1. In cell E2 (first data row), delete old formula
2. Type = → click C2 (Qty) → type *** ** → click D2 (Price) → Enter → Formula becomes =[@Qty]*[@Price] (beautiful!)
3. Drag down or just press Enter — table auto-fills!

If you add a new row at bottom → formula auto-copies!

B. Total Row – Instant summaries

1. Click anywhere in table → Table Design tab
2. Check Total Row (bottom row appears)
3. In the total row cells → click dropdown → choose Sum, Average, Count, Max, etc.
   • Example: Under Total column → Sum → shows grand total of all sales
   • Under Qty → Average → average quantity sold

Magic: When you filter data (next section), the total row automatically updates to show only visible rows!

3. Sorting and Filtering Data

Tables make sorting/filtering effortless.

Sorting:

• Click filter arrow in any header (e.g., Total)
• Sort Largest to Smallest → see best-selling items first
• Sort A to Z on City → group by location

Multi-level sort:

1. Click filter arrow → Sort by Color or custom
2. Or: Data tab → Sort button → add levels (e.g., first by City, then by Total descending)

Filtering:

• Click arrow → checkboxes appear
• Uncheck items → hide rows instantly
• Text Filters → Contains “Mumbai”, Begins With “Ch”, etc.
• Number Filters → Greater Than 1000, Top 10, Between 10 and 50
• Date Filters → This Month, Next Week, etc.

Example:

• Filter City → only “Mumbai” → see only Mumbai sales
• Total row now shows Mumbai-only sum/average — super useful for reports!

Clear filter: Click arrow → Clear Filter from “City” Or Data tab → Clear

4. Removing Duplicates

Very common: duplicate entries from copy-paste or multiple entries.

How to remove duplicates in a table:

1. Click anywhere in your table
2. Go to Table Design tab → Tools group → Remove Duplicates (or Data tab → Data Tools → Remove Duplicates — same thing)
3. Dialog box opens:
   • Check/uncheck columns to consider for duplicates
   • Example: If you want unique Item + Date combinations → check only Date and Item
   • Uncheck City, Qty, etc. if they can differ
4. Click OK → Excel removes duplicate rows, keeps first occurrence

Important notes:

• Permanent — copy table first (Ctrl+C → paste elsewhere) if unsure!
• Keeps first row of duplicates, deletes others
• Works perfectly on tables (auto-adjusts)

Example: Suppose you accidentally entered “Chai” twice on 01-Jan-26. Select Date + Item columns in Remove Duplicates → one row remains.

Quick Summary Table – Chapter 6

Homework / Practice (Do This Today!)

1. In your practice file → new sheet “SalesLog”
2. Enter the example data above (at least 10–15 rows, some duplicates!)
3. Select data → Ctrl + T → create table → name it TeaSales
4. In Total column → use =[@Qty]*[@Price] → drag or let auto-fill
5. Turn on Total Row → Sum on Total, Average on Qty
6. Filter City → only “Mumbai” → watch total change!
7. Sort Total descending → see top sellers
8. Add duplicate row (same Date + Item) → Remove Duplicates using Date + Item
9. Add new row at bottom (type below table) → watch table expand + formulas copy!