Formatting Data as a Table in Excel

When you enter data in Excel, you may want to format it as a table. Tables improve the look of your worksheet. They also help organize data and make it easier to use. Excel has built-in table styles and tools that let you create tables quickly.

Download our Practice Workbook.

How to Format Data as a Table

  1. Select the cells you want to format. (Example: A2:D9)


  2. Click the Format as Table button on the Home tab.


  3. Choose a table style from the drop-down menu.


  4. A dialog box will appear. Confirm the selected range.

  5. If your table has headers, check the My table has headers box. Then click OK.


  6. The selected range will now be formatted as a table.


Tables in Excel include filters by default. You can use the drop-down arrows in header cells to filter data.

Modifying a Table

You can change the look of a table easily. Excel lets you add rows, columns, or change the table style.

Adding Rows or Columns

You can increase the table size in two ways:

  • Enter new data in the next row or column. Excel will include it in the table automatically.


  • Click and drag the bottom-right corner of the table to expand it.


Changing the Table Style

  1. Select any cell in the table.


  2. Click the Design tab.


  3. In the Table Styles group, click the More button.


  4. Select a style from the list.


Your table will now have the new style.

Customizing Table Style Options

You can turn options on or off to change the table’s appearance. Available options include:

  • Header Row
  • Total Row
  • Banded Rows
  • First Column
  • Last Column
  • Banded Columns
  • Filter Button

To modify table options:

  1. Click any cell in the table.

  2. Go to the Design tab.


  3. Check or uncheck the desired options.


For example, selecting Total Row will add a row that calculates totals automatically.

Removing a Table

You can remove a table without deleting your data. However, some formatting (like colors or banded rows) may be lost.

  1. Select any cell in the table.

  2. Click the Design tab.


  3. Click Convert to Range in the Tools group.


  4. A dialog box will appear. Click Yes.


Your table will now be a normal range, but it will keep its formatting.

To clear all formatting, go to the Home tab and select Clear → Clear Formats.