3. Choose Insert➪Tables➪Table (or press Ctrl+T).Excel responds with its Create Table dialog box, shown in Figure 6.4. Excel tries to guess the range, and whether the table has a header row.
Most of the time, it guesses correctly. If not, make your corrections before you click OK.
ON the CD-ROM ON the CD-ROM
FIGURE 6.4
Use the Create Table dialog box to verify that Excel guessed the table dimensions correctly.
The range is converted to a table (using the default table style), and the Table➪Tools➪Design tab of the Ribbon appears.
Excel may guess the table’s dimensions incorrectly if the table isn’t separated from other infor- mation by at least one empty row or column. If Excel guesses incorrectly, just specify the exact range for the table in the Create Table dialog box. Or, click Cancel and rearrange your worksheet such that the table is separated from your other data by at least one blank row or column.
Changing the Look of a Table
When you create a table, Excel applies the default table style. The actual appearance depends on which document theme is used in the workbook. If you prefer a different look, you can easily change the entire look of the table.
Select any cell in the table and choose Table Tools➪Design➪Table Styles. The Ribbon shows one row of styles, but if you click the bottom of the scrollbar to the right, the table styles group expands, as shown in Figure 6.5. The styles are grouped into three categories: Light, Medium, and Dark. Notice that you get a
“live” preview as you move your mouse among the styles. When you see one you like, just click to make it permanent.
For a different set of color choices, choose Page Layout➪Themes➪Themes to select a different document theme. For more information about themes, refer to Chapter 7.
NOTE NOTE
FIGURE 6.5
Excel offers many different table styles.
If applying table styles isn’t working, it’s probably because the range was already formatted before you converted it to a table. Table formatting doesn’t override normal formatting. To clear the existing background fill colors, select the entire table and choose Home➪Font➪Fill Color➪No Fill. To clear the existing font colors, choose Home➪Font➪Font Color➪Automatic. After you issue these commands, the table styles should work as expected.
If you’d like to create a custom table style, choose Table Tools➪Design➪Table Styles➪New Table Style to display the New Table Quick Style dialog box shown in Figure 6.6. You can customize any or all of the 13 table elements. Select an element from the list, click Format, and specify the formatting for that element.
When you’re finished, give the new style a name and click OK. Your custom table style will appear in the Table Styles gallery in the Custom category. Unfortunately, custom table styles are available only in the workbook in which they were created.
TIP TIP
If you would like to make changes to an existing table style, locate it in the Ribbon and right- click. Choose Duplicate from the shortcut menu. Excel displays the Modify Table Quick Style dialog box with all of the settings from the specified table style. Make your changes, give it a new name, and click OK to save it as a custom table style.
FIGURE 6.6
Use this dialog box to create a new table style.
Working with Tables
This section describes some common actions you’ll take with tables.
Navigating in a table
Selecting cells in a table works just like selecting cells in a normal range. One difference is when you use the Tab key. Pressing Tab moves to the cell to the right, and when you reach the last column, pressing Tab again moves to the first cell in the next row.
Selecting parts of a table
When you move your mouse around in a table, you may notice that the pointer changes shapes. These shapes help you select various parts of the table.
n To select an entire column:Move the mouse to the top of a cell in the header row, and the mouse pointer changes to a down-pointing arrow. Click to select the data in the column. Click a second time to select the entire table column (including the header). You can also press Ctrl+Space (once or twice) to select a column.
n To select an entire row:Move the mouse to the left of a cell in the first column, and the mouse pointer changes to a right-pointing arrow. Click to select the entire table row. You can also press Shift+Space to select a table row.
TIP TIP
n To select the entire table:Move the mouse to the upper-left part of the upper-left cell. When the mouse pointer turns into a diagonal arrow, click to select the data area of the table. Click a second time to select the entire table (including the Header Row and the Totals Row). You can also press Ctrl+A (once or twice) to select the entire table.
Right-clicking a cell in a table displays several selection options in the shortcut menu.
Adding new rows or columns
To add a new column to the end of a table, just active a cell in the column to the right of the table and start entering the data. Excel automatically extends the table horizontally. Similarly, if you enter data in the row below a table, Excel extends the table vertically to include the new row.
An exception to automatically extending tables is when the table is displaying a Totals Row. If you enter data below the Totals Row, the table will not be extended.
To add rows or columns within the table, right-click and choose Insert from the shortcut menu. The Insert shortcut menu command displays additional menu items:
n Table Columns To The Left n Table Columns To The Right n Table Rows Above
n Table Rows Below
When the cell pointer is in the bottom-right cell of a table, pressing Tab inserts a new row at the bottom.
When you move your mouse to the resize handle at bottom-right cell of a table, the mouse pointer turns into a diagonal line with two arrow heads. Click and drag down to add more rows to the table. Click and drag to the right to add more columns.
When you insert a new column, the Header Row displays a generic description, such as Column 1, Column 2, and so on. Normally, you’ll want to change these names to more descriptive labels.
Deleting rows or columns
To delete a row (or column) in a table, select any cell in the row (or column) to be deleted. If you want to delete multiple rows or columns, select them all. Then right-click and choose Delete➪Table Rows (or Delete➪Table Columns).
Moving a table
To move a table to a new location in the same worksheet, move the mouse pointer to any of its borders.
When the mouse pointer turns into a cross with four arrows, click and drag the table to its new location.
To move a table to a different worksheet (in the same workbook or in a different workbook):