Creating Tables, Sorting & Filtering in Microsoft Excel

https://www.youtube.com/watch?v=O28-xL5YGkE

1. Introduction

Tables, Sorting, and Filtering are essential Excel skills used daily in:

✔ Finance departments

✔ Bookkeeping

✔ Data entry roles

✔ Business analysis

✔ Reporting & dashboards

These tools help you organise, analyse, and control large sets of data quickly and professionally.

2. What Is a Table in Excel?

A Table in Excel is a structured data range that:

• Automatically formats data

• Adds filter buttons

• Expands automatically when new data is added

• Makes formulas easier to manage

Tables are highly recommended in professional finance work.

3. Creating a Basic Table

Example Data:

Date	Customer	Invoice No	Amount
01/01/26	ABC Ltd	INV001	2,000
05/01/26	XYZ Ltd	INV002	3,500
10/01/26	LMN Ltd	INV003	1,800

Step-by-Step: Create a Table

Step 1:

Select your data (including headings).

Step 2:

Click the Insert tab.

Step 3:

Click Table.

Step 4:

Ensure “My table has headers” is ticked.

Step 5:

Click OK.

Your data is now a structured table.

4. Benefits of Using Tables

✔ Automatic filtering

✔ Professional formatting

✔ Easy sorting

✔ Automatic expansion

✔ Easier reporting

Finance Tip: Always convert raw transaction data into a table.

5. Renaming a Table (Best Practice)

1. Click inside the table.

2. Go to Table Design.

3. Change the Table Name (top left).

Example:

• Sales Data

• Invoice Register

• Expenses2026

This is useful when using formulas later.

6. Sorting Data

Sorting allows you to arrange data in order.

Sort by Amount (Lowest to Highest)

Step 1:

Click the dropdown arrow in the Amount column.

Step 2:

Select:

• Sort Smallest to Largest

OR

• Sort Largest to Smallest

Sort by Date

Click Date column dropdown → Choose:

• Oldest to Newest

• Newest to Oldest

Finance Application:

✔ Sort unpaid invoices

✔ Sort highest expenses

✔ Sort latest transactions

7. Multi-Level Sorting

Example: Sort by Customer, then by Date.

Step 1:

Click anywhere in table.

Step 2:

Go to Data tab.

Step 3:

Click Sort.

Step 4:

Choose:

• Sort by Customer

• Then by Date

This is useful in Sales Ledger or Purchase Ledger management.

8. Filtering Data

Filtering allows you to show specific data and hide the rest.

Example: Show only invoices above £2,000.

Basic Filter

1. Click dropdown arrow in Amount column.

2. Untick “Select All”.

3. Tick the amounts you want.

4. Click OK.

Number Filter (Advanced)

1. Click dropdown in Amount column.

2. Choose Number Filters.

3. Select:

   • Greater Than

   • Less Than

   • Between

Example: Show invoices greater than 2,000.

Date Filter

1. Click dropdown in Date column.

2. Choose Date Filters.

3. Select:

   • This Month

   • Last Month

   • Between specific dates

Very useful for month-end reporting.

9. Clearing Filters

To remove filters:

Click dropdown → Select Clear Filter

OR

Go to Data tab → Click Clear

Always clear filters before saving reports (best practice).

10. Using Table Total Row

Tables can automatically calculate totals.

Step 1:

Click inside the table.

Step 2:

Go to Table Design.

Step 3:

Tick Total Row.

Excel will add a total row at the bottom.

You can choose:

• Sum

• Average

• Count

• Max

• Min

Used daily in finance reporting.

11. Example Business Scenario

You work in Accounts Receivable.

You receive 200 invoices.

You need to:

✔ Sort by customer

✔ Filter unpaid invoices

✔ Calculate total outstanding balance

✔ Sort highest invoice values

Tables + Sorting + Filtering allow you to do this quickly.

12. Practice Exercise

Create this table:

Date	Supplier	Invoice No	Amount
01/02/26	ABC Supplies	P001	1,200
05/02/26	Quick Ltd	P002	3,400
10/02/26	ABC Supplies	P003	900
12/02/26	OfficeCo	P004	2,100

Tasks:

1. Convert data into a Table.

2. Sort by Amount (Largest to Smallest).

3. Filter only ABC Supplies.

4. Add Total Row.

5. Clear filter.

13. Common Mistakes to Avoid

❌ Not including headers

❌ Leaving blank rows

❌ Sorting only one column

❌ Forgetting filters are applied

❌ Saving reports with hidden data

14. Knowledge Check Questions

1. Why should you convert data into a Table?

2. What is the difference between sorting and filtering?

3. How do you filter invoices above a certain value?

4. Why is the Total Row useful in finance?

15. Key Takeaways

✔ Tables make data structured and professional

✔ Sorting organises data

✔ Filtering isolates specific information

✔ These skills are essential in Sales Ledger and Purchase Ledger roles

✔ Used daily in finance departments