Microsoft Excel 2016 BIBLE (2016)

IN THIS CHAPTER

1. Understanding how a table differs from a normal range

2. Working with tables

3. Using the Total Row

4. Removing duplicate rows from a table

5. Sorting and filtering a table

A 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 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 provided 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.

Excel tables have many advantages and only one disadvantage. For some reason, Excel's custom views feature is disabled if your workbook contains at least one table. No one has ever provided a logical reason why tables are not compatible with custom views. I discuss custom views in Chapter 9, “Printing Your Work.”

Caution

If you'd like to practice working with tables, the workbook shown here is available on this book's website at www.wiley.com/go/excel2016bible. The file is named real estate table.xlsx.

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 data 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.

Note

Excel may not guess the table's dimensions correctly if the table isn't separated from other information by at least one empty row or column. If Excel guesses incorrectly, just specify the exact range for the table in the Create Table dialog box. Better yet, click Cancel and rearrange your worksheet such that the table is separated from your other data by at least one blank row or column.

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 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, “Worksheet Formatting”). 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 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.

Figure 5.5 Excel offers many different table styles.

Tip

To change the default table style for the workbook, right-click the style in the Table Styles group and choose Set As Default from the shortcut menu. Subsequent tables that you create in that workbook will use that style.

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.

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.

Tip

If applying table styles isn't working, it's probably because the range was already formatted before you converted it to a table. Table formatting doesn't override normal formatting. To clear existing background fill colors, select the entire table and choose Home Font Fill Color No Fill. To clear existing font colors, choose Home Font Font Color Automatic. To clear existing borders, choose Home Font Borders No Borders. After you issue these commands, the table styles should work as expected.

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.

Figure 5.6 Use this dialog box to create a new table style.

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.

Tip

If you want to make changes to an existing table style, locate it in the Ribbon and right-click. Then choose Duplicate from the shortcut menu. Excel displays the Modify Table Quick Style dialog box with all the settings from the specified table style. Make your changes, give the style a new name, and click OK to save it as a custom 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 of the table.

Selecting parts of a table

When you move around your mouse 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.

Tip

Right-clicking a cell in a table displays several selection options in the shortcut menu.

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 into the row below a table, Excel extends the table vertically to include the new row.

Note

An exception to automatically extending tables is when the table is displaying a total row. If you enter data below the total row, the table won't be extended and the data won't be part of the table.

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

Tip

When the cell pointer is in the bottom-right cell of a table, pressing Tab inserts a new row at the bottom of the table, above the total row (if the table has one).

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.

Excel Remembers

When you do something with a complete column in a table, Excel remembers that and extends that “something” to all new entries added to that column. For example, if you apply currency formatting to a column and then add a new row, Excel applies currency formatting to the new value in that column.

The same thing applies to other operations, such as conditional formatting, cell protection, data validation, and so on. And if you create a chart using the data in a table, the chart will be extended automatically if you add new data to 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):

· 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.

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

Caution

If you have a formula that refers to a value in the total row of a table, the formula returns an error if you hide the total row. But if you make the total row visible again, the formula works as it should.

For more information about formulas, including the use of formulas in a table column, see Chapter 10, “Introducing Formulas and Functions.”

Removing duplicate rows from a table

If data in a table was compiled from multiple sources, the table may contain duplicate items. Often, you want to eliminate the duplicates. In the past, removing duplicate data was essentially a manual task, but removal is 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 the 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.

Figure 5.8 Removing duplicate rows from a table is easy.

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.

Tip

Data does not have to be in the form of a designated table to remove duplicates. To remove duplicate rows from a normal range, choose Data Data Tools Remove Duplicates.

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/2016, and the other is formatted to display as May 15, 2016. When removing duplicates, Excel considers these dates to be different. You can avoid such a problem by applying consistent formatting for all data in a column.

Sorting and filtering a table

Each item in the Header Row of a table contains a drop-down arrow known as a Filter Button. When clicked, the Filter Button displays sorting and filtering options (see Figure 5.9).

Figure 5.9 Each column in a table has sorting and filtering options.

Tip

If you don't plan to sort or filter the data in a table, you can turn off the display of Filter Buttons in a table's Header Row. Choose Table Tools Design Table Style Options Filter Button to display or hide the drop-down arrows.

Sorting a table

Sorting a table rearranges the rows based on the contents of a particular column. You may want to sort a table to put names in alphabetical order. Or, maybe you want to sort your sales staff by the total sales made.

To sort a table by a particular column, click the Filter Button in the column header and choose one of the sort commands. The exact command varies, depending on the type of data in the column.

You can also select Sort by Color to sort the rows based on the background or text color of the data. This option is relevant only if you've overridden the table style colors with custom formatting.

You can sort on any number of columns. The trick is to sort the least significant column first and then proceed until the most significant column is sorted last. For example, in the real estate table, you may want to sort the list by agent. And within each agent's group, sort the rows by area. And within each area, sort the rows by list price. For this type of sort, first sort by the List Price column, then sort by the Area column, and then sort by the Agent column. Figure 5.10 shows the table sorted in this manner.

Figure 5.10 A table after performing a three-column sort.

Note

When a column is sorted, the Filter Button in the header row displays a different graphic to remind you that the table is sorted by that column.

Another way of performing a multiple-column sort is to use the Sort dialog box (choose Home Editing Sort & Filter Custom Sort). Or right-click any cell in the table and choose Sort Custom Sort from the shortcut menu.

