9.1 What Is an Excel Table?

An Excel Table is a structured data container with specific characteristics that distinguish it from an ordinary range of data:

9.2 Creating a Table

Requirements Before Creating a Table

• Your data must have column headers in the first row — each column must have a unique header name
• No blank rows within the data — blank rows break the Table boundary
• No merged cells in the data range
• Each column should contain the same type of data throughout (all text, all numbers, all dates — not mixed)

Method 1 — Format as Table (Ribbon)

1. Click anywhere inside your data range
2. Home → Styles group → Format as Table
3. A gallery of table styles appears (Light, Medium, Dark — see Section 9.4)
4. Click any style → the "Format as Table" dialog appears asking: "Where is the data for your table?"
5. Excel auto-detects the range — verify it covers the correct area including the header row
6. Ensure "My table has headers" is ticked if your data has a header row
7. Click OK

Method 2 — Insert Table (Recommended)

1. Click anywhere inside your data range
2. Insert tab → Tables group → Table — or press Ctrl+T
3. The Create Table dialog appears → verify the range → tick "My table has headers" → click OK
4. The Table is created with the default Table style (usually blue or the last used style)

What Happens When a Table is Created

• The range is formatted with the chosen table style (alternating row colours, styled header)
• AutoFilter dropdown arrows ▼ appear on every header cell
• The Table is given an automatic name (Table1, Table2, etc.)
• The Table Design contextual tab appears in the Ribbon whenever the cursor is inside the Table
• The Table name appears in the Name Box when a Table cell is active

9.3 The Table Design Tab

When any cell inside a Table is selected, the Table Design contextual tab appears at the right end of the Ribbon. This tab contains all Table management tools.

Properties Group

Tools Group

Table Style Options Group

9.4 Table Styles

A Table Style is a pre-built combination of formatting for the header row, banded rows, total row, and borders. There are 60 built-in styles in three categories, plus the ability to create custom styles.

Applying a Table Style

1. Click anywhere inside the Table
2. Table Design → Table Styles group → the gallery shows a small selection. Click the More ▼ button to expand the full gallery.
3. Hover over any style to preview it on the Table in real time (Live Preview)
4. Click to apply

The Three Style Categories

Creating a Custom Table Style

1. Table Design → Table Styles → New Table Style…
2. Name the style (e.g., "Corp Table Style")
3. Select each Table Element in the list (Whole Table, Header Row, First Column, Total Row, Banded Rows, etc.) and click Format… to define the formatting for that element
4. Set the formatting (fill colour, border, font) → OK for each element
5. Tick "Set as default table style for this document" if you want every new Table in this workbook to use this style
6. Click OK → the style appears at the top of the gallery in a "Custom" section

Removing Table Style (Keeping Table Structure)

• Table Design → Table Styles gallery → scroll to the very beginning → select None
• All visual formatting is removed but the Table remains fully functional (still has AutoFilter, still expands, still has structured references)

Clearing Table Formatting Completely

• Apply "None" style → then Table Design → Convert to Range → this removes both the style AND the Table structure

9.5 The Total Row

The Total Row is a dedicated row at the bottom of a Table that automatically provides aggregate calculations for each column — with a dropdown selector for each cell so you can choose the appropriate calculation.

Enabling the Total Row

• Table Design → Table Style Options → tick Total Row
• A "Total" row appears below the last data row, separated visually from the data
• By default, the rightmost numeric column shows a SUM; all other columns are blank

Choosing the Calculation for Each Column

1. Click any cell in the Total Row
2. A dropdown arrow ▼ appears → click it to choose the aggregation:

How the Total Row Works Technically

Total Row calculations use the SUBTOTAL function, which only counts visible rows:

This means when you apply a filter to the Table, the Total Row automatically updates to show the total/average/count of only the filtered rows — not the entire dataset. This is one of the most useful features for data analysis.

9.6 Filtering & Sorting in Tables

Every Table has AutoFilter dropdowns on the header row by default. These allow you to filter and sort the data without any setup.

Sorting a Table Column

