How to Create a Table in Excel: Format as Table, Structured References and Total Row

shape
shape
shape
shape
shape
shape
shape
shape
How to Create a Table in Excel: Format as Table, Structured References and Total Row - featured image

An Excel table is a range of data that Excel treats as one object. It grows when you add rows, copies formulas down by itself, keeps the header row in view, and gives you filter buttons, banded rows and a total row with one click. Tables also make formulas easier to read, because you can write [Amount] instead of C2:C100, and they are the base for slicers, PivotTables and charts that update automatically. This guide shows how to create a table, name it, add a calculated column, use structured references, add a total row, resize the table and convert it back to a normal range. A small sales example with numbers you can check by hand is used throughout.

Updated November 2026

In this guide

  1. Table or normal range?
  2. Example data
  3. Step 1: create the table with Ctrl+T
  4. Step 2: give the table a name
  5. Step 3: add a calculated column
  6. Step 4: add rows and columns
  7. Step 5: use structured references
  8. Step 6: add a total row
  9. Step 7: sort and filter
  10. Step 8: change the style
  11. Resize or convert the table
  12. Tables with charts and PivotTables
  13. Lookups and tables
  14. Common mistakes
  15. Practice problems
  16. Quick recap

Table or normal range?

A normal range is just cells. If you add a new row under it, formulas, charts and PivotTables built on the range do not always include it. A table is different, because it knows where the data starts and ends, and it expands when you type directly below it. That is why most Excel experts recommend tables for any list of records, such as orders, customers, expenses or attendance. A table needs a header row, with a different text in each header cell, and it should have no completely blank rows or columns inside the data.

Example data

Imagine a list with the headers Item, Quantity, Price and Total in A1:D1. The rows are Pen with 10 and 2, Book with 5 and 12, and Bag with 2 and 30. The Total column will hold quantity times price, so Pen is 20, Book is 60 and Bag is 60, and the sum of the three is 140. Keep these numbers in mind, because we will use them to check the formulas and the total row.

Step 1: create the table with Ctrl+T

Click any cell inside the data and press Ctrl+T. A small dialog asks where the data for the table is, and shows the range, for example =$A$1:$D$4. Make sure My table has headers is ticked, then click OK. Excel applies a blue style with banded rows and adds filter arrows to the headers. You can also use the Insert tab and choose Table, or the Home tab, Format as Table, which lets you choose the style first. When the table is active, a Table Design tab appears on the ribbon.

Step 2: give the table a name

By default, Excel names the first table Table1. Click inside the table, go to Table Design and type a short name in the Table Name box, such as Sales, and press Enter. A good name has no spaces, starts with a letter and describes the data. The name appears in formulas, so Sales[Total] is easier to understand than Table1[Total]. Our named range guide explains the naming rules, which are the same for tables.

Step 3: add a calculated column

Click the first cell under the Total header, which is D2, and type an equals sign, then click the Quantity cell, type an asterisk and click the Price cell. Excel writes =[@Quantity]*[@Price]. The at sign means this row. Press Enter and Excel fills the formula down the whole column at once. The results are 20, 60 and 60. If you add a fourth row, the formula appears there automatically. If the fill does not happen, check File, Options, Proofing, AutoCorrect Options, AutoFormat As You Type, and tick Fill formulas in tables to create calculated columns.

Step 4: add rows and columns

To add a row, type in the first empty row right under the table, and the table expands and takes the formatting and the formulas with it. You can also press Tab in the last cell of the table to create a new row. To add a column, type a header in the cell next to the last header. To insert a row or column in the middle, right-click a cell, choose Insert and then Table Rows Above or Table Columns to the Left. To delete, choose Delete and then Table Rows or Table Columns.

Step 5: use structured references

