Search Contextures Sites

 

Contextures
Excel news
by email

 

 

 

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

 

Creating an Excel Table

What is an Excel Table?

In Excel 2007, and later versions, you can use the Table command to convert a list of data into a formatted Excel Table. Tables have many features, such as sorting and filtering, that will help you organize and view your data.

formatted excel table

An Excel Table makes an excellent source for a pivot table, so you should use this feature if you plan to create a Pivot Table from the data.

Preparing Your Data

Before you create the formatted Excel Table, follow these guidelines for organizing your data.

  • The data should be organized in rows and columns, with each row containing information about one record, such as a sales order, or inventory transaction.
  • In the first row of the list, each column should contain a short, descriptive and unique heading.
  • Each column in the list should contain one type of data, such as dates, currency, or text.
  • Each row in the list should contain the details for one record, such as a sales order. If possible, include a unique identifier for each row, such as an order number.
  • The list should have no blank rows within it, and no completely blank columns.
  • The list should be separated from any other data on the worksheet, with at least one blank row and one blank column between the list and the other data.

    prepare data for excel table

Creating an Excel Table

After your data is organized, as described above, you're ready to create the formatted Table.

  1. Select a cell in the list of data that you prepared.
  2. On the Ribbon, click the Insert tab.

    insert table command

  3. In the Tables group, click the Table command.
  4. In the Create Table dialog box, the range for your data should automatically appear, and the My table has headers option is checked. If necessary, you can adjust the range, and check box.
  5. Click OK to accept these settings.

    create table dialog box

Sort and Filter the Data

Your list is now an Excel Table, and is automatically formatted with a default Table Style, which you can change.

The heading cells have drop down arrows that you can use to sort or filter the data.

sort and filter commands

Rename an Excel Table

When it is created, an Excel table is given a default name, such as Table 3. You should change the name to something meaningful, so it will be easier to work with the table later.

To change the table name:

  1. Select any cell in the table
  2. On the Ribbon, under the Table Tools tab, click the Design tab.
  3. design tab on Ribbon

  4. At the far left of the Ribbon, click in the Table name box, to select the existing name
  5. table name box

  6. Then, type a new name, such as Orders, and press the Enter key

    new name in Table Name box

Video: Create an Excel Table

To see the steps in creating an Excel Table, please watch this short video tutorial.

Create an Excel Table With Specific Style

When you create a table with the Table command on the Ribbon's Insert tab, the table retains any formatting that it currently has, and the default Table Style is applied.

If you want to apply a specific table style when creating an Excel Table:

  1. Select a cell in the list of data that you prepared.
  2. On the Ribbon, click the Home tab.
  3. In the Styles group, click Format as Table
  4. Click on the Style that you want to use
  5. format as table

    OR, to apply a Style and remove any existing formatting, right-click on a Style, and click Apply and Clear Formatting

    format as table

  6. In the Create Table dialog box, the range for your data should automatically appear, and the My table has headers option is checked. If necessary, you can adjust the range, and check box.
  7. Click OK to accept these settings.

A formatted Excel Table is created, with the selected Table Style.

Video: Create an Excel Table With Specific Style

When you create a table you can apply a specific style from the Table Style options, instead of using the default style. Then, when you are applying that style, click the option to remove any current cell formatting from the data range.

Watch this video to see the steps, and the written instructions are below the video

Show Totals in a Table

After you create an Excel table, it's easy to show the total for a column, or for multiple columns, using a built-in Table feature.

To show a total:

  1. Select any cell in the table
  2. On the Ribbon, under the Table Tools tab, click the Design tab
  3. In the Table Style Options group, add a check mark for Total Row
  4. prepare data for excel table

  5. A Total row will be added at the bottom of the table, and one or more column of numbers might show a total.

Change and Add Totals

In addition to the automatically created totals, you can select totals for other columns.

  1. Click in the Total cell for one of the columns.
  2. From the drop down list, select the function that you want to use in the current column.

    prepare data for excel table

A SUBTOTAL formula is added to the cell, and it shows the calculation based on the visible cells in the table's column.

prepare data for excel table

Print Excel Table Only

When you are working with lists in Excel, use the built-in Table feature, to make it easier to work with the data. Then, if you want to print just the table, without the other items on the worksheet, you can use a built-in command -- Print List.

The command is not on the Ribbon, so you can add it there, or put the command on the Quick Access Toolbar. Watch this short video to see the steps.

Download the Sample File

To download the sample file for this Table tutorial, click here: Table Sample File.

The zipped file is in xlsx format, and does not contain macros.

Related Excel Tutorials

Pivot Table Tutorial for Excel 2007

Pivot Table Tutorial for Excel 2003 and earlier

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: July 8, 2014 7:37 PM