Tables

Using tables in Excel 2016 can improve how your spreadsheet looks. Use the table function in Excel to also better organize content.

Modifying tables

It's easy to modify the look and feel of any table after adding it to a worksheet. Excel includes many 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 into 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:

To change the table style:

  1. Select any cell in your table, then click the Design tab.
    navigating to the design tab
  2. Locate the Table Styles group, then click the More drop-down arrow to see all available table styles.
    more tables styles
  3. Select the desired table style.
    choosing a table style
  4. The table style will be applied.

    table

To modify table style options:

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

  1. Select any cell in your table, then click the Design tab.
  2. 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.
    table style options
  3. The table style will be modified. In our example, a new row has been added to the table with a formula that automatically calculates the total value of the cells in column D.
    total row

Depending on the type of content you have—and the table style you've chosen—these options can affect your table's appearance in various ways. You may need to experiment with a few different options to find the exact style you want.

To remove a table:

It's possible to remove a table from your workbook without losing any of your data. However, this can cause issues with certain types of formatting, including colors, fonts, and banded rows. Before you use this option, make sure you're prepared to reformat your cells if necessary.

  1. Select any cell in your table, then click the Design tab.
  2. Click the Convert to Range command in the Tools group.
    convert to range command
  3. A dialog box will appear. Click Yes.
    convert to range confirmation
  4. The range will no longer be a table, but the cells will retain their data and formatting.
    converted table

To restart your formatting from scratch, click the Clear command on the Home tab. Next, choose Clear Formats from the menu.

clear command

Challenge!

  1. Open our practice workbook.
  2. Click the Challenge tab in the bottom-left of the workbook.
  3. Select cells A2:D9 and format as table. Choose one of the light styles.
  4. Insert a row between rows 4 and 5. In the row you just created, type Empanadas: Banana and Nutella, with a unit price of $3.25, and a quantity of 12.
  5. Change the table style to Table Style Medium 10.
  6. In Table Style Options, uncheck banded rows and check banded columns.
  7. When you're finished, your workbook should look like this:

    Tables Challenge