Excel Table Design: Create and Format a Table

How-to basics · Updated November 18, 2026

To create a table in Excel, place your data in a simple grid with one header row, select a cell in the range, and choose Insert > Table. Confirm the range and whether it has headers, then use the Table Design tab to choose a style and table options. The result is a structured, filterable range that is easier to read, sort, and extend than ordinary cells.

What You’ll Need

  • Excel for Microsoft 365 on Windows, or a recent Excel version with the Table feature.
  • A workbook containing data arranged in columns, with one clear heading at the top of each column.
  • Optional: a preferred table style or a color scheme that fits the rest of the workbook.

You do not need to format the data before converting it. Excel can apply a table style during creation, and you can change the design later without changing the underlying values.

Step-by-Step

  1. Prepare the data. Put each record on its own row and each type of information in its own column. For example, a product list might have columns for Product, Category, Quantity, and Unit Price. Use one row of distinct headings, and avoid completely blank rows or columns inside the data. Remove merged cells from the range; tables need a consistent grid. If the worksheet has a title above the data, leave a blank row between the title and the column headings so it is not included accidentally.
  2. Select the range to turn into a table. Click any cell inside the contiguous data block. Excel usually detects the whole block automatically, including the heading row. If you prefer, drag to select the exact range, such as A1:D25. Check that the selection includes every record and all intended columns, but does not include notes or a separate report title. You can also click the first cell and use Ctrl+Shift+End, but check the highlighted range because previously used cells can make that shortcut select too much.
  3. Insert the table. On the ribbon, choose Insert > Table. Alternatively, with a cell in the data selected, press Ctrl+T on Windows. In the Create Table dialog, review the range shown in the box. Tick My table has headers if the first row contains column names. If it does not, leave the box unticked and Excel will create generic headings such as Column1; you can rename them afterward. Choose OK.
  4. Check the header row. Excel converts the range and displays filter arrows in the headings. Confirm that each heading is correct and that the first data row has not been mistaken for a heading. To edit a heading, click its cell and type the new text. Keep headings distinct and descriptive, such as Order Date or Unit Price, rather than leaving blanks or using the same heading twice. Excel may adjust duplicate names to make them unique.
  5. Choose a table style. Click a cell in the new table to show the contextual Table Design tab, then open the Table Styles gallery. Select a style with enough contrast for your workbook. If the gallery is compact, use its More button to see additional styles. Hover over a style to preview it before selecting. A style changes the table’s appearance, not the values. To remove the style colors while keeping the table structure, choose a plain or minimal style in the gallery.
  6. Set the design options. On Table Design, use the Table Style Options checkboxes to control features such as Header Row, Total Row, First Column, Last Column, Banded Rows, and Filter Button. Banded Rows adds alternating shading that helps readers follow long records across a page. First Column or Last Column can emphasize key fields, though the effect depends on the selected style. Keep Filter Button on if you want the dropdowns for sorting and filtering. Options shown can vary slightly by Excel version and window size.
  7. Give the table a useful name. With a table cell selected, find the Table Name box on the Table Design tab. Replace the default name, such as Table1, with something meaningful, for example SalesData or Inventory. Table names cannot contain spaces and must follow Excel’s naming rules; use an underscore or capitalize words to make a multiword name readable. A clear name is useful when you refer to the table in formulas, charts, or other workbook features.
  8. Format values by type. Select the relevant table column and use Home > Number Format to choose an appropriate display, such as Short Date, Currency, or Percentage. You can also use the number-format dropdown in the Number group. Formatting a column makes values easier to interpret, but does not convert text into a real date or number. If values are left-aligned unexpectedly or calculations do not work, inspect whether they were imported as text. Use a consistent number of decimal places where that improves readability.
  9. Add a total row or calculated column if needed. To show totals, select a table cell, open Table Design, and tick Total Row. A row appears at the bottom; click a cell in that row to choose a calculation from its dropdown, such as Sum or Average. Excel uses a subtotal-style calculation that responds to filtered rows. To add a calculated column, type a formula into the first data cell of a new table column; Excel commonly fills the formula down the rest of that column. For example, with columns named Quantity and Unit Price, and a new Total column, enter =[@Quantity]*[@[Unit Price]]. If the row has Quantity 3 and Unit Price 12.50, the result is 37.50. Apply a suitable number format to display it as currency if appropriate.
  10. Test the table and save. Use a heading’s filter arrow to sort or filter a column, then clear the filter when finished. Enter a new record in the first row directly below the table, or press Tab from the last cell in the table’s final row, and confirm the new row adopts the table formatting. Excel tables generally expand when data is entered immediately next to them, but a blank row or column can interrupt that behavior. Save the workbook after checking the displayed range, headings, and table name.