1. Click the dropdown arrow ▼ in the header of the column to sort by
2. At the top of the dropdown: Sort A to Z (ascending) or Sort Z to A (descending)
3. For numbers: Sort Smallest to Largest or Largest to Smallest
4. For dates: Sort Oldest to Newest or Newest to Oldest
5. The entire Table row moves together — data integrity is maintained across all columns

Filtering a Table Column

1. Click the dropdown ▼ on the column to filter
2. Below the sort options: a list of all unique values in the column appears with checkboxes
3. Tick the values to show → untick values to hide → click OK
4. The rows not matching the filter are hidden (not deleted). The row numbers of hidden rows turn blue to indicate filtering is active.
5. The header with an active filter shows a filter icon (🏬) instead of the standard dropdown arrow

Filter Types (Number/Date/Text Filters)

• For number columns: the dropdown includes Number Filters → Greater Than, Less Than, Between, Top 10, Above Average, Below Average, Custom Filter…
• For date columns: Date Filters → Yesterday, Today, Tomorrow, This Week, Last Month, Next Quarter, Before, After, Between, and date grouping options
• For text columns: Text Filters → Contains, Does Not Contain, Begins With, Ends With, Equals, Does Not Equal

Clearing Filters

• Clear one column's filter: click the column's filter dropdown ▼ → Clear Filter From "[Column Name]"
• Clear all filters: Data tab → Sort & Filter → Clear — or — Home → Editing → Sort & Filter → Clear

Search Filter

• The dropdown has a search box at the top — type to instantly filter the list of values to only those containing the typed text
• Useful for columns with many unique values (e.g., a city column with 50+ cities — type "Johan" to quickly find "Johannesburg")

9.7 Adding & Removing Rows and Columns

Adding a Row to the Table

Adding a Column to the Table

• Type to the right: click the cell immediately to the right of the last column header → type a column name → press Enter. The Table automatically expands to include the new column.
• Right-click → Insert: right-click any column header within the Table → Insert → Table Columns to the Left

Deleting Rows from the Table

• Right-click the row within the Table → Delete → Table Rows
• — or — Home → Cells → Delete → Delete Table Rows
• Only the Table rows are deleted (not the entire worksheet rows, unless you use the worksheet Delete option)

Deleting Columns from the Table

• Right-click the column → Delete → Table Columns

9.8 Structured References

Structured references are formula references that use the Table name and column name instead of cell addresses. They are one of the most powerful aspects of Excel Tables — making formulas readable, self-documenting, and automatically adjustable when the Table grows.

How Structured References Work

Structured Reference Syntax

Calculated Columns

A calculated column is a new column you add to a Table where the formula automatically fills the entire column with the same formula, and automatically extends when new rows are added:

1. Click the header of the empty column to the right of the Table → type a column name (e.g., "Net Pay") → Enter
2. Click the first data cell in the new column → type a formula using @ references: e.g., =[@GrossPay]-[@Deductions]
3. Press Enter — Excel automatically fills the same formula down to every row in the Table
4. Every time a new row is added to the Table, the formula extends to that row automatically

9.9 Table Navigation Shortcuts

Selecting Table Parts with Keyboard / Mouse

• Select entire column: click the top edge of the column header (the cursor becomes a downward arrow ▼) — first click selects the column data; second click includes the header
• Select entire row: click the left edge of the row (the cursor becomes a right-pointing arrow →)
• Select entire Table: click the top-left corner of the Table (at the intersection of the top edge and left edge)

9.10 Converting & Removing Tables

Convert Table to Range (Remove Table Structure, Keep Data)

1. Click anywhere inside the Table
2. Table Design → Tools → Convert to Range
3. A confirmation dialog appears: "Do you want to convert the table to a normal range?" → click Yes
4. The Table structure is removed (no more structured references, no auto-expansion, no Total Row) but the data and most visual formatting remain
5. The Table Design tab disappears from the Ribbon

Delete Table Data (Keep the Formatted Empty Table)

1. Select all Table data rows (not the header or total row)
2. Right-click → Delete → Table Rows — or — press Delete to clear contents only

Delete the Entire Table Including Headers

