Chapter 5: Introducing Tables

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

A very common type of spreadsheet contains information in a structured list, also known as 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 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 tables. As always, I urge you to just dig in and experiment with the various table-related commands. You may be surprised by what you can accomplish with just a few mouse clicks.

What Is a Table?

A table is 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.

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. And if you enter a formula into a cell, Excel will propagate the formula to other rows in 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.

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.

Figure 5.2

An Excel table.

What's the difference between a standard range and a table? With a table

• Activating any cell in the table gives you access to the Table Tools contextual tab on the Ribbon (see Figure 5.3).

Figure 5.3

When you select a cell in a table, you can use the commands located on the Table Tools ⇒ Design tab.

• The cells contain background color and text color formatting. This formatting is optional.

• Each column header contains a Filter Button — a drop-down list that you can use to sort the data or filter the table to display only rows that meet certain criteria. Displaying the Filter Button is optional.

• You can create easy to use Slicers to simplify filtering data.

• If the active cell is within the table, when you scroll down the sheet 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.

• Tables support structured references. Instead of using cell references, formulas can use table names and column headers.

• 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).

• Selecting rows and columns within the table is simplified.

All these concepts will become clearer later on.

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.

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, just 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 column headers with more meaningful text.

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 (see Chapter 6). 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 5.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. And yes, some are really ugly and practically illegible.

For a different set of color choices, choose Page Layout ⇒ Themes ⇒ Themes to select a different document theme.

 For more information about themes, see Chapter 6.

Figure 5.5

Excel offers many different table styles.

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 elements 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 Quick Style dialog box shown in Figure 5.6. You can customize 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. Unfortunately, custom table styles are available only in the workbook in which they were created.

Figure 5.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, but 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.

• 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 Row and the Total Row, if it has one). You can also press Ctrl+spacebar (once or twice) to select a column.

• 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+spacebar to select a table row.

• 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 Total Row). You can also press Ctrl+A (once or twice) to select the entire table.

Adding new rows or columns

To add a new column to the end of a table, select a cell in the column to the right of the table and start entering the data. Excel automatically extends the table horizontally and adds a generic column name for the new column.

Similarly, if you enter data in the row below a table, Excel extends the table vertically to include the new row.

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:

• Table Columns to the Left

• Table Columns to the Right

• Table Rows Above

• Table Rows Below

When you move your mouse to the resize handle at the bottom-right cell of a table, the mouse pointer turns into a diagonal line with two arrowheads. 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 Column1, Column2, and so on. Typically, you'll want to change these names to more descriptive labels. Just select the cell and overwrite the generic text with your new text.

Deleting rows or columns

To delete a row (or column) in a table, select any cell in the row (or column) to be deleted. To delete multiple rows or columns, select a range of cells. 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 (which could be in a different workbook), you can drag and drop it as well — as long as the destination worksheet is visible onscreen.

Or, you can use these steps to move a table to different worksheet or workbook:

1. Press Ctrl+A twice to select the entire table.

2. Press Ctrl+X to cut the selected cells.

3. Activate the new worksheet and select the upper-left cell for the table.

4. Press Ctrl+V to paste the table.

Working with the Total Row

The Total Row in a table contains formulas that summarize the information in the columns. When you create a table, the Total Row isn't turned on. To display the Total Row, choose Table Tools ⇒ Design ⇒ Table Style Options and put a check mark next to Total Row.

By default, a Total Row displays the sum of the values in a column of numbers. In some cases, you'll want a different type of summary formula. When you select a cell in the Total Row, a drop-down arrow appears in the cell. Click the arrow, and you can select from a number of other summary formulas (see Figure 5.7):

Figure 5.7

Several types of summary formulas are available for the Total Row.

• None: No formula.

• Average: Displays the average of the numbers in the column.

• Count: Displays the number of entries in the column. (Blank cells are not counted.)

• Count Numbers: Displays the number of numeric values in the column. (Blank cells, text cells, and error cells are not counted.)

• Max: Displays the maximum value in the column.

• Min: Displays the minimum value in the column.

• Sum: Displays the sum of the values in the column.

• StdDev: Displays the standard deviation of the values in the column. (Standard deviation is a statistical measure of how “spread out” the values are.)

• Var: Displays the variance of the values in the column. (Variance is another statistical measure of how “spread out” the values are.)

• More Functions: Displays the Insert Function dialog box so that you can select a function that isn't in the list.

 For more information about formulas, including the use of formulas in a table column, see Chapter 10.

Removing duplicate rows from a table

If data in a table was compiled from multiple sources, the table may contain duplicate items. Most of the time, you want to eliminate the duplicates. In the past, removing duplicate data was essentially a manual task, but it's very easy if the data is in a table.

Start by selecting any cell in your table. Then choose Table Tools ⇒ Design ⇒ Tools ⇒ Remove Duplicates. Excel responds with Remove Duplicates dialog box shown in Figure 5.8. The dialog box lists all the columns in your table. Place a check mark next to the columns that you want to be included in the duplicate search. Most of the time, you'll want to select all the columns, which is the default. Click OK, and Excel weeds out the duplicate rows and displays a message that tells you how many duplicates it removed.

When you select all columns in the Remove Duplicates dialog box, Excel will delete a row only if the content of every column is duplicated. In some situations, you may not care about matching some columns, so you would deselect those columns in the Remove Duplicates dialog box. When duplicate rows are found, the first row is kept and subsequent duplicate rows are deleted.

Figure 5.8

Removing duplicate rows from a table is easy.

Caution

It's important to understand that duplicate values are determined by the value displayed in the cell — not necessarily the value stored in the cell. For example, assume that two cells contain the same date. One of the dates is formatted to display as 5/15/2012, and the other is formatted to display as May 15, 2012. When removing duplicates, Excel considers these dates to be different.