Section 14.1. The Basics of tablesTables

14.1. The Basics of tablesTables

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.

  • They can link to databases . Tables are perfectly useful in standalone worksheets. However, they can also double as indispensable tools for navigating information contained in a database. In Chapter 25, you'll learn how to get information out of a database and into an Excel table.

14.1.1. Creating a Table

Creating a table is easy. Here's how:

  1. 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.
  2. 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.

  3. 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 14-1.

    Figure 14-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.

  4. 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 14-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 14.2.4).

    Figure 14-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.

    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.

Keep in mind that tables consist of exactly two elements: column headers (Figure 14-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.

14.1.2. Formatting a Table

When you move to a cell in a table, Excel adds a new tab to the ribbonthe Table Tools Design tab. This tab has five sections:

  • Properties , which lets you rename your table and resize it. (The table name is important only if you choose to use it in a formula, as described in Section 14.4.2.)

  • Tools , which lets you use pivot tables (described in Chapter 21), find duplicate rows (Section 14.3), and convert your table back to ordinary cells.

    Figure 14-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.

  • External Table Data , which lets you work with external data (such as records drawn from a database) using an Excel table. (You'll learn more in Chapter 25 about how to perform maneuvers like that.)

  • Table Style Options and Table Styles , which help you make your table look pretty.

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 6.2.4), 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 14-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 14-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 in like cell styles, which you learned about in Section 6.2. 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 (Section 14.4.4), and so on. To get a better feel for the variety of options in a table style, you can create your own by clicking the drop-down arrow in the Table Tools Design Table Styles section, and then choosing New Table Style. Youll see a New Table Quick Style dialog box that lets you go through the somewhat painstaking process of tweaking the formatting for each part of the table (Figure 14-5).

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.

Most of the time, it's not worth creating your own table styles because it's simply too much work (and the prebuilt table styles give you a good selection of formatting choices). However, 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 6.2.4 has more about themes.

Figure 14-5. To set the formatting for a part of the table, select it in the Table Element list, and then click Format. Excel opens a slimmed-down Format Cells dialog box that includes only the Font, Border, and Fill tabs. For example, to change the font for the whole table, select Whole Table, click Format, and then make your changes. If you think you've created the perfect table style, you can turn on the "Set as default table quick style" checkbox so Excel uses it whenever you create a new table in this workbook.

Along with the table style and theme settings, you have a few more options to finetune 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 14.2.4).

  • Total Row lets you show or hide the row with summary calculations at the bottom of your table. You'll learn how to configure this row in Section 14.4.4.

  • 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 less than banded rows, because people usually read tables from side to side (not top to bottom).

The Difference Between Excel Worksheets and Databases

An Excel table uses some of the same concepts as a databasenamely, the idea of records and fields. However, databases and Excel worksheets are two very different entities.

For starters, databaseswhich programs like Microsoft Access and SQL Server let you createhave much stricter rules than Excel worksheets. Before you can add any data to a table in a database, you must carefully define the table. You need to specify not only the name of each field, but also the type of information the field can contain. Although Excel provides some of these so-called data validation features (which you'll explore in Chapter 16), the program isn't nearly as strict about itvalidation is completely optional. Also, unlike Excel, most modern databases are relational , which means they contain multiple tables that have specific links to one another. For example, a relational database might tie together customers in one table and the orders they've made in another. In Excel, a worksheet can hold multiple tables of data, but there's no way to tie them together.

Most importantly, databases play a dramatically different role in the world of business. Typically, Excel is an end user program, which means ordinary mortals who generally know how to create an Excel file, design what it's going to look like, and then fill it up with data to use it. Ex-math majors, on the other hand, usually create databases, and they store information, behind-the-scenes, that non-programmer types end up using. For example, every time you use Google or search on Amazon for something to buy, you're actually seeing answers that have been stored in, and generated by, massive and powerful databases. In Chapter 25, you'll see how you can use Excel to retrieve information from a database, and then analyze it in a worksheet.

14.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 14-6. 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.

Figure 14-6. 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. Don't worryif Excel expands a table against your wishes, you aren't completely powerless. To correct Excel's mistake, look for the lightning bolt icon that appears immediately next to the newly added column. This doohickey is a smart tag that lets you reverse the expansion. Click it once, and Excel displays a pop-up menu giving you two choices: Undo Table AutoExpansion (to return your table to its previous size) and Stop Automatically Expanding Tables (to disable this behavior altogether).

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 14-7, is that any rows or columns outside your table remain unaffected when you add new rows or columns.)

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.

Figure 14-7. 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.

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

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.

14.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 14-8 shows an example.

Figure 14-8. 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 use another Excel feature like validation (Section 22.2) or charting (Chapter 17). 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 .

Excel 2007[c] The Missing Manual
Excel 2007[c] The Missing Manual
ISBN: 596527594
Year: 2007
Pages: 173 © 2008-2017.
If you may any questions please contact us: