How to Create and Use an Excel Table

An Excel Table is more than a colored range. It gives a list named columns, built-in sort and filter controls, formulas that can fill down, and a boundary that can expand as records are added. Those features make a changing list easier to maintain than a range you must repeatedly select by hand.

This guide builds one table from a small service-ticket list, then uses it to answer a practical question: how much work was billed, and how much of that total belongs to the Design team?

Know what you are creating

An Excel Table keeps the original records in place. It adds a structured layer around them: headers identify fields, filter buttons narrow the view, and a table name lets formulas refer to the data. It is different from a PivotTable, which creates a separate summary, and from the Data > What-If Analysis > Data Table command.

Use an Excel Table when you have one record per row and expect to add, sort, filter, calculate, or reuse those records. A static report layout with merged cells, several header rows, or manual subtotals needs cleanup before it becomes a reliable table.

Prepare a clean source list

Enter this sample in A1:D6. Rate is the amount billed per hour, with all amounts in the same currency. Keep the first row as headers and enter Hours and Rate as numbers, not text such as 2 hours or $80 per hour.

Enter the records in A1:D6 as a normal range first, with one header row and no blank rows or columns inside the list.

Give each column a distinct, nonblank heading. Avoid blank rows or columns inside the list, and keep unrelated notes or totals outside the range you intend to convert. The important choice in the Create Table dialog is the range: if a record is outside it, the table cannot include that record in filters or formulas.

Turn the range into a table

1. Select any cell in A1:D6. If other data touches the list, select A1:D6 explicitly.
2. In desktop Excel, choose Insert > Table. On Windows, Ctrl + T is a shortcut.
3. Check the highlighted range in the Create Table dialog.
Before creating the table, confirm that Excel selected the correct range and keep the My table has headers option checked.

4. Select My table has headers so Ticket, Team, Hours, and Rate remain the column names.
5. Select OK. Look for filter buttons in the header row and a contextual table tab when a table cell is selected.

After you confirm the Create Table dialog, Excel converts the range into a table and adds filter buttons to each header.

If you leave My table has headers unchecked, Excel treats the first row as data and supplies default column names. Correct the selection and header choice before building formulas on top of the table. Microsoft’s create-and-format guide covers the current Windows, Mac, and web paths.

Give the table a useful name and style

Select a cell in the table and open Table Design in Excel for Windows; some Mac versions show Table instead. In the Table Name box, replace a generic name such as Table1 with ServiceLog, then press Enter. A table name must be unique in the workbook and cannot contain spaces or look like a cell reference.

Use the Table Name box on the Table Design tab to replace a generic name such as Table1 with a meaningful name like ServiceLog.

Choose a style that keeps headers and values readable. Banded rows can help the eye track across a wide list, but the style does not change the underlying numbers or calculations. If you only want different colors, choose another table style; do not convert the table back to a range just to change its appearance.

Sort and filter the records

Every table header has a menu for sorting and filtering. To see only Design tickets, open the Team menu, clear the other selections, choose Design, and select OK. The Support and Operations rows are hidden from this view, not deleted.

Filtering the Team column to Design hides the other records without deleting them.

To restore the full list, clear the filter in the Team menu. To rank the visible work, open the Hours menu and choose a largest-to-smallest sort. A sort changes row order, so check that each ticket’s Team, Hours, and Rate still travel together.

A filter changes which records are visible; it does not change what a normal SUM formula includes. Use the table’s Total Row when you want a total that responds to filtering.

Add a calculated column

Clear the Team filter so all tickets are visible. Click E1, immediately to the right of the Rate header, and type Cost. In E2, enter:

=[@Hours]*[@Rate]

Enter =[@Hours]*[@Rate] in the first Cost cell and Excel fills the calculated column down automatically.

The @ means “this row.” Excel should fill the same calculation through the table column, using each ticket’s Hours and Rate. These references are called structured references; they use column names instead of cell addresses.

For the five tickets, Cost should be 160, 285, 120, 280, and 190. If a result looks wrong, inspect the source values before changing the formula. Numbers entered as text or an inconsistent formula in one row can prevent the column from behaving as expected. Microsoft explains structured-reference syntax and calculated-column behavior.

Add a filter-aware Total Row

1. Select any cell in the table.
2. On the contextual Table Design tab, turn on Total Row. On Mac, look for the equivalent option on the Table tab.
3. In the Total Row cell under Cost, open its menu and choose Sum.
4. With no filter active, confirm that the Cost total is 1,035.
The Total Row uses SUBTOTAL so the displayed Cost total can update when table filters are applied.

5. Filter Team to Design. The visible Cost total should now be 475.

When the Team column is filtered to Design, the table’s SUBTOTAL-based Total Row updates the Cost total from 1,035 to 475.

Excel uses a SUBTOTAL formula for the standard Total Row choices, so filtered-out rows do not contribute to the displayed result. The Total Row can also show Average, Count, and other summaries, depending on the column. Microsoft’s Total Row instructions show the current controls and formula behavior.

Check the filter before quoting a total: 475 is the Design-only result, while 1,035 is the full-list result. Both are correct for different views.

Use the table name in a formula

Outside the table, you can refer to the whole Cost column by name:

=SUM(ServiceLog[Cost])

Use =SUM(ServiceLog[Cost]) outside the table to total the Cost data column by its table and column names.

This formula includes the table’s data rows and excludes its header and Total Row. Unlike the Total Row’s SUBTOTAL, a regular SUM includes values in rows hidden by a table filter. Use the Total Row when the displayed result should follow the visible records; use SUM(ServiceLog[Cost]) when you need the full data total regardless of the current filter.

Renaming the table or a column updates structured references that use that name. Adding rows inside the table also adjusts references to the table column, which is one reason a named table is useful as a changing data source.

Extend or resize the table safely

Type a new ticket in the first row directly below the table, or add a new column immediately to its right. Excel can expand the table to include the new data and extend its formatting and calculated-column formula. When the Total Row is visible, enter the new record in a table row above that Total Row rather than typing into the summary cells.

After adding a record, confirm that it has the table style, appears in a header filter, and receives a Cost formula. If the new data stayed outside the table, select a table cell and choose Table Design > Resize Table on Windows, then select the complete new range. Microsoft’s resize guide documents the available ways to extend rows and columns.

Keep or remove table functionality

Changing the table style or turning off banding does not remove the table. If you truly need a normal range, select a table cell and choose Table Design > Convert to Range in Excel for Windows; on Mac, use the corresponding Table tab command. Confirm the conversion only after checking formulas or reports that depend on the table name.

The existing values and applied style can remain, but table-specific behavior ends: header filter buttons are removed and structured references in formulas become ordinary cell references. You can still sort or filter the resulting range by turning those tools on separately. See Microsoft’s conversion guide for platform-specific steps.

Fix common Excel Table problems

Frequently asked questions

Make the table earn its place

Before sharing a workbook, check three things: the boundary includes every record, calculated columns give plausible values, and filters are clear or intentionally documented. A table’s convenience comes from those dependable connections—not from its color scheme.