Creating a table –
Most of the time, you’ll create a table from an existing range of data. However, Excel also
allows you to create a table from an empty range so that you can fill in the data later. The
following instructions assume that you already have a range of data that’s suitable for a
table:
- Make sure the range doesn’t contain any completely blank rows or columns;
otherwise, Excel will not guess the table range correctly. - Select any cell within the range.
- Choose Insert ➪ Tables ➪ Table (or press Ctrl+T). Excel responds with its Create
Table dialog box, shown in Figure below. Excel tries to guess the range, as well as
whether the table has a header row. Most of the time, it guesses correctly. If not,
make your corrections before you click OK.
The range is converted to a table (using the default table style), and the Table Tools Design
tab of the Ribbon appears.
To create a table from an empty range, select the range and choose Insert ➪ Tables ➪ Table.
Excel creates the table, adds generic column headers (such as Column1 and Column2), and
applies table formatting to the range. Almost always, you’ll want to replace the generic col-
umn headers with more meaningful text.
Adding data to a table –
If your table doesn’t have a total row, the easiest way to enter data is simply to start typ-
ing in the row just below the table. When you enter something in a cell, Excel automatically
expands the table and applies the formatting, formulas, and data validation to the new
row. You can also paste a value in the next row. In fact, you could paste several rows’ worth
of data and the table will expand to accommodate.
If your table does have a total row, you can’t use that technique. In that case, you can
insert rows into a table just like you would insert a row into any range. To insert a row,
select a cell or the entire row and choose Home ➪ Cells ➪ Insert. When the selected
range is inside a table, you’ll see new entries on the Insert menu that deal with tables
specifically. When you use these, the table is changed, but the data outside the table is
unaffected.
When the selected cell is inside a table, the shortcut keys Ctrl− (minus sign) and Ctrl+ (plus
sign) work on the table only and not on data outside the table. Moreover, as opposed to
when you’re not in a table, those shortcuts work on the whole table row or column regard-
less of whether you’ve selected the whole row or column.
Changing the table’s appearance –
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 apply a different table style.
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 More button at the bottom of the scrollbar to the
right, the Table Styles group expands, as shown in Figure below. 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.
And yes, some are really ugly and practically illegible.
For a different set of color choices, choose Page Layout ➪ Themes ➪ Themes to select a dif-
ferent document theme.
You can change some elements of the style by using the check box controls in the Table
Tools Design ➪ Table Style Options group. These controls determine whether various ele-
ments of the table are displayed and whether some formatting options are in effect:
Header Row Toggles the display of the header row.
Total Row Toggles the display of the total row.
First Column Toggles special formatting for the first column. Depending on the table
style used, this command might have no effect.
Last Column Toggles special formatting for the last column. Depending on the table style
used, this command might have no effect.
Banded Rows Toggles the display of banded (alternating color) rows.
Banded Columns Toggles the display of banded columns.
Filter Button Toggles the display of the drop-down buttons in the table’s header row.
If you’d like to create a custom table style, choose Table Tools Design ➪ Table Styles ➪ New
Table Style to display the New Table Style dialog box shown in Figure below. You can custom-
ize any or all of the 12 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.
Custom table styles are available only in the workbook in which they were created.
However, if you copy a table that uses a custom style to a different workbook, the custom
style will be available in the other workbook.
