Organize data into Excel 2007 tables

Excel 2007 Step by Step book cover

Microsoft Office Excel 2007 Step by Step
By Curtis D. Frye

Curtis D. Frye is a freelance author and Microsoft Office Excel Most Valuable Professional living in Portland, Oregon. He is a coauthor of Microsoft Office Excel 2003 Programming Inside Out and the author of Microsoft Office Excel 2003 Step by Step, Microsoft Excel Version 2007 Plain & Simple, Faster Smarter Home Networking, several books on Microsoft Access, and numerous online training courses.

To learn more about other books on the 2007 Microsoft Office system, visit Microsoft Press.

In this article

Microsoft Office Excel 2007 has always enabled you to manage lists of data effectively, enabling you to sort your worksheet data based on the values in one or more columns, limit the data displayed by using criteria (for example, show only those routes with fewer than 100 stops), and create formulas that summarize the values in visible (that is, unfiltered) cells. Customer feedback indicated that many Office Excel 2007 users wanted a more robust structure within Office Excel 2007 that enabled users to perform those operations and more. Office Excel 2003 included a structure called an Excel list that has evolved into the Excel table in Office Excel 2007.

Data organized into a table

Create an Excel table

To create an Excel table, type a series of column headers in adjacent cells and then type a row of data below the headers. Select the headers and data. On the Home tab, in the Styles group, click Format as Table. From the gallery that appears, click the style you want to apply to the table. When the Format as Table dialog box appears, verify that the cells in the “Where is the data for your table?” field reflect your current selection. Also, make sure the My table headers check box is selected, and then click OK.

Office Excel 2007 can also create a table from an existing data list as long as your data has a differently formatted header row, the list has no blank rows or columns within the data, and there is no extraneous data in cells immediately below or next to the list.

Top of Page Top of Page

Add data to a table

When you want to add data to a table, select a cell in the row immediately below the last row in the table or a cell in the column immediately to the right of the table; then type a value into the cell. After you enter the value and move out of the cell, the AutoCorrect Options smart tag appears. If you didn’t mean to include the data in the table, you can click Undo Table AutoExpansion to exclude the cells from the table. If you never want Office Excel 2007 to include adjacent data in a table, click the Stop Automatically Expanding Tables option.

 Tip   To stop Table AutoExpansion before it starts, click the Microsoft Office Button and then click Excel Options. In the Excel Options dialog box, click Proofing and then click the AutoCorrect Options button to display the AutoCorrect dialog box. Click the AutoFormat As You Type tab, clear the Include new rows and columns in table check box, and then click OK twice.

Top of Page Top of Page

Add or remove rows and columns

You can add rows and columns to a table, or remove them from a table, by dragging the resize handle at the table’s lower-right corner. If your table’s headers contain a recognizable series of values (such as Region1, Region2, and Region3), and you drag the resize handle to create a fourth column, Office Excel 2007 creates the column with the label Region4—the next value in the series.

Top of Page Top of Page

Summarize data using calculations

Tables often contain data you can summarize by calculating a sum or average, or by finding the maximum or minimum value in a column. To summarize one or more columns of data, you can add a Total row to your table.

An added Total row

When you add the Total row, Office Excel 2007 creates a formula that calculates the sum of the values in the rightmost table column. To change that summary operation or to add a summary operation to any other cell in the Total row, click the cell, click the down arrow that appears, and then click the summary operation you want to apply. Clicking the More Functions item displays the Insert Function dialog box, from which you can select any of the functions in Office Excel 2007.

Top of Page Top of Page

Name a table

Much as it does when you create a new worksheet, Office Excel 2007 gives your tables generic names such as Table1 and Table2. You can change a table name to something easier to recognize by clicking any cell in the table, clicking the Design contextual tab, and then, in the Properties group, editing the value in the Table Name field. Changing a table name might not seem important, but it helps make formulas that summarize table data much easier to understand. You should make a habit of renaming your tables so you can recognize the data they contain.

If for any reason you want to convert your table back to a normal range of cells, click any cell in the table and then, on the Table Tools contextual tab, in the Tools group, click Convert to Range. When Office Excel 2007 displays a message box asking if you’re sure you want to convert the table to a range, click OK.

Top of Page Top of Page

Applies to:
Excel 2007