A structured reference points to a table part by name. =SUM(Sales[Total]) adds the whole Total column, which gives 140 for our example. =AVERAGE(Sales[Price]) gives 44 divided by 3, which is 14.67. When you add rows, these formulas include them without any change, because the reference grows with the table. Use Sales[[#Headers],[Item]] for a header, and Sales[#Totals] for the total row. Our SUMIFS guide shows how to use table columns as criteria ranges.

Step 6: add a total row

Click inside the table and press Ctrl+Shift+T, or tick Total Row in Table Design. A new row appears at the bottom with a total for the last column. Click a cell in the total row, and a drop-down arrow lets you choose Sum, Average, Count, Max, Min and other functions. These use SUBTOTAL, so they ignore rows hidden by a filter. For our table, the total of the Total column is 140. If you filter to show only Book, the total becomes 60. Our SUBTOTAL guide explains why.

Step 7: sort and filter

Each header has a filter arrow. Click it to sort from A to Z or from smallest to largest, to filter by value or color, or to search. A filtered table shows blue row numbers, and the filter arrow changes to a funnel. For a more visual filter, use Insert Slicer in the Table Design tab, which our slicer guide explains. To clear all filters, go to Data and choose Clear. Sorting a table keeps each row together, so the values stay with their items.

Step 8: change the style

On the Table Design tab, open the Table Styles gallery and pick a style. Tick or untick Banded Rows, First Column and Last Column to change the look. Choose Clear in the gallery for a plain table with the table features kept. A light style prints well in black and white, and a dark header helps on screen. You can also set a default style by right-clicking a style and choosing Set as Default, so every new table has the same look.

Resize or convert the table

To change the size of the table, go to Table Design and choose Resize Table, then select the new range. This is useful when you want to include a row that sits outside the table. To turn the table back into a normal range, choose Convert to Range in Table Design. The data and the formatting stay, but the formulas change from structured references to normal cell references. For example, =[@Quantity]*[@Price] becomes =B2*C2.

Tables with charts and PivotTables

A chart built on a table updates when you add rows, so you do not need to change the chart range. The same is true for a PivotTable, but you must refresh it after adding data. This makes a table the best source for a dashboard. Create the table, build a PivotTable and a chart from it, and when new records arrive you add them under the table and refresh. Our pie chart guide shows a chart that you can base on a table.

Lookups and tables

Tables work well with lookup formulas. If the Sales table holds items and prices, then =XLOOKUP("Book",Sales[Item],Sales[Price]) returns 12, and the formula keeps working when you add new items below the table. You do not need to change the range or use absolute references. This is one reason why tables are the base of many templates, such as invoices, price lists and trackers. See our inventory tracker guide for a full example that uses a table.

Common mistakes

The most common mistake is to leave blank rows inside the data, which splits the table in two. Another is to use merged cells in the header, which a table does not allow. A third is to have duplicate header names, which Excel changes to Item2 and so on. A fourth is to type below the table when the row below is not empty, so the table cannot expand. A fifth is to turn on a total row and then type over its formulas by accident. Fix these and the table behaves as expected, and you can share it with other people without problems.

Practice problems

Try these. Problem 1: create a table from the example data and name it Sales. Problem 2: add the calculated column and check that the results are 20, 60 and 60. Problem 3: add a row for Cap with quantity 4 and price 10, and check that the new row total is 40 and the column sum is 180. Problem 4: add a total row and filter for Book, which should show 60. Problem 5: convert the table to a range and look at how the formula changes. Problem 6: use XLOOKUP to find the price of Bag, which should be 30.

Quick recap

Click in the data and press Ctrl+T, tick My table has headers and click OK. Name the table in Table Design. Write one formula in a column and let Excel fill it. Use structured references such as Sales[Total], add a total row with Ctrl+Shift+T, and filter, sort or add a slicer. Resize the table if needed, and convert it to a range when you no longer want table behavior. A table keeps your data tidy and your formulas correct as the list grows.

Frequently asked questions

How do I create a table in Excel?

Click in the data and press Ctrl+T, then tick My table has headers and click OK.

How do I add a total row to a table?

Press Ctrl+Shift+T or tick Total Row in the Table Design tab.

How do I turn a table back into a range?

Choose Convert to Range in the Table Design tab.

What is a structured reference?

It is a formula reference that uses table and column names, such as Sales[Total].

Why does my table not expand?

The row below may not be empty, or autoformat options may be switched off.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *