Search
Contextures Sites
| |||
|
|
|
|||
|
|
|||
|
|
|
|
|
Search Contextures Sites
Creating an Excel Table
- What is an Excel Table?
- Preparing Your Data
- Creating an Excel Table
- Video: Create an Excel Table
- Sort and Filter the Data
- Rename an Excel Table
- Show Totals in a Table
- Download the Sample File
- Related Tutorials
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.
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.
Creating an Excel Table
After your data is organized, as described above, you're ready to create the formatted Table.
- Select a cell in the list of data that you prepared.
- On the Ribbon, click the Insert tab.
- In the Tables group, click the Table command.
- 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.
- Click OK to accept these settings.
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.
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:
- Select any cell in the table
- On the Ribbon, under the Table Tools tab, click the Design tab.
- At the far left of the Ribbon, click in the Table name box, to select the existing name
- Then, type a new name, such as Orders, and press the Enter key
Video: Create an Excel Table
To see the steps in creating an Excel Table, please watch this short video tutorial.
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:
- Select any cell in the table
- On the Ribbon, under the Table Tools tab, click the Design tab
- In the Table Style Options group, add a check mark for Total Row
- 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.
- Click in the Total cell for one of the columns.
- From the drop down list, select the function that you want to use in the current column.
A SUBTOTAL formula is added to the cell, and it shows the calculation based on the visible cells in the table's column.
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
Contextures Inc., Copyright ©2013
All rights reserved.
Last updated: May 30, 2013 3:34 PM