1. Click inside the Table → press Ctrl+A twice to select the entire Table including headers
2. Home → Cells → Delete → Delete Rows — or — right-click → Delete → Delete Rows

9.11 Table Best Practices

Always Name Your Tables

Rename every Table immediately after creating it — before writing any formulas. Table1, Table2 become confusing quickly in workbooks with multiple tables. Use descriptive names:

One Table Per Sheet (Preferred)

While it is possible to have multiple Tables on one sheet, it is generally best to keep one primary Table per sheet. Multiple Tables on one sheet can cause confusion with structured references, filtering (filtering one Table does not filter the others), and printing.

Keep Headers Descriptive and Clean

• Use clear, concise column headers that describe the data type exactly
• Avoid special characters in headers that will appear in structured references: [ ] # @ are all used in structured reference syntax and can cause formula errors
• Good: "Employee Name", "Gross Salary", "Leave Days" • Avoid: "Name [Full]", "#Days", "@Rate"

Use Calculated Columns for Consistency

Any column that derives its value from other columns should be a calculated column with a structured reference formula. This guarantees every row always has the correct formula — unlike a range where you might accidentally delete or change a formula in one row.

Refresh PivotTables After Adding Data

When a Table is the source for a PivotTable, new rows added to the Table appear in the PivotTable only after you refresh it: right-click the PivotTable → Refresh — or — PivotTable Analyse → Data → Refresh.

9.12 Quick Self-Check

Q1: You have a salary dataset with 200 rows in a plain range. You apply a filter to show only employees in the "Finance" department and the SUM formula at the bottom still shows the total of all 200 rows, not just the Finance department. How do you fix this?

✓ The SUM formula uses a standard SUM function which adds ALL rows regardless of filtering. Convert the range to a Table (click inside data → Ctrl+T → OK) → enable Total Row (Table Design → tick Total Row) → click the Total Row cell in the Salary column → select Sum from the dropdown. The Total Row uses SUBTOTAL which only sums visible rows. Now when you filter to show Finance only, the Total Row shows the Finance department's salary total only. Alternatively, replace the existing SUM formula with =SUBTOTAL(109, C2:C201) which has the same filter-aware behaviour.

Q2: You need a formula outside the Table that sums all values in the "Revenue" column of your Table named "SalesData". You need the formula to automatically include new rows when data is added to the Table. Write the formula.

✓ =SUM(SalesData[Revenue]). This structured reference always includes every data row in the Revenue column of the SalesData Table, regardless of how many rows are added. Unlike =SUM(B2:B201) which would miss rows added below row 201, the structured reference automatically covers all current and future rows. To make the formula work, the Table must be named "SalesData" — rename it in Table Design → Table Name field if needed.

Q3: You add a "Net Pay" calculated column to your employee Table. The formula in the first row is =[@GrossPay]-[@Deductions]-[@Tax]. Excel shows an AutoCorrect suggestion to fill the column. What happens when you accept it, and what happens when a new employee row is added later?

✓ When you accept the AutoCorrect suggestion, Excel automatically fills the =[@GrossPay]-[@Deductions]-[@Tax] formula to every existing row in the Table column simultaneously. The @ symbol references the current row — so each row's formula calculates that specific row's GrossPay minus Deductions minus Tax. When a new employee row is added to the Table (by typing below the last row, pressing Tab from the last cell, or inserting a row), the Net Pay formula automatically populates in the new row. You never need to manually copy the formula down — the Table handles it automatically. This is one of the most powerful advantages of calculated columns over manual formula copying.

Q4: What is the difference between "Delete Table Rows" and pressing the Delete key when rows are selected in a Table?

✓ "Delete Table Rows" (right-click → Delete → Table Rows, or Home → Cells → Delete → Delete Table Rows) physically removes the selected rows from the Table and the worksheet — the rows below shift up, the Table shrinks, and the data is gone. Pressing the Delete key clears the content of the selected cells but leaves the rows in place — the rows remain as blank rows inside the Table. The Table does NOT shrink. Blank rows inside a Table are generally undesirable because they break data continuity and can affect calculations. Use Delete Table Rows to remove data permanently; use the Delete key only to clear cell content when you intend to replace it with new data.