In the Sort dialog box, use the drop-down lists to specify the sort specifications. In this example, you start with Agent. Then click the Add Level button to insert another set of search controls. In this new set of controls, specify the sort specifications for the Area column. Then add another level and enter the specifications for the List Price column. Figure 5.11 shows the dialog box after entering the specifications for the three-column sort. This technique produces the same sort as described in the previous paragraph.

Figure 5.11 Using the Sort dialog box to specify a three-column sort.

Filtering a table

Filtering a table refers to displaying only the rows that meet certain conditions. (The other rows are hidden.)

Note that entire worksheet rows are hidden. Therefore, if you have other data to the left or right of your table, that information may also be hidden when you filter the table. If you plan to filter your list, don't include any other data to the left or right of your table.

Using the real estate table, assume that you're interested only in the data for the N. County area. Click the Filter Button in the Area Row Header and remove the check mark from Select All, which unselects everything. Then place a check mark next to N. County and click OK. The table, shown in Figure 5.12, is now filtered to display only the listings in the N. County area. Notice that some of the row numbers are missing. These rows are hidden and contain data that does not meet the specified criteria.

Figure 5.12 This table is filtered to show only the information for N. County.

Also notice that the Filter Button in the Area column now shows a different graphic — an icon that indicates the column is filtered.

You can filter by multiple values in a column using multiple check marks. For example, to filter the table to show only N. County and Central, place a check mark next to both values in the drop-down list in the Area Row Header.

You can filter a table using any number of columns. For example, you may want to see only the N. County listings in which the Type is Single Family. Just repeat the operation using the Type column. All tables then display only the rows in which the Area is N. County and the Type is Single Family.

For additional filtering options, select Text Filters (or Number Filters, if the column contains values). The options are fairly self-explanatory, and you have a great deal of flexibility in displaying only the rows that you're interested in. For example, you can display rows in which the List Price is greater than or equal to $200,000 but less than $300,000 (see Figure 5.13).

Figure 5.13 Specifying a more complex numeric filter.

Also, you can right-click a cell and use the Filter command on the shortcut menu. This menu item leads to several additional filtering options that enable you to filter data based on the contents of the selected cell. You can also filter by formatting. This is useful if you've applied conditional formatting to cells in the table. See Chapter 21, “Visualizing Data Using Conditional Formatting.”

Note

As you may expect, when you use filtering, the total row is updated to show the total only for the visible rows.

When you copy data from a filtered table, only the visible data is copied. In other words, rows that are hidden by filtering aren't copied. This filtering makes it easy to copy a subset of a larger table and paste it to another area of your worksheet. Keep in mind, though, that the pasted data is not a table — it's just a normal range. You can, however, convert the copied range to a table.

To remove filtering for a column, click the drop-down in the Row Header and select Clear Filter. If you've filtered using multiple columns, it may be faster to remove all filters by choosing Home Editing Sort & Filter Clear.

Filtering a table with slicers

Another way to filter a table is to use one or more slicers. This method is less flexible but more visually appealing. Slicers are particularly useful when the table will be viewed by novices or those who find the normal filtering techniques too complicated. Slicers are very visual, and it's easy to see exactly what type of filtering is in effect. A disadvantage of slicers is that they take up a lot of room on the screen.

To add one or more slicers, activate any cell in the table and choose Table Tools Design Tools Insert Slicer. Excel responds with a dialog box that displays each header in the table (see Figure 5.14).

Figure 5.14 Use the Insert Slicers dialog box to specify which slicers to create.

Place a check mark next to the field(s) that you want to filter. You can create a slicer for each column, but that's rarely needed. In most cases, you'll want to be able to filter the table by only a few fields. Click OK, and Excel creates a slicer for each field you specified.

A slicer contains a button for every unique item in the field. In the real estate listing example, the slicer for the Agent field contains 14 buttons because the table has records for 14 different agents.

Note

Slicers may not be appropriate for columns that contain numeric data. For example, the real estate listing table has 78 different values in the List Price column. Therefore, a slicer for this column would have 78 buttons (and there's no way to group the values into numeric ranges). This is an example of how a slicer is not as flexible as normal filtering using Filter Buttons.

To use a slicer, just click one of the buttons. The table displays only the rows that have a value that corresponds to the button. You can also press Ctrl to select multiple buttons and press Shift to select a continuous group of buttons — which would be useful for selecting a range of List Price values.

If your table has more than one slicer, it's filtered by the selected buttons in each slicer. To remove filtering for a particular slicer, click the Clear Filter icon in the upper-right corner of the slicer.

New

Excel 2016 displays a new icon in slicers: Multi-Select. If you click this icon, you can select multiple items without pressing Ctrl. This makes it easier for people who use touch screens.

Use the tools in the Slicer Tools Options context menu to change the appearance or layout of a slicer. You have quite a bit of flexibility.

Figure 5.15 shows a table with two slicers. The table is filtered to show only the records for Adams, Barnes, Lang, and Robinson in the N. County area.

Figure 5.15 The table is filtered by two slicers.

Converting a table back to a range

If you need to convert a table back to a normal range, just select a cell in the table and choose Table Tools Design Tools Convert to Range. The table style formatting remains intact, but the range no longer functions as a table. In addition, any filtering you applied is canceled, and all hidden rows are displayed.