Excel Tables, Structured References, and Data Sorting and Filtering

What Is an Excel Table?

An Excel Table is a named, structured object that Excel treats as a self-contained database. When you convert a plain data range into a Table, Excel automatically applies banded row formatting, adds filter drop-down arrows to every column header, and tracks the boundaries of your data so that formulas and sorting always act on the correct rows — even when you add new records later.

For bookkeeping work, Tables are valuable because a general ledger file can hold hundreds or thousands of rows. A Table lets you sort, filter, and total that data quickly and reliably, which matters when an auditor or supervisor asks a question that needs a fast, accurate answer.

Creating a Table

To convert a data range to a Table:

1. Click anywhere inside your data range.

2. Choose Insert > Table (keyboard shortcut: Ctrl + T).

3. Confirm that the My table has headers checkbox is checked, then click OK.

Excel assigns a default name such as Table1. You should rename it immediately to something meaningful. Click inside the table, go to the Table Design tab (or Table Tools > Design in older versions), and type your chosen name in the Table Name box at the far left. For this lesson, use GLTransactions. Table names must start with a letter, contain no spaces, and cannot duplicate a cell reference.

The Total Row

With the table selected, check Total Row on the Table Design tab. Excel adds a shaded row at the bottom. Click any cell in the Total Row and use the drop-down to choose a function: Sum, Average, Count, Max, Min, and others. For a general ledger file, you will typically choose Sum under both the Debit and Credit columns so you can confirm that total debits equal total credits — a basic bookkeeping accuracy check.

Structured References

When you write formulas that reference a Table, Excel uses structured references instead of ordinary cell addresses. A structured reference names the table and the column rather than a cell like B2:B121.

Examples:

| What you want | Structured reference |

|---|---|

| All values in the Debit column | `=GLTransactions[Debit]` |

| Sum of the Debit column | `=SUM(GLTransactions[Debit])` |

| Sum of the Credit column | `=SUM(GLTransactions[Credit])` |

Structured references update automatically when rows are added or removed. This is safer than a fixed range like `=SUM(B2:B121)`, which would miss any row added beyond row 121.

Sorting a Table

Sorting reorganizes rows without losing any data. To perform a multi-level sort:

1. Click inside the table.

2. Go to Data > Sort.

3. Add your first sort level: Account Number, Order = A to Z (ascending).

4. Click Add Level; add a second sort level: Date, Order = Oldest to Newest.

5. Click OK.

This gives you a ledger organized by account, with transactions in chronological order inside each account — the standard presentation for internal review or audit support.

Filtering a Table

Filtering hides rows that do not match your criteria; it does not delete them. Excel displays only matching rows and shows blue row numbers to signal that a filter is active.

To filter for a specific account category such as Utilities:

1. Click the drop-down arrow on the Account Category column header.

2. Clear Select All, then check Utilities only.

3. Click OK.

To clear a filter, click the drop-down again and choose Clear Filter From [column name], or click Data > Clear.

For numeric filters (for example, transactions over $500), click the drop-down on the Amount or Debit column, choose Number Filters > Greater Than, and type 500.

You can combine filters across multiple columns at the same time — for example, filter the Date column for March entries and filter the Debit column for values greater than $500.

Printing a Filtered View to PDF

When you need to deliver a filtered result as documentation:

1. With the filter active, go to File > Print (or Ctrl + P).

2. Under Settings, choose Print Active Sheet. Excel prints only visible rows.

3. Add a header by clicking Page Setup > Header/Footer > Custom Header. Type the descriptive header, for example: Utilities Account Detail – March 2025.

4. Choose Microsoft Print to PDF as the printer, then click Print and save the file.

A clear, labeled header is not just professional courtesy — it is essential in bookkeeping so that anyone reading the document later knows exactly what subset of data it represents.

Data Integrity and Accuracy

Before sorting or filtering, verify that:

• Every column has a unique, clear header (no blank headers).
• There are no completely blank rows inside the data range, which can cause Excel to misread the table boundary.
• Date values are stored as actual Excel dates, not text strings that look like dates — otherwise date sorting will not work correctly.

These checks are the bookkeeper's equivalent of a pre-task safety inspection: catching a problem before you act prevents incorrect results from reaching a report or audit file.

Saving Your Work

Save frequently using Ctrl + S. Use a clear file-naming convention such as GLTransactions_YourName_Date.xlsx. Never overwrite the original source file — always work on a copy so the raw data is preserved.