Q5: Your workbook has two sheets: one with a Table named "SalesData" and another with summary formulas. What formula would you use on the summary sheet to count the number of rows in the SalesData Table where the "Region" column equals "Gauteng"?

✓ =COUNTIF(SalesData[Region],"Gauteng"). The structured reference SalesData[Region] refers to all data cells in the Region column of the SalesData Table — on any sheet. The COUNTIF function counts cells where the value equals "Gauteng". This formula works correctly from a different sheet. Alternative for case-sensitivity or multiple criteria: =COUNTIFS(SalesData[Region],"Gauteng"). The structured reference automatically expands when new rows are added to the Table — no need to update the formula range.

Q6: When is it appropriate to use "Convert to Range" on a Table, and what are the consequences?

✓ Use Convert to Range when: (1) the Table structure is causing problems with other operations (some advanced sorting or macro operations work better with plain ranges); (2) you are submitting the file to a system or person that has difficulty with Tables; (3) the data is finalized and will never need to grow again, so the auto-expansion benefit is no longer relevant. Consequences: all structured references in formulas are immediately converted to standard cell references (they still work but lose auto-expansion); the Total Row becomes a regular row with SUBTOTAL formulas; the Table Design tab disappears; AutoFilter remains but is no longer linked to a Table; future rows added will NOT be included in formulas automatically. The visual formatting (colours, borders from the Table Style) usually remains. This action cannot be undone with Ctrl+Z.

✓ Module 9 Complete — You Have Learned:

• Excel Table vs plain range — 9-feature comparison (auto-filter, auto-format, auto-expand, Total Row, structured references, named range, dynamic PivotTable source, column header visibility on scroll)
• Table requirements — unique column headers, no blank rows, no merged cells, consistent data types per column
• Creating Tables — Format as Table (Home → Styles) and Insert → Table (Ctrl+T ★); Create Table dialog; what happens on creation (style, AutoFilter arrows, Table name, Table Design tab)
• Table Design tab — Properties (Table Name → rename immediately, Resize Table); Tools (Summarize with PivotTable, Remove Duplicates, Insert Slicer, Export, Convert to Range); Table Style Options (Header Row, Total Row, Banded Rows, Banded Columns, First Column, Last Column, Filter Button)
• Table Styles — 3 categories (Light, Medium, Dark); creating custom styles (New Table Style with per-element formatting); "None" to remove style while keeping structure
• Total Row — enabling; 10 aggregation options per column (Sum, Average, Count, Count Numbers, Max, Min, StdDev, Var, More Functions, None); uses SUBTOTAL — only visible rows counted; auto-updates when filter is applied
• Filtering — Sort A-Z/Z-A; checkbox filter; Number Filters (Greater Than, Less Than, Between, Top 10, Above/Below Average); Date Filters (Yesterday/Today/This Week etc.); Text Filters (Contains, Begins With etc.); search box; clearing individual column filter vs clearing all filters
• Adding rows (type below, Tab from last cell, right-click Insert, drag resize handle); adding columns (type to right, right-click Insert); deleting Table Rows and Table Columns (right-click)
• Structured references — TableName[Column] syntax; special specifiers (#Headers, #Totals, #All); current-row @ reference for calculated columns; auto-expansion advantage over fixed cell references; COUNTIF/SUMIF with structured references
• Calculated columns — enter formula in one cell → auto-fills entire column; extends automatically to new rows; =[@Col1]-[@Col2] row-level calculation syntax
• Table navigation shortcuts — Ctrl+T, Tab for new row, Ctrl+A (3-step selection), Alt+▼ for dropdown; column/row selection by clicking edges
• Convert to Range — removes structure (auto-expansion, Total Row, structured refs convert to cell refs); warning: cannot be undone; when to use vs keep as Table
• Best practices — always name Tables immediately; one Table per sheet preferred; clean header names (avoid [ ] # @); calculated columns for formula consistency; refresh PivotTable after adding data