Section 8.1. The Basics of Tables | Excel 2007 for Starters. The Missing Manual

8.1. The Basics of Tables

An Excel table is really nothing more than a way to store a bunch of information about a group of items. Each item occupies a separate row, and different kinds of information about the item reside side by side in adjacent columns . In database terminology, the rows are records , and the columns of information are fields . For example, the records could represent customers, and the fields could contain things like name , address, purchase history, and so on.

Excel tables have a number of advantages over ordinary worksheet data:

They grow and shrink dynamically . As you fill data into adjacent rows and columns, the table grows to include the new cells . And as a table changes size , any formulas that use the table adjust themselves accordingly . In other words, if you have a formula that calculates the sum of a column in a table, the range that the SUM( ) function uses expands when you add a new record to the table.
They have built-in smarts . You can quickly select rows and columns, apply a custom sort order, and search for important records.
They excel (ahem) at dealing with large amounts of information . If you need to manage vast amounts of information, you may find ordinary worksheet data a little cumbersome. If you put the same information in a table, you can simply apply custom filtering , which means you see only the records that interest you.

8.1.1. Creating a Table

Creating a table is easy. Here's how:

Choose the row where you want your table to start .

If you're creating a new table, the worksheet's first row is a good place to begin. (You can always shift the table down later by putting your cursor in the top row, and then choosing Home Cells Insert Insert Sheet Rows.) This first row is where you enter any column titles you want to use, as explained in the next step.

Cells Insert Insert Sheet Rows to add some extra space when things get crowded, its always better to start off with plenty of breathing room.
Enter the column titles for your table, one column title for each category you want to create .

To create the perfect table, you need to divide your data into categories. For example, if you're building a table of names and addresses, you probably want your columns to hold the standard info you see on every form ever created: First Name, Last Name, Street, City, and so on. The columns you create are the basis for all the searching, sorting, and filtering you do. For instance, if you have First Name and City columns, you can sort your contacts by first name or by city.

If you want, you can start to add entries underneath the column headings now (in the row directly below the column titles). Or just jump straight to the next step to create the table.

Make sure you're currently positioned somewhere inside the table ( anywhere in the column title row works well), and then choose Insert Tables Table .

Excel scans the nearby cells, and then selects all the cells that it thinks are part of your table. Once Excel determines the bounds of your table, the Create Table dialog box appears, as shown in Figure 8-1.