In Excel for Mac, use the Insert tab and choose Table, or use the equivalent Table command available in the ribbon; menu and ribbon layouts can differ by release. The Table Design tab appears when you select a table cell, as it does on Windows. Excel for the web also supports creating and styling tables, though some ribbon controls may be arranged differently.

Common Mistakes

  • Including a title as a table heading. A report title above the column names can end up as the first heading if the selected range is too large. Start the selection at the actual header row.
  • Leaving headings blank or unclear. Every column should have a usable heading. Vague names make filtering and formula references harder to understand.
  • Using merged cells in the data range. Merging cells disrupts the row-and-column structure tables depend on. Unmerge them and repeat the value where needed before creating the table.
  • Choosing a style for appearance alone. Very strong colors or low-contrast text can make figures difficult to read. Check the table on screen and in print, especially if the workbook will be shared.
  • Assuming a number format fixes the underlying value. Applying a date format to text that looks like a date does not always convert it into a true date value. Check alignment and whether sorting or arithmetic behaves as expected.
  • Deleting table structure when you only want to change its look. Clear or change the style from Table Design rather than converting the table to a normal range. Converting removes table behavior such as automatic filters and structured references.

Tips

  • Keep each table focused on one dataset. If you need a summary above the records, place it outside the table rather than inserting subtotal lines in the middle.
  • Use short, specific headings. Names such as Invoice Date and Customer ID are easier to recognize than Date and ID when the workbook grows.
  • Use banded rows for long lists, and consider a restrained style for a table that will be printed in black and white.
  • Filter before reviewing a large dataset, and remember that a filter hides rows rather than deleting them. Clear filters when you need to see all records again.
  • Table columns can expand as you type a new heading or add data beside the table. If you are preparing a report, verify the table boundaries afterward so unrelated notes have not been absorbed.
  • Use a consistent naming pattern for multiple tables, such as Sales2026 and Returns2026. Avoid spaces and names that could be confused with cell references.
  • If the workbook will be used by others, freeze the worksheet’s heading row separately when needed. A table’s Header Row option formats its headings but does not, by itself, keep them visible while scrolling.

Troubleshooting

The Table Design tab is missing. Click a cell inside the table, not a nearby worksheet cell. The tab is contextual and appears only when the selection is within a table. If the range is not actually a table, create one with Insert > Table.

The wrong cells became part of the table. Click inside the table and choose Table Design > Resize Table. Enter or select the correct range and confirm. You can also undo immediately after creating the table and repeat the selection carefully. Do not resize it to include a separate title, notes, or blank spacer rows.

The first record became the heading. Open Table Design and check the Header Row option, then inspect the actual column names. If Excel created generic headings, edit them directly. If your first data row was promoted to headings, undo the conversion and create the table again with the correct range and My table has headers setting.

New rows do not receive the style or formulas. Check that you are typing immediately below the final table row, without an empty row between the entry and the table. You can also right-click a table row and use Insert > Table Rows Above to add a row inside the table. If a calculated formula has not filled down, confirm that the column is part of the table and enter the formula in a data cell in that column.

The total row shows the wrong result. Click the total-row cell and inspect its dropdown calculation. A Count option, for example, counts entries rather than adding values. Choose Sum for a numeric total or another calculation that matches the question. If the result still looks wrong, check that the source values are numeric rather than numbers stored as text.

Filter arrows or banded shading disappeared. Select a table cell and inspect the Table Style Options on Table Design. Turn on Filter Button or Banded Rows as needed. If colors remain difficult to distinguish, choose a different style from the gallery. In some releases, the Table Design controls may be collapsed or hidden when the ribbon is simplified.

Frequently Asked Questions

What is the difference between an Excel table and a formatted range?
A formatted range can have colors and borders, but it does not automatically provide table-specific behavior. An Excel table includes headings with filter controls, can expand as records are added, supports a Total Row, and can use structured references in formulas.

Can I change an Excel table’s design later?
Yes. Select a cell in the table, open Table Design, and choose another style or change the style options. This changes the presentation without deleting the data or converting the table to a normal range.

Can I create a table without headers?
Yes. In the Create Table dialog, clear My table has headers. Excel supplies generic column names, which you can replace with descriptive headings afterward. For most working datasets, meaningful headers make sorting and filtering much more useful.

How do I remove the table design but keep the table?
Select the table, open Table Design, and choose a plain style from the Table Styles gallery. You can also turn off options such as Banded Rows. Avoid Convert to Range unless you specifically want to remove table behavior.

Can an Excel table include a blank row?
A table can contain a blank record, but blank rows inside a dataset can make it harder to review and may interfere with selecting or extending the intended range. Keep records continuous where possible. If you need visual spacing, use borders, shading, or a separate section outside the table instead.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.