How to Create a Table in Excel with Multiple Columns and Rows

To create a table in Excel with multiple columns and rows, type your headers in the first row and your data in the rows below, click any cell in the data, and press Ctrl + T (or select Insert > Table). Check that the range is right, tick My table has headers, and select OK. Excel turns the range into a table with filter buttons, banded rows and automatic expansion.

Applies to: Excel for Microsoft 365, Excel 2024, 2021, 2019 and 2016 on Windows, Excel for Mac, and Excel for the web.

Step 1: Lay out your data

• Put one header in each column of the first row, such as Date, Customer, Product, Quantity and Price. Every header must be different.
• Enter one record per row underneath, with no completely blank rows or columns in the middle.
• Don’t merge cells. Tables can’t contain merged cells.

Step 2: Turn the range into a table

1. Click any cell inside your data.
2. Press Ctrl + T, or go to Insert > Table. On a Mac, press Command + T.
3. Check the range in the Create Table box. Excel selects all connected cells; adjust it if needed.
4. Tick My table has headers. If you leave it clear, Excel adds generic headers named Column1, Column2 and so on.
5. Select OK.

You can also use Home > Format as Table and pick a style first; it creates the same kind of table.

Step 3: Name and style the table

1. Click inside the table to show the Table Design tab (called Table on a Mac).
2. In Table Name on the left, replace Table1 with a name such as Sales. Names can’t contain spaces.
3. Pick a style from Table Styles, and use the check boxes for Header Row, Banded Rows, First Column and Filter Button.

Add more rows and columns

• New row: type in the row directly below the table, or press Tab in the last cell of the last row. The table grows automatically.
• New column: type a header in the column just to the right of the table.
• Insert in the middle: right-click a cell and choose Insert > Table Rows Above or Table Columns to the Left.
• Resize: drag the small handle at the bottom-right corner, or use Table Design > Resize Table.
• Delete: right-click and choose Delete > Table Rows or Table Columns.

Add totals and formulas

Tick Total Row on the Table Design tab. A row appears at the bottom; click a cell in it and choose Sum, Average, Count or another function from the drop-down.

To calculate across columns, type a formula in the first cell of an empty column, for example =[@Quantity]*[@Price]. Excel fills it down the whole column automatically and adds it to new rows. These structured references use column names instead of cell addresses, so formulas are easier to read.

Sort, filter and slice the data

• Use the arrow in any header to sort or filter that column.
• For buttons that filter with one click, choose Table Design > Insert Slicer; inserting a slicer in Excel goes into more detail.
• To build a summary, click inside the table and choose Insert > PivotTable. The PivotTable uses the table name, so it picks up new rows when you refresh it.

Why use a table instead of a plain range?

• Charts, PivotTables and formulas that refer to the table include new rows automatically.
• Headers stay visible in place of column letters when you scroll down.
• Formatting stays consistent as the data grows.
• Duplicates can be removed with Table Design > Remove Duplicates. There’s a longer walkthrough in removing duplicates in Excel.

If you only want lines around your cells, you don’t need a table: select the cells and choose Home > Borders > All Borders.

If something goes wrong

• Table and Format as Table are greyed out: the sheet is protected, or the workbook is shared using the older Shared Workbook feature. Unprotect the sheet first.
• “A table can’t overlap another table”: your selection includes part of an existing table. Select a range outside it.
• The table doesn’t include all your data: a blank row or column split the range. Use Resize Table to include the rest.
• Formulas don’t fill down: AutoCorrect may be off. Go to File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type and tick Fill formulas in tables to create calculated columns.

Frequently asked questions

How do I turn a table back into a normal range?

Choose Table Design > Convert to Range. The formatting stays but the table features go away. More on this in undoing a table in Excel.

Can I move a table to another place?

Yes. Select the whole table and cut and paste it, or drag its border. For a walkthrough, read moving a table in Excel.

Can I combine two tables?

Yes, with Power Query or XLOOKUP, as shown in merging two tables in Excel.

How do I put an Excel table in a PowerPoint slide?

Copy it and paste it into the slide, or embed it so it stays editable. Details are in inserting an Excel table into PowerPoint.

How many rows can a table have?

As many as the worksheet allows: 1,048,576 rows and 16,384 columns.

Related: freezing multiple columns, naming columns, and creating a relationship between tables.

Matt Jacobs

Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.

His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.