This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

Microsoft Excel 2007

Creating Tables

Excel Tables are useful for managing sets of related data. Excel 2007 makes it easy to set up a Table and add data to it. For basic information on Tables, refer to Tables Overview.

Creating a Table

By creating a table with Excel's Table button, you will have access to Table Tools and the accompanying Design command tab (neither of which are available for normal a data range).

You can either create a blank table or create a table from an existing data range.

Creating a Table: From a Blank Cell Range

  1. On your worksheet, select a range of cells you want to make into a Table
    Cell Range selected

  2. From the Insert command tab, in the Tables group, click TABLETable button
    NOTES:
    The Create Table dialog box appears, displaying the selected cell range.
    Behind the Create Table dialog box, the selected cell range is highlighted with an animated border.
    Create Table dialog box

  3. OPTIONAL: To specify a different cell range, in the Where is the data for your table? text box, type the desired cell range
    OR
    To select the range
    1. Click COLLAPSE DIALOG BOXCollapse Dialog Box button
    2. Select the desired cell range
    3. Click EXPAND DIALOG BOXExpand Dialog Box button
    4. Click OK

  4. OPTIONAL: If your selected cell range already has headers (i.e., column labels), select My table has headers

  5. To accept the selected cell range for your table, click OK
    The selected cell range is converted into a Table.
    Selected Cell Range into a Table
    NOTE: For information on Table terms, refer to Tables Overview: Table Terms.

 

Creating a Table: From an Existing Data Range

  1. Select the data that will make up your Table

  2. From the Insert command tab, in the Tables group, click TABLETable button
    The Create Table dialog box appears, displaying the selected data range.
    If Excel detects headers (i.e., column labels) in the selected data range, the My table has headers option is automatically selected.

  3. OPTIONAL: If your table does not already have headers (i.e., column labels), deselect My table has headers

  4. OPTIONAL: To specify a different cell range, in the Where is the data for your table? text box, type the desired cell range
    OR
    To select the range
    1. Click COLLAPSE DIALOG BOXCollapse Dialog Box button
    2. Select the desired cell range
    3. Click EXPAND DIALOG BOXExpand Dialog Box button
    4. Click OK

  5. To accept the selected cell range for your Table, click OK
    The selected cell range is converted into a Table.

To convert a Table back to a data range:

  1. Select a cell within the Table

  2. From the Design command tab, in the Tools group, click CONVERT TO RANGEConvert To Range button

  3. In the confirmation dialog box, click YES
    The Table is converted to a range.

return to topUsing Forms to Enter Table Data

Once your Table has been created, Excel provides an easy way to enter data called the Form feature. Instead of moving the cursor to each new cell, with the Form feature you can add and edit Table data from a simple dialog box. The Form dialog box is also helpful for searching for records. The Find Next and Find Prev options make locating a specific record easier.

In Excel 2007, to access the Form feature, the Form button must first be added to the Quick Access toolbar.

Adding the Form Button to the Quick Access Toolbar

  1. At the top of the Excel window, to the right of the Quick Access toolbar, click CUSTOMIZE QUICK ACCESS TOOLBARCustomize the Quick Access Toolbar button» select More Commands
    The Excel Options dialog box appears, with Customize selected.

  2. From the Customize Quick Access Toolbar section, in the Choose commands from pull-down list, select Commands Not in the Ribbon
    The scroll box under the pull-down list refreshes to display several commands not found on the Ribbon.

  3. From the scroll box, select Form...

  4. Click ADD

  5. Click OK
    The Form button is added to the Quick Access toolbar.

Accessing the Form Dialog Box

NOTES:
The Form feature cannot be accessed from a blank worksheet; you must have an existing Table.
If you try to open the Form dialog box from empty cell range (i.e., not a Table), a dialog box will appear giving you the option to either use the first row of the selection as labels and not as data (i.e., the Form dialog box opens), or to cancel and make any appropriate changes to your database (i.e., the Form dialog box does not open).

  1. Select a cell within the Table

  2. From the Quick Access toolbar, click FORMForm button
    The Form dialog box appears displaying the sheet name, your Table's field names (i.e., column labels), and any previously entered row data.
    NOTE: The following two graphics depict a Table and the form dialog box when opened from the table.
    Table with Header Row
    Form dialog box

Using Form Options

Adding a New Record

  1. Access the Form dialog box
    NOTES:
    The current view in the the Form dialog box always represents one Table row (i.e., a blank new row or an existing row with previously entered data).
    The Form dialog box displays existing column labels with corresponding text boxes representing the individual cells under each column.

  2. Click NEW
    The form is cleared and ready for you to enter a new record.

  3. In each text box, type the desired information 

  4. To move between fields, press [Tab]

  5. To add the current data to your Table and automatically open a blank form for a new record, press [Enter] or [return]

  6. Repeat steps 3–5 as needed
    NOTE: Your Table will automatically expand if you enter more records than the number of rows you initially indicated for the table.

  7. When you are finished entering data, click CLOSE
    The new rows appear at the bottom of your Table.

Editing a Record

  1. Access the Form dialog box
    NOTES:
    The current view in the the Form dialog box always represents one Table row (i.e., a blank new row or an existing row with previously entered data).
    The Form dialog box displays existing column labels with corresponding text boxes representing the individual cells under each column.

  2. To move to the desired row, click FIND NEXT or FIND PREV
    OR
    Press [Up Arrow] or [Down Arrow]

  3. In the appropriate Form dialog box text boxes, make the desired changes

  4. When finished, click CLOSE

Deleting a Record

  1. Access the Form dialog box
    NOTES:
    The current view in the the Form dialog box always represents one Table row (i.e., a blank new row or an existing row with previously entered data).
    The Form dialog box displays existing column labels with corresponding text boxes representing the individual cells under each column.

  2. To select the existing record you want to delete, click and hold the scroll bar dragging up or down
    OR
    Click FIND PREV or FIND NEXT

  3. Click DELETE
    A confirmation dialog box appears.

  4. Click OK

  5. Click CLOSE
    The record is permanently deleted from your database.

Searching for a Record

The Criteria feature allows you to search according to desired criteria.

  1. Access the Form dialog box
    NOTES:
    The current view in the the Form dialog box always represents one Table row (i.e., a blank new row or an existing row with previously entered data).
    The Form dialog box displays existing column labels with corresponding text boxes representing the individual cells under each column.

  2. Click CRITERIA

  3. In the appropriate field(s), type your search criteria 

  4. Click FIND NEXT or FIND PREV

  5. Repeat steps 3-4 as necessary

  6. Click CLOSE

return to topUsing the Worksheet Window

If you need to make only minor changes in your Table, it may be quicker to make them in the worksheet view.

Adding a Row

  1. Click in the first blank row at the bottom of your Table

  2. Select the cell where you want to enter new data

  3. Type the data accordingly

  4. Press [Tab]
    OR
    Use the arrow keys to move to the next field of the record
    NOTE: As you enter data and move within the row, that row is automatically added to your Table.

Deleting a Row

For information on deleting Table rows, refer to Working with Rows and Columns: Deleting Rows.

Searching for Specific Terms or Values

For information on searching in your Table, refer to Using the Find and Replace Features: Using the Find Feature.

Excellence. Our Measure. Our Motto. Our Goal.