Figure 8-1. The Create Table dialog box displays the cell references for the currently selected range. In this example, the selection includes only the headings (there's no data yet). You can change the range by typing in new information or by clicking the mini worksheet icon at the right end of the cell range box, which lets you select the range by dragging on the appropriate cells in the worksheet.

Make sure the "My table has headers" checkbox is turned on. This option tells Excel you're using the first row just for column headers. Then click OK .

Excel transforms your cells into a table, like the one shown in Figure 8-2. You can tell that your ordinary range of cells has become a genuine table by the presence of a few telltale signs. First, tables start out with automatic formatting that gives each row a shaded background (alternating between blue and gray). Second, the column headings appear in bold white letters on a dark background, and each one includes a drop-down arrow that you can use for quick filtering (a feature you'll explore in Section 8.2.3).

If you create a table from a group of cells that don't include column titles, don't turn on the "My table has headers" checkbox. When you create the table, Excel adds a row of columns at the top with generic names like Column1, Column2, and so on. You can click these cells, and then edit the column titles, to be more descriptive.

Figure 8-2. To quickly resize your table, look for the tiny triangle icon at the bottom-right corner (under the two-headed arrow in this figure), and then drag it to encompass more (or fewer) rows and columns.

Keep in mind that tables consist of exactly two elements: column headers (Figure 8-3) and rows. Tables don't support row headers (although there's no reason why you can't create a separate column and use that as a row title). Tables also have a fixed structure, which means that every row has exactly the same number of columns. You can create multiple tables on the same worksheet, but you're often better off placing them on separate worksheets so you can more easily manage them.

Figure 8-3. Here's one unsung frill in every table. When you can't see the column headers any longer (because you've scrolled down the page), the column buttons atop the worksheet grid change from letters (like A, B, C) to your custom headers (like Product ID, Model Name, and Price). This way, you never forget what column you're in.

8.1.2. Formatting a Table

Every table starts out with some basic formatting, and you can use the ribbon and the Format Cells dialog box (as discussed in Chapter 5) to further change its appearance. However, Excel gives you an even better optionyou can use table styles .

A table style is a collection of formatting settings that apply to an entire table. The nice part about table styles is that Excel remembers your style settings. If you add new rows to a table, Excel automatically adds the right cell formatting. Or, if you delete a row, Excel adjusts the formatting of all the cells underneath to make sure the banding (the alternating pattern of cell shading that makes each row easier to read) stays consistent.

When you first create a table, you start out with a fairly ordinary set of colors: a grayblue combination that makes your table stand out from the rest of the worksheet. By choosing another table style, you can apply a different set of colors and borders to your table.

Note: Excel's standard table styles don't change the fonts in a table. To change fonts, you can change the theme (Section 5.3.2.1), or select some cells, and then, from the ribbon's Home

Font section, pick the font you want.

Table Styles section. Youll see a gallery of options as shown in Figure 8-4. As you move over a table style, Excel uses its live preview feature to change the table, giving you a sneak peak at how your table would look with that style.

Figure 8-4. Depending on your Excel window's width, in the ribbon, you may see the table style gallery. Or, if there's not enough room available, you see a Quick Styles button that you need to click to display a drop-down style gallery (as shown here).

Note: Notice that some table styles use banding, while others don't.

Table styles work like cell styles, which you learned about in Chapter 5. Like cell styles, they let you standardize and reuse formatting. Table styles, however, include a whole package of settings that tell Excel how to format different portions of the table, including the headers, first and last columns, the summary row, and so on.

Note: You can't edit the built-in table styles. However, you can change the table styles you create. In the table gallery, just right-click a style, and then choose Modify.

You'll notice that the built-in table styles have a limited set of colors. Excel limits them because table styles use colors from the current theme, which ensures that your table meshes well with the rest of your worksheet ( assuming you've been sticking to theme colors elsewhere). To get different colors for your tables, you can change the theme by choosing from the Page Layout Themes Themes gallery. Section 5.3.2.1 has more about themes.

Along with the table style and theme settings, you have a few more options to fine-tune your table's appearance. Head over to the ribbon's Table Tools Design Table Style Options section, where you see a group of checkboxes, each of which lets you toggle on or off different table elements:

Header Row lets you show or hide the row with column titles at the top of the table. You'll rarely want to remove this option. Not only are the column headers informative, but they also include drop-down lists for quick filtering (Section 8.2.3).
Total Row lets you show or hide the row with summary calculations at the bottom of your table.
First Column applies different formatting to the first column in your table, if it's defined in the table style.
Last Column applies different formatting to the last column in your table, if it's defined in the table style.
Banded Rows applies different formatting to each second row, if it's defined in the table style. Usually, the banded row appears with a background fill. Large-table lovers like to use banding because it makes it easier to scan a full row from right to left without losing your place.
Banded Columns applies different formatting to each second column, if it's defined in the table style. Folks use banded columns less than banded rows, because people usually read tables from side to side (not top to bottom).

8.1.3. Editing a Table

Once you've created a table, there are three basic editing tasks you can perform:

Edit a record . This part's easy. Just modify cell values as you would in any ordinary worksheet.
Delete a record . First, go to the row you want to delete (you can be in any column). Then, choose Home Cells Delete Delete Table Rows. Excel removes the row and shrinks the table automatically. For faster access that bypasses the ribbon altogether, just right-click a cell in the appropriate row, and then choose Delete Table Rows.
Add a new record . To add a record, head to the bottom of the table, and then type a new set of values just underneath the last row in the table. Once you finish typing the first value, Excel expands the table automatically, as shown in Figure 8-5. If you want to insert a row but don't want it to be at the bottom of the table, you can head to your chosen spot, and then choose Home Cells Insert Insert Table Rows Above (or right-click and choose Insert Table Rows Above). Excel inserts a new blank row immediately above the current row.

Note: Notice that when you insert or remove rows, you're inserting or removing table rows, not worksheet rows . The operation affects only the cells in that table. For example, if you have a table with three columns and you delete a row, Excel removes three cells, and then shifts up any table row underneath. Any information in the same row that exists outside the table is unaffected.

You may also decide to change the structure of your table by adding or removing columns. Once again, you'll find this task is like inserting or removing columns in an ordinary worksheet. (The big difference, as shown in Figure 8-6, is that any rows or columns outside your table remain unaffected when you add new rows or columns.)

Figure 8-5. Top: Here, a new record is being added just under the current table.
Bottom: Once you enter at least one column of information and move to another cell, Excel adds the new row to the table and formats it.

To add a column to the left of a column you're currently in, select Home Cells Insert Insert Table Columns to the Left. Excel automatically assigns a generic column title, like Column1, which you can then edit. If you want to add a column to the right side of the table, just start typing in the blank column immediately to the right of the table. When youve finished your entry, Excel automatically merges that column into the table, in the same way that it expands to include new rows.

To delete a column, move to one of its cells, and then choose Home Cells Delete Delete Table Column.

Figure 8-6. Excel makes an effort to leave the rest of your worksheet alone when you change your table's structure. For example, when expanding a table vertically or horizontally, Excel moves cells out of the way only when it absolutely needs more space. The example here demonstrates the point. Compare the before (top) and after (bottom) pictures: Even though the table in the bottom figure has a new column, it hasn't affected the data underneath the table, which still occupies the same column. The same holds true when deleting columns.

Finally, you can always convert your snazzy table back to an ordinary collection of cells. Just click anywhere in the table, and then choose Table Tools Design Tools Convert to Range. But then, of course, you dont get to play with your table toys anymore.

8.1.4. Selecting Parts of a Table

Once you've created a table, Excel provides you with some nice timesaving tools. For example, Excel makes it easy to select a portion of a table, like an individual row or column. Here's how it works:

To select a column , position your mouse cursor over the column header. When it changes to a down-pointing arrow, click once to select all the values in the column. Click a second time to select all the values plus the column header.
To select a row , position your mouse cursor over the left edge of the row until it turns to a right-pointing arrow; then click once.
To select the entire table , position your mouse at the top-left corner until it turns into an arrow that points down and to the right. Click once to select all the values in the table, and click twice to select all the values plus the column headers.

Figure 8-7 shows an example.

Figure 8-7. You can easily select an entire column in a table. Just position the mouse as shown here, and click once.

Once you've selected a row, column, or the entire table, you can apply extra formatting or create a chart (Chapter 9). However, changing a part of a table isn't exactly like changing a bunch of cells. For example, if you give 10 cells a hot-pink background fill, that's all you get10 hot-pink cells. But if you give a column a hot-pink background fill, your formatting change may initially affect 10 cells, but every time you add a new value in that column, it also gets the hot-pink background. This behavior, in which Excel recognizes that you're changing parts of a table, and applies your change to new rows and columns automatically, is called stickiness .