Excel tables: organise records, formulas and filtered totals

You can spend half an hour colouring cells in Excel and still end up with a register that falls apart as soon as someone sorts it. I start by deciding what one row represents. Choosing a style comes later. Colour will not supply a missing date or explain why the same transaction appears twice. Decorating the problem is a surprisingly effective way to postpone understanding it.

Here I show how to turn a range into an Excel table, prepare useful columns, add a calculated column and check the total after filtering. The data is synthetic: one row represents one ticket. The interface instructions mainly target Microsoft 365 for Windows; buttons can have different names or locations on Mac and in the browser. Microsoft describes the basic object in its table creation guide.

What creating a table actually changes

A range is a collection of cells identified by addresses, such as A1:E5. An Excel table is an object with a name, headers and a defined data area. You can refer to its columns by name and extend the same dataset when new records arrive. Drawing borders around a rectangle does not give it those properties. Turning on a filter in an ordinary range does not create a table either.

Throughout this article, “table” means that specific Excel object. A PivotTable has a different purpose: it summarises a source dataset. A data table used for What-If Analysis is another separate feature. We are preparing a dependable source here, which can later feed a report. Distinguishing these terms makes it much easier to understand instructions or ask someone for help with a workbook.

Click inside the finished object and look for the Table Design tab and its table name setting. Alternating coloured rows are only a visual clue. You can remove that styling while retaining the table itself. I therefore check the object and its boundaries instead of assuming that anything with coloured headers has been created correctly. Appearance is useful, but it provides limited evidence about the data underneath.

Decide what one row means

Suppose you record a ticket date, an owner, an amount and a status. A ticket may contain several items. You need to decide whether this register stores tickets or individual items. Mixing those levels makes the number of rows an unreliable ticket count. It can also repeat the total ticket amount on several lines, inflating the sum. No keyboard shortcut can resolve that definition on your behalf.

In this exercise, every ID is unique and every amount uses the same arbitrary unit. In a real workbook, I would add a currency column if different currencies were present and avoid combining amounts without an agreed conversion. I settle those rules before writing formulas. Otherwise, the spreadsheet may calculate something perfectly accurately while answering a question nobody intended to ask.

Use one header row. Keep merged cells out of the data area, repeat the owner on every relevant record and avoid blank separator rows between months. Each row should carry the information needed to understand it after sorting. A large report title can sit above the table. Inside the source, a regular structure gives you much more freedom to filter, combine and analyse records later.

Build a small register

Enter the following dataset in A1:E5. Use genuine Excel date values recognised by your regional settings and actual numbers for the amounts. The display below shows the intended contents rather than prescribing cell formatting. Names and IDs are fictional. I use compact headings so that the formulas remain easy to follow without scrolling across a large worksheet.

The amounts total 200. Anna owns two tickets totalling 100, while all open tickets total 130. You can establish those values simply by reading the four records. They become useful checkpoints when you start applying filters. If a result differs, examine the data, table boundary and active filters. Typing the expected total over the formula would only conceal the reason for the discrepancy.

Column names should be concise and specific. “Date” is sufficient for this exercise, but a real process may need both Ticket_date and Closure_date. Similarly, “Amount” should have a documented meaning: planned, actual, gross or net. A useful header helps someone interpret the dataset even when the person who built it is unavailable. Ambiguous labels push that work onto every future reader of the workbook.

Create the table and name it

Select A1:E5 and choose Insert > Table. In Excel for Windows, Ctrl+T is a convenient shortcut. Check the proposed rectangle and confirm that the data has headers. If you leave that option unchecked, Excel handles the first row differently and supplies its own column labels. Checking the range before accepting the dialogue is a small step that prevents an unnecessarily confusing start.

Click inside the new table and change its name to Tickets on the Table Design tab. A descriptive name is more useful than Table7 when several sources share a workbook. It describes the contents rather than their current position on a sheet. Months later, a formula that refers to Tickets gives you a better starting point than one whose references consist entirely of cell addresses.

Choose a restrained style with banded rows, adjust column widths and format the dates and amounts appropriately. Applying a currency format does not convert values or establish their definition. Changing the symbol leaves the underlying number unchanged. If several people supply data, agree on units before combining it. A consistently formatted column can still contain values that should never be added together.

Filter records while keeping the dataset intact

Use the filter in Owner to select Anna. You should see Z-101 and Z-103. Add a Status filter for Open and the same two records remain. Filters across columns work together to narrow the selection. If a view suddenly looks incomplete, I inspect every active filter rather than repeatedly changing the last one I touched. The hidden condition is often in a different column.

Filtering hides records from the view without deleting them from the source. Clearing all filters brings back the four tickets. Microsoft explains the mechanism in its filtering guide. Before sharing the workbook, either clear the filters or make the selected scope obvious. Otherwise, someone may interpret a partial view as the complete register and build a misleading summary from it.

Sort using a table header, for example Date from oldest to newest. Check that IDs, owners and amounts still form the correct records afterwards. Text masquerading as dates or numbers can produce a surprising order. Fix the type and the source values before trying to force the desired sequence. For files arriving as plain text, the article on importing CSV into Excel explains how to protect types during import.

Add a readable calculated column

Enter Check in F1 and make sure the new column belongs to the table. In its first data row, enter the formula below. This English example uses commas between arguments; your regional settings may require semicolons. The Polish equivalents of IF and AND are JEŻELI and ORAZ. Function language and argument separators are separate settings to keep in mind when copying formulas.

=IF(AND([@Amount]<50,[@Owner]="Anna"),"Review","OK")

