Excel Advanced - Organising Data with Tables

0 of 8 activities completed

Microsoft Excel Advanced · Lesson 03

Organising Data with Tables

Transform an ordinary range into a dependable Excel Table, then sort, filter, summarise and remove duplicates without damaging the source data.

Learning outcomes

Create and name TablesConvert a clean range and apply a meaningful table name.

Build calculated columnsUse structured references that automatically fill and expand.

Sort and filter safelyApply multi-level, custom and value-based filters.

Control duplicatesExtract unique records and remove duplicates with before-and-after checks.

Practice dataset

The embedded dataset contains Malaysian retail transactions. It deliberately includes repeated customers and one exact duplicate so you can distinguish legitimate repeat business from duplicated records.

28source rows

27unique Transaction IDs

RM 127,075sales before cleanup

RM 122,775sales after cleanup

Recommended workflow: download the Excel file, open the Practice Data sheet and save a working copy. The workbook contains raw fields only; you will create the Sales column during the tutorial.

1. Prepare the range

ACTIVITY 1

Inspect before converting

1. Select any cell in the dataset.
2. Press Ctrl+End; the last used cell should be I29.
3. Confirm row 1 contains one header per column, with no blank headers, merged cells or subtotal rows.
4. Confirm Transaction ID is stored as text and Date as a real Excel date.

I confirmed the source range is A1:I29.

ACTIVITY 2

Create and name an Excel Table

1. Click inside the range and press Ctrl+T (or Insert → Table).
2. Confirm A1:I29 and tick My table has headers.
3. On Table Design, replace the default name with tblRetailSales.
4. Choose a medium table style with visible banded rows.

Table names cannot contain spaces and must begin with a letter, underscore or backslash. Descriptive names make formulas easier to audit.

My table is named tblRetailSales.

2. Calculated columns and structured references

ACTIVITY 3

Calculate Sales

1. In the first empty header cell, J1, type Sales.
2. In J2, enter the formula below and press Enter.
3. Excel should fill the entire Table column automatically.
4. Format Sales as Currency or Accounting with two decimals.

=[@Quantity]*[@[Unit Price]]

Checkpoint: the first row is RM3,600 and the source total is RM127,075.

The Sales formula filled all 28 rows.

ACTIVITY 4

Add a Total Row

1. Select the Table, then Table Design → Total Row.
2. In the Sales total cell, choose Sum.
3. In Quantity, choose Sum; the result is 246 units.
4. Filter the Table and observe that totals respond to visible rows because Excel uses SUBTOTAL.

=SUBTOTAL(109,tblRetailSales[Sales])

The Total Row responds to filters.

3. Sorting and filtering

ACTIVITY 5

Apply a multi-level sort

1. Open Data → Sort and tick My data has headers.
2. Sort by Region, A to Z.
3. Add a level: Sales, Largest to Smallest.
4. Verify rows remain intact—never sort only one column in a transaction table.

Checkpoint: within Central, transaction TXN-1026 (RM8,400) should appear before other Central transactions.

I completed the two-level sort.

ACTIVITY 6

Filter high-value completed orders

1. Filter Status to Completed.
2. On Sales, choose Number Filters → Greater Than or Equal To → 5000.
3. Read the filtered Total Row.

Expected result: 8 transactions with total Sales of RM51,140.

Optional custom-list sort

To display regions in a business-defined order, use Sort → Order → Custom List and enter: Central, Northern, Southern, Eastern. Custom lists are preferable to manually moving rows.

My high-value filter returns 8 rows.

4. Advanced Filter and unique records

ACTIVITY 7

Extract unique customers

Advanced Filter can copy a distinct list without altering the Table.

1. Copy the Customer header to L1.
2. Select the Customer column including its header.
3. Choose Data → Advanced → Copy to another location.
4. Set Copy to L1, tick Unique records only, then OK.

Checkpoint: there are 17 unique customers plus the header.

Microsoft 365 alternative

=SORT(UNIQUE(tblRetailSales[Customer]))

This dynamic-array result updates as the Table expands. Advanced Filter remains suitable for older Excel versions and fixed extracts.

I extracted 17 unique customer names.

5. Remove duplicates safely

ACTIVITY 8

Remove only the exact duplicate

1. Save a backup or duplicate the worksheet.
2. Clear all filters and record the controls: 28 rows, 246 units, RM127,075.
3. Select the Table and choose Data → Remove Duplicates.
4. Tick all original columns (Transaction ID through Status). You may include Sales because it is derived consistently.
5. Click OK. Excel should report 1 duplicate value found and removed; 27 unique values remain.

After cleanup: 27 rows, 241 units and RM122,775. The removed duplicate is the second copy of TXN-1012.

Why not select Customer alone? A customer may place several valid orders. Duplicate removal must use the fields that define a duplicate business record—normally a unique Transaction ID, or all relevant transaction fields.

My cleaned data has 27 rows and RM122,775 Sales.

6. Make the Table expand

Click the first row immediately below the Table and enter a new transaction. The Table should expand, inherit its formatting and calculate Sales automatically. If it does not, check File → Options → Proofing → AutoCorrect Options → AutoFormat As You Type → Include new rows and columns in table.

Transaction ID	Date	Region	Customer	Product	Category	Quantity	Unit Price	Status
TXN-1028	2026-04-05	Eastern	Rimba Trading	Tablet	Electronics	3	1850	Completed

Expected Sales: RM5,550. A chart or PivotTable based on the Table can include new rows after refresh without redefining its source range.

Troubleshooting

Formula did not fill
Confirm the formula was entered inside the Table and calculated-column auto-fill is enabled.

Filter misses RM5,000
Use “Greater Than or Equal To,” and confirm Sales contains numbers rather than text.

Wrong duplicate count
Undo immediately. Clear filters, select the full Table and use all fields that define a record.

Structured reference shows #REF!
Check the Table and column names. Renaming a referenced column incorrectly can break formulas.

Knowledge check

1. Why is a Table better than a fixed range?

It expands with new data, carries formulas and formatting down, provides filters, and offers readable structured references.

2. What is the safest way to sort?

Sort from within the Table or select the full dataset so complete records move together.

3. Advanced Filter or Remove Duplicates?

Use Advanced Filter to create a separate unique extract. Use Remove Duplicates only when you intend to delete duplicate rows from the source, after making a backup and recording controls.

4. Why does a Table Total Row use SUBTOTAL?

SUBTOTAL can ignore filtered-out rows, so the total reflects visible data.

Completion checklist

• Table name: tblRetailSales
• Calculated column: Sales
• High-value completed filter: 8 rows / RM51,140
• Unique customers: 17
• Cleaned controls: 27 rows / 241 units / RM122,775

Next lesson: use clean, structured Table data to build clear comparisons, trends and relationships with charts.

Lesson 03 · Organising Data with Tables · Excel Advanced