Introducing Tables
Many worksheets are designed to store data in a table. A table is a rectangular range of data that usually has a row of text headings to describe the contents of each column. Excel’s table feature
(introduced in Excel 2007) makes common tasks much easier — and a lot better looking. More importantly, the table features may help eliminate some common errors.
This chapter is a basic introduction to Excel table features. As always, I urge you to just dig in and experiment with the various table-related commands. You may be surprised at what you can accomplish with just a few mouse clicks.
What Is a Table?
A table is simply a rectangular range of structured data. Each row in the table corresponds to a single entity. For example, a row can contain information about a customer, a bank transaction, an employee, a product, and so on. Each column contains a specific piece of information. For example, if each row contains information about an employee, the columns can contain data such as name, employee number, hire date, salary, department, and so on. Tables typically have a header row at the top that describes the information contained in each column.
CHAPTER
IN THIS CHAPTER
Understanding how a table differs from a normal range
Working with tables
Using the Total Row
Removing duplicate rows from a table
Sorting and filtering a table
99
Part I: Getting Started with Excel
Setting up data like this in a range of cells is very straightforward. The magic happens when you tell Excel to convert a range of data into an “official” table. You do this by selecting any cell within the range and then choosing Insert Tables Table.
When you explicitly identify a range as a table, Excel can respond more intelligently to the actions you perform with that range. For example, if you create a chart from a table, the chart will expand automatically as you add new rows to the table.
Figure 5.1 shows a range of data that has not yet been converted to a table. Notice that this range corresponds to the description I provide earlier: It’s a range of structured data with column headers. In this example, each row contains information about a single real estate listing. The range has 10 columns and 125 rows of data (plus a descriptive header row).
FIGURE 5.1
This range of data is a good candidate for a table.
Figure 5.2 shows the range after I converted it to a table by choosing Insert Tables Table.
On the CD
If you’d like to practice working with tables, the workbook shown here is available on the CD-ROM. The file is named real estate table.xlsx.
100
Chapter 5: Introducing Tables
FIGURE 5.2
An Excel table.
What’s the difference between a standard range and table?
•Activating any cell in the table gives you access to the Table Tools contextual tab on the Ribbon (see Figure 5.3).
•The cells contain background color and text color formatting. This formatting is optional.
•Each column header contains a drop-down list, which you can use to sort the data or filter the table to hide specific rows.
•If the active cell is within the table, when you scroll the sheet down so that the header row disappears, the table headers replace the column letters in the worksheet header.
•Tables support calculated columns. A single formula in a column is automatically propagated to all cells in the column. (See Chapter 10.)
•Tables support structured references. Rather than using cell references, formulas can use table names and column headers. (See Chapter 10.)
•The lower-right corner of the lower-right cell contains a small control that you can click and drag to extend the table’s size, either horizontally (add more columns) or vertically (add more rows).
•Excel can remove duplicate rows automatically.
•Selecting rows and columns within the table is simplified.
101
Part I: Getting Started with Excel
FIGURE 5.3
When you select a cell in a table, you can use the commands located on the Table Tools Design tab.
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 details later. The following instructions assume that you already have a range of data that’s suitable for a table.
1.Make sure that the range doesn’t contain any completely blank rows or columns; otherwise, Excel will not guess the table range correctly.
2.Select any cell within the range.
3.Choose Insert Tables Table (or press Ctrl+T). Excel responds with its Create Table dialog box, shown in Figure 5.4. 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.
FIGURE 5.4
Use the Create Table dialog box to verify that Excel guessed the table dimensions correctly.
102