Excel 2013: Tables

Lesson 21: Tables

/en/excel2013/groups-and-subtotals/content/

Introduction

Once you've entered information into a worksheet, you may want to format your data as a table. Just like regular formatting, tables can improve the look and feel of your workbook, but they'll also help to organize your content and make your data easier to use. Excel includes several tools and predefined table styles, allowing you to create tables quickly and easily.


Optional: Download our practice workbook.

To format data as a table:

  1. Select the cells you want to format as a table. In our example, we'll select the cell range A4:D10.
    Screenshot of Excel 2013Selecting a cell range to format as a table
  2. From the Home tab, click the Format as Table command in the Styles group.


    Screenshot of Excel 2013Clicking the Format as Table command
  3. Select a table style from the drop-down menu.
    Screenshot of Excel 2013Choosing a table style
  4. A dialog box will appear, confirming the selected cell range for the table.
  5. If your table has headers, check the box next to My table has headers, then click OK.
    Screenshot of Excel 2013Clicking OK
  6. The cell range will be formatted in the selected table style.


    Screenshot of Excel 2013The cell range formatted as a table

Tables include filtering by default. You can filter your data at any time using the drop-down arrows in the header cells. To learn more, review our lesson on Filtering Data.

Modifying tables

It's easy to modify the look and feel of any table after adding it to a worksheet. Excel includes different options for customizing a table, including adding rows or columns and changing the table style.

To add rows or columns to a table:

If you need to fit more content in your table, Excel allows you to modify the table size by including additional rows and columns. There are two simple ways to change the table size:

  • Begin typing new content after the last row or column in the table. The row or column will be included in the table automatically.
    Screenshot of Excel 2013Typing a new row below an existing table
  • Click, hold, and drag the bottom-right corner of the table to create additional rows or columns.


    Screenshot of Excel 2013Dragging the table border to create more rows

To change the table style:

  1. Select any cell in your table, then click the Design tab.
    Screenshot of Excel 2013Clicking the Design tab
  2. Locate the Table Styles group, then click the More drop-down arrow to see all available table styles.
    Screenshot of Excel 2013Clicking the More drop-down arrow
  3. Select the desired style.


    Screenshot of Excel 2013Choosing a new table style
  4. The selected table style will appear.
    Screenshot of Excel 2013The new table style

To modify the table style options:

You can turn various options on or off to change the appearance of any table. There are several options: Header Row, Total Row, Banded Rows, First Column, Last Column, Banded Columns, and Filter Button.

  1. Select any cell in your table.
  2. From the Design tab, check or uncheck the desired options in the Table Style Options group. In our example, we'll check Total Row to automatically include a total for our table.


    Screenshot of Excel 2013Checking the Total Row option
  3. The table style will be modified. In our example, a new row has been added to the table with a formula that will automatically calculate the total value of the cells in column D.


    Screenshot of Excel 2013The table with a total row

These options can affect your table style in various ways, depending on the type of content in your table. You may need to experiment with a few different options to find the exact style you want.

To remove a table:

Sometimes you may not want to use the additional features included with tables, such as the Sort and Filter drop-down arrows. You can remove a table from the workbook while still preserving the table's formatting elements, like font and cell color.

  1. Select any cell in your table. The Design tab will appear.
  2. Click the Convert to Range command in the Tools group.
    Screenshot of Excel 2013Clicking Convert to Range
  3. A dialog box will appear. Click Yes.
    Screenshot of Excel 2013Removing a table
  4. The range will no longer be a table, but the cells will retain their data and formatting.
    Screenshot of Excel 2013The cell range formatted as a normal range

Challenge!

  1. Open an existing Excel workbook. If you want, you can use our practice workbook.
  2. Format a range of cells as a table. If you are using the example, format the cell range A2:E13.
  3. Add a row or column to the table.
  4. Choose a new table style.
  5. Change the table style options. If you are using the example, add a total row.
  6. Remove the table.

/en/excel2013/charts/content/