For Z-101, both conditions hold: the amount is 40 and the owner is Anna. The result is Review. Z-103 belongs to Anna but has an amount of 60, so it receives OK. Both of Jan’s tickets also receive OK. This is an intersection of conditions, following the documented IF and AND rules. A different threshold could change the selection even if the owners and ticket IDs remained exactly the same.

The @ character refers to the current row. By contrast, Tickets[Amount] refers to the table’s entire data column. The distinction is documented in Microsoft’s structured reference guide. A normal calculated column fills its formula down through the records. If one row behaves differently, compare its formula: someone may previously have replaced that particular cell with a fixed value.

I would investigate such an exception before overwriting it. It may reflect an intentional rule or a manual attempt to make an inconvenient warning disappear. Ideally, an exception is expressed through data, such as an Explanation column, rather than hidden inside one unusual formula. A colleague should be able to reconstruct why the result differs without comparing every cell against the rest of the column.

Make the total follow the filter

Enable Total Row on the Table Design tab. In the cell beneath Amount, choose Sum from the list. The standard selection uses SUBTOTAL, although the total row also accepts a manually written formula. Microsoft’s total row instructions explain that flexibility. A cell labelled Total therefore does not, by itself, tell you exactly which records its formula includes.

With all filters cleared, the result should be 200. Select Anna and it should become 100. Clear that selection and filter for Open instead: the expected total is 130. With function code 109, SUBTOTAL also excludes manually hidden rows. Code 9 includes manually hidden rows. Both exclude rows hidden by filtering, as the SUBTOTAL reference specifies. That distinction matters when someone hides a row without using a filter.

An ordinary SUM over the whole data column can still include records that are currently invisible. I therefore use different labels for “whole register amount” and “visible ticket amount”. Both can be useful, but readers need to know which one they are looking at. When placing a screenshot in a presentation, add the active scope as well. The screenshot cannot reveal a filter that is no longer visible in the cropped area.

Check new records after adding them

With Total Row enabled, right-click a cell in the last data record and choose Insert > Table Rows Above. Enter Z-105 in the new row, using 2026-10-05, Anna, an amount of 20 and Open. If Total Row is disabled, typing directly below the table normally expands it. Still check the object boundary and the new calculated cell, especially after pasting a larger block. A blank row between the table and the addition makes it easier to leave the new data outside the intended source.

If the record remains outside, use Table Design > Resize Table and select the complete new area. Microsoft documents typing, pasting and resizing as ways to extend a table. Clear the filters and check for a total of 220. Anna’s total should now be 120. Those checks help establish both that the record entered the table and that the summary includes it.

Do not assume that every downstream report has refreshed just because the source table grew. A PivotTable, connection or export may have its own refresh step. Follow the flow from the new record to the number the recipient sees. A matching stripe on a new row is reassuring, but it only tells you about one part of that flow. The final report deserves a separate check.

Give duplicates and colours a clear meaning

Repeated amounts are often highlighted as possible duplicates. They can be a useful clue, but two matching amounts do not establish that a record is duplicated. Anna and Jan may legitimately submit different purchases worth 40 each. The definition of a duplicate depends on the key and the meaning of a row, for example a repeated ticket ID. Colour alone cannot supply that definition.

Use a conditional formatting rule on the relevant column and explain what the rule signals. Manual colouring does not update when a value changes. If red means “requires investigation”, a text status is useful too: it can be filtered and carried into another tool. The colour supports reading, while the underlying data preserves the meaning when someone prints, exports or reformats the worksheet.

When repairing an older workbook, preserve the source separately from the working copy and avoid deleting suspicious records based solely on appearance. First compare identifiers and establish the intended row definition. After a justified correction, check both the ticket count and the total amount. That gives you a concrete understanding of the change rather than a vague impression that the sheet looks cleaner.

Keep comments attached to the right record

A note about a specific ticket belongs in the same table as that ticket. If notes sit outside the sorted area, they can remain at the old row position while the tickets move. Someone then inherits a comment attached to the wrong ID. Keep general instructions, such as status definitions and the amount unit, on a separate instruction sheet rather than between records.

Monthly summary lines should also stay out of the detailed source. Adding a row called “October total” with an amount of 220 beneath records already totalling 220 would make an ordinary sum count 440. Use the designated total row or a separate report for summaries. The register itself should preserve a consistent meaning for every data row.

Agree on ownership and updates

Before handing over a shared register, decide who assigns IDs, which statuses are permitted and what makes a ticket closed. Labels such as Done and Closed may mean the same thing or two different stages. Resolve that process question before relying on a filter to communicate progress.

An empty amount and a zero amount also need separate definitions. Zero can be a valid value, while an empty cell may mean the amount has not been established. Replacing every missing amount with zero removes that signal. Review missing amounts and genuine zero amounts separately, then decide what the recipient needs: a summary or the detailed records needed to investigate individual tickets.

Exercise: hand over an understandable workbook

Rebuild the register, add Z-105 and show only Anna’s open tickets. There should be three records totalling 120. Inspect Check separately: after adding the new record, two rows should say Review. Clear the filters, sort dates descending and confirm that IDs still match their owners and amounts. Explain each result by pointing to the relevant records, without relying on the summary alone.

Ask another person to identify the amount definition, the meaning of one row and the active filter without a spoken explanation. If they have to guess, improve the headings or add a brief instruction. A good table should remain useful to someone who was not present when it was created. That is a more practical quality check than asking whether the chosen shade of blue looks professional.

The historical workbook from the original Polish article remains available as an additional resource. It uses different data, so its totals should not be compared with this exercise’s 200 and 220. For a next step, try percentage change calculations and begin by naming exactly which values and periods you want to compare.