No Ticket Needed

Appearance

How to Format Data as a Table in Excel

Applies toMicrosoft 365ExcelExcel for MacExcel for the web
4 min fix Updated 29 Jul 2026

By , 10+ years in IT support and helpdesk How these guides are checked

Quick Answer

Click any cell inside your data and press Ctrl + T. Check that the range in the Create Table dialog is right, leave My table has headers ticked, and click OK.

Checked against Microsoft 365 Roadmap, Office release notes - Current Channel and between 5 Aug 2026 and 12 Sep 2026. Nothing published in that period has been linked to this guide.

Summary ​

Pressing Ctrl + T converts a block of cells into a real Excel table, and everything downstream gets better: filter buttons appear on every column, the header row stays visible as you scroll, a formula typed once fills the whole column, and =SUM(Sales[Amount]) keeps working when the data grows from 500 rows to 5,000. This article covers building the table, naming it, using structured references, adding a Total Row, and converting back to a plain range.

In this guide

Before You Start ​

  • A block of data with one header row at the top and no completely blank rows or columns inside it.
  • No merged cells inside the block — Excel refuses to build a table over them.
  • Edit permission on the workbook. No admin rights required.

Instructions ​

1. Turn a Range into a Table ​

The keyboard route takes two seconds. The ribbon route lets you pick the colours at the same time.

  1. Click any single cell inside your data. Do not select the whole block — Excel works out the boundaries for you.
  2. Press Ctrl + T. The Create Table dialog opens, showing a proposed range such as =$A$1:$E$412, and the block on the sheet is outlined with a marching dashed border.
  3. Check that the range covers everything you expect and nothing you do not.
  4. Leave My table has headers ticked if your top row holds column names such as Region and Amount. Clear it if your data starts straight in with values.
  5. Click OK.

The block redraws with a coloured header row, alternating light and white row stripes, and a small dropdown arrow on every column heading. A new Table Design tab appears at the right end of the ribbon.

Note: To choose the colour scheme up front instead, open the Home tab, click Format as Table in the Styles group, and pick a style from the gallery. The same Create Table dialog follows. The result is identical — Ctrl + T applies your workbook's default style.

Note: In Excel 2019 and earlier the new tab reads Design and sits under a green Table Tools heading. In Excel for the web it reads Table Design, same as Microsoft 365.

Verification: Scroll down until the first row leaves the screen. The grey column letters at the top of the sheet are replaced by your own header text — Region, Amount, Date. That only happens inside a real table.

2. Give the Table a Name ​

Excel names your first table Table1, the next Table2, and so on. Those names end up inside every formula you write, so spend ten seconds on a better one.

  1. Click any cell inside the table.
  2. Open the Table Design tab on the ribbon.
  3. Find the Table Name box at the far left, in the Properties group. It currently reads Table1.
  4. Click into the box, type a name such as Sales, and press Enter.

Names cannot contain spaces and must start with a letter or an underscore. Use SalesQ3 or Sales_Q3, not Sales Q3.

Tip: To see every table in the workbook at once, open the Formulas tab and click Name Manager in the Defined Names group. Tables are listed alongside named ranges, each showing the sheet it lives on.

3. Write Formulas with Structured References ​

This is the payoff. Inside a table, columns have names, and formulas use those names instead of cell addresses.

  1. Click an empty cell outside the table and type =SUM(.
  2. Move the mouse over the Amount column of your table and click its header. Excel writes the reference for you:
=SUM(Sales[Amount])
  1. Type ) and press Enter. The total appears.

Compare that with =SUM(A2:A500). When row 501 arrives, the cell-range version silently misses it. Sales[Amount] expands the moment the new row joins the table, and every formula pointing at it updates.

Inside the table, use @ to mean "this row":

  1. Click the first empty cell of a new column to the right of the table, and type a header such as Line Total. The table stretches to include the new column.
  2. In the first data cell of that column, type =, click the Quantity cell in the same row, type *, and click the Price cell in the same row. Excel writes:
=[@Quantity]*[@Price]
  1. Press Enter. The formula fills down every row of the column on its own, and a small lightning-bolt AutoCorrect Options button appears beside the cell.

Note: That automatic fill is called a calculated column. If it does not happen, the feature is switched off: go to File > Options > Proofing > AutoCorrect Options, open the AutoFormat As You Type tab, and tick Fill formulas in tables to create calculated columns.

Tip: Structured references work from other sheets too — =SUMIF(Sales[Region],"North",Sales[Amount]) reads far better than a page of dollar signs. For more on that pattern, see How to Use SUMIF and COUNTIF in Excel.

4. Add a Total Row ​

The Total Row sits at the bottom of the table and moves down as the table grows, so it never gets buried.

  1. Click any cell inside the table.
  2. On the Table Design tab, find the Table Style Options group in the middle of the ribbon.
  3. Tick Total Row. A new shaded row appears below the last data row, with the word Total at the left and a sum in the rightmost numeric column.
  4. Click any cell in that row. A dropdown arrow appears at the right edge of the cell.
  5. Click the arrow and choose the calculation you want: Sum, Average, Count, Count Numbers, Max, Min, StdDev, Var, or None.

Note: The Total Row writes a SUBTOTAL formula, not SUM. That is deliberate — SUBTOTAL ignores rows hidden by a filter, so the total reflects what you are actually looking at. Filter to the North region and the total changes to the North figure.

Tip: Ctrl + Shift + T toggles the Total Row on and off without leaving the keyboard.

5. Use the Filter Buttons and the Sticky Header Row ​

Every table gets sorting and filtering with no extra setup.

  1. Click the dropdown arrow on any column header.
  2. Choose Sort A to Z, Sort Z to A, or Sort by Color at the top of the menu. For number and date columns the labels read Sort Smallest to Largest and Sort Oldest to Newest.
  3. To filter, clear the (Select All) checkbox in the list at the bottom of the menu, tick only the values you want, and click OK. The rows that do not match are hidden, the row numbers down the left side jump, and the column's dropdown arrow gains a small funnel symbol.
  4. To clear it again, open the same menu and choose Clear Filter From followed by the column name.

The header row behaves differently from a normal sheet: as soon as you scroll past row 1 with the active cell inside the table, your header text takes over the column-letter bar at the top. Click a cell outside the table and the letters A, B, C come back.

Note: That replacement only applies while you are inside the table. To keep a header row visible across the entire sheet, use View > Freeze Panes instead — see How to Freeze Panes and Split Views in Excel.

Tip: For filtering that stays on screen as buttons rather than hiding in a menu, click Insert Slicer in the Tools group of the Table Design tab, tick the columns you want, and click OK. Slicers work on tables without needing a PivotTable.

6. Change the Table Style or Turn Banding Off ​

The default stripes are a starting point, not a commitment.

  1. Click any cell in the table and open the Table Design tab.
  2. In the Table Styles gallery on the right, hover over a style to preview it live on the sheet, then click to apply.
  3. Click the small More arrow at the bottom-right corner of the gallery to see the full set, grouped as Light, Medium and Dark. Clear sits at the very bottom and strips all table colouring while keeping the table itself.
  4. In the Table Style Options group, tick or clear the checkboxes to control the parts:
    • Banded Rows — the alternating stripes.
    • Header Row — the coloured top row.
    • Filter Button — the dropdown arrows on the headers.
    • First Column and Last Column — bold emphasis on the outer columns.
    • Banded Columns — vertical stripes instead of horizontal.

Note: If the stripes never appear, manual fill colour on those cells is sitting on top of the table style. Select the range, open the Home tab, click the arrow beside the paint-bucket Fill Color button, and choose No Fill. The banding shows through.

7. Convert the Table Back to a Range ​

A handful of features refuse to work on tables — Data > Subtotal is greyed out, and some older add-ins and consolidations expect a plain range. Converting back takes one click.

  1. Click any cell in the table.
  2. On the Table Design tab, find the Tools group and click Convert to Range.
  3. A dialog asks Do you want to convert the table to a normal range? Click Yes.

The Table Design tab disappears, the filter buttons go, and the Total Row becomes an ordinary row of formulas. The colours and banding stay, because they are now plain cell formatting.

Note: Every structured reference is rewritten into cell addresses at the moment you convert. =SUM(Sales[Amount]) becomes =SUM(C2:C412) and stops expanding when you add rows. Check any formula that pointed at the table before you save.

Troubleshooting ​

TIP

Tables and PivotTables are made for each other. Build the PivotTable from a table rather than a range and the PivotTable's source grows automatically — add rows to the table, click Refresh on the PivotTable Analyze tab, and the new data is included with no range to edit. See How to Create a PivotTable in Excel.

Symptom / ErrorPotential CauseSolution
Ctrl + T proposes the wrong rangeBlank row or column inside the dataExcel stops at the first fully blank line. Delete the blank row, or type the correct range into the Create Table dialog before clicking OK.
This action won't work on merged cellsMerged cells inside the blockSelect the range, open the Home tab, and click Merge & Center to unmerge. Then press Ctrl + T again.
A formula did not fill down the columnCalculated columns switched offGo to File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type and tick Fill formulas in tables to create calculated columns (Step 3).
A new row typed below the table is not part of itAutomatic expansion switched offOn the same AutoFormat As You Type tab, tick Include new rows and columns in table. Or drag the small right-angle handle at the bottom-right corner of the last cell down over the new rows.
Data > Subtotal is greyed outSubtotals do not run on tablesUse the Total Row instead (Step 4), or convert back to a range (Step 7).
A structured reference returns #REF!Referenced column renamed or deletedOpen the formula and point it at the current column name. Renaming a table column updates existing formulas; deleting one does not.

Last updated:

Frequently asked questions

What is the difference between a table and a normal range in Excel?
A range is a set of cells. A table is a named object with a defined header row, so Excel knows where the data starts and ends. That is what gives you automatic filter buttons, formulas that fill down the whole column, a Total Row, and references such as =SUM(Sales[Amount]) that expand on their own when you add rows.
Does formatting data as a table change my data?
No. It changes how the cells are formatted and how Excel treats the block, but every value stays exactly as it was. The one thing to check is your header row — Excel needs one, and if you leave My table has headers unticked it inserts Column1, Column2 and so on above your data.
How do I remove table formatting but keep the data?
Click any cell in the table, open the Table Design tab, and click Convert to Range in the Tools group. The colours stay, the filter buttons and structured references go. To strip the colours as well, first choose Clear at the bottom of the Table Styles gallery.