Options: You can change the default sorting scheme from top to bottom so that sorting rules are executed from left to right, and you can check a box to make your sort case sensitive.
Custom sorting
478 Sorting and Filtering in Tables and Worksheets
✦ By Color: If you have applied color formats to a table, you can use this pop-up menu to sort by cell color or font color. If you have applied con- ditional formatting (see Chapter 4 of this minibook), then you can sort by the symbol you applied. If you haven’t applied colors or conditional formatting, this button is inactive.
Figure 5-5:
Displaying the Filter dialog.
Filtering
Beneath the Sort functionality is the Filter section of the Filter dialog. (Refer to Figure 5-5.) Usually, you know what you’re looking for in a column, so the first thing to do is either type what you want in the search filter or choose it from the Choose One pop-up menu and form field. Starting at the top of the Filter options shown in Figure 5-6 you can choose:
✦ By Color: Show records in your column that match the cell color, font color, or cell icon. If you haven’t applied colors or conditional format- ting, this pop-up menu is inactive.
✦ Choose One: Select a criterion from this pop-up menu. Then, in the pop-up menu to the right, you can select a record from the column that matches the set of conditions. When you make your first condition set, the dialog changes to enable you to select And or Or to add a second set of filter criteria, as shown in Figure 5-6. As you change your criteria set- tings, observe that the filter check boxes are turned on and off for you to match your criteria.
✦ Check boxes: You can select and deselect these boxes to display only rows that match the selected items.
✦ Clear Filter button: Removes all criteria from the entire Filter dialog so that no filter or sorting is performed.
Book III Chapter 5
Making, Formatting,and Filtering a Table
479
Calculating Columns
Figure 5-6:
Filtering with logic.
Calculating Columns
Table Options is one of the most useful Excel tools because of its ability to quickly do mathematical and statistical operations on table columns by displaying the Total row. A Total row lets you perform any calculation (even advanced statistical calculations) for the cells in the column.
To display the Total row, go to the Ribbon’s Tables tab, find the Table Options group, and then select the Total Row check box. The Total row dis- plays at the bottom of your table, as shown in Figure 5-7. Click in any cell of the Total row to display a button that produces a pop-up menu from which to choose popular functions. Excel uses the SUBTOTAL worksheet function for many of these calculations.
By choosing the More Functions option, you display the Formula Builder (see Chapter 3 of this minibook), which gives you easy access to all the func- tions in Excel for the Total row.
Figure 5-7:
Formulating in the Total Row.
480 Managing Tables
Managing Tables
Sometimes, tables need some maintenance. Perhaps a row was accidentally duplicated. Maybe you need to rename the table, or the table isn’t useful anymore and you want to change it back to normal Excel cells. The following sections show you how to do all these table management tasks.
Removing duplicate rows
A common task is to identify and then remove duplicate records (a row is a record) in a table. You can use the Remove Duplicates tool to make that task easier. First select a cell in a table. Then go to the Ribbon’s Tables tab, find the Tools group, and click Remove Duplicates to display the Remove Duplicates dialog shown in Figure 5-8. Choose a column to analyze by select- ing its field name check box. Excel will instantly report how many duplicates it found and put a marker to the right of each row containing a duplicate record. Click the Remove Duplicates button to delete rows containing dupli- cate records.
By default, the Remove Duplicate option analyzes all the columns in your table, and it finds duplicates only if entire rows (records) have the same data.
Figure 5-8:
Removing duplicate records.
Duplicate row
Renaming a table
Like everything you put on a worksheet, a table has a name. The default name is something nondescript, such as Table1. (See Figure 5-8.) To give your table a descriptive name so that you can select it using the Name box or a macro, go to the Ribbon’s Tables tab, find the Tools group, and click Rename. No dialog comes up to rename the table. Instead, the current name of the table will be highlighted in the Name box, where you simply type a new name for your table, and then press Return or Enter.
Book III Chapter 5
Making, Formatting,and Filtering a Table
481
Making a PivotTable
If you rename a table after you’ve referred to its name by formula, the for- mula will update, but if you rename a table after using its name in a macro, you have to manually edit the name of the table in your macro. If you don’t update the macro, it won’t work.
Untabling a table
We know untabling isn’t a word, but we couldn’t come up with a better one to describe the process of converting a table into an ordinary spreadsheet range.
The official terminology for this process is to convert a table to a range object.
It’s easy enough to do. Select a cell in the table and then go to the Ribbon’s Tables tab. Find the Tools group and click Convert to Range. Say goodbye to those nice number formats, but your Total row formulas will persist.
Making a PivotTable
A PivotTable is a special kind of table that summarizes data from a table, data range, or database external to the workbook. If you’re PivotTable aficio- nado, you will be in seventh heaven with the new PivotTable capabilities in Excel 2011. Using the example data from Figure 5-1 (shown earlier), we gen- erated the nice PivotTable report shown in Figure 5-9 in just a few seconds.
Here’s how to make a PivotTable:
1. (Optional) Select a cell in your data range or table.
Your data range must obey the table rules we set out in the earlier sec- tion, “Listing the Table Rules.”
2. Choose Data➪PivotTable. Alternatively, on the Ribbon’s Tables tab, go to the Tools group and click Summarize with PivotTable.
The Create PivotTable dialog displays, as shown in Figure 5-9.
3. Choose the data to analyze:
Make choices from the following options:
• Location: If you performed Step 1, your table or range is already filled in for you. If you didn’t start with a table or range, you can select a data range or table using the mouse.
• Use an External Data Source: Displays the Mac OS X ODBC dialog, which we describe in Chapter 8 of this minibook.
4. Choose where to put the PivotTable:
• New Worksheet: If selected, adds a new sheet to the workbook and places your PivotTable in Cell A1 of the new worksheet.
• Existing Worksheet: Choose a cell on your worksheet. The cell will be the upper-leftmost corner of your PivotTable. Make sure there’s enough room so your PivotTable doesn’t overlap existing cell ranges.
482 Making a PivotTable
Figure 5-9:
Choosing the data and report location.
5. Click OK.
The PivotTable Builder displays, as shown in Figure 5-10.
6. Drag field names from the Field Name section at the top to the panes below.
• Selecting and deselecting the field names includes or excludes the columns from the pivot table.
• Clicking the pop-up buttons within the pivot table displays Filter dia- logs appropriate for the data type in your pivot table.
• You can filter the Field Name list by typing field names in the search box in the Pivot Table Builder dialog.
• Drag fields from one pane to another to generate new pivot table variations.
Figure 5-10:
Building a PivotTable.
Click to filter
Book III Chapter 5
Making, Formatting,and Filtering a Table
483
Making a PivotTable
You can change the column names, calculations, and number formats pro- vided by the PivotTable Builder. There’s a little information button at the right end of each field name in the panels at the bottom of the PivotTable Builder. Click the information button to display the PivotTable Field dialog, shown in Figure 5-11. The properties displayed are for the field name of the button you clicked:
✦ Field Name (Optional): Type a new field name.
✦ Summarize By: Choose which type of calculation to use.
✦ Show Data As: Select how you want to show the data from the pop-up menu. You can choose from Normal, Difference From, % Of, % Difference From, Running Total In, % of Row, % of Column, % of Total, or Index.
✦ Base Field and Base Item: If you choose Difference From in the Show Data As pop-up menu, choose which fields you’re comparing.
✦ Delete: Removes this field from the PivotTable report.
✦ Number: Displays the Number tab of the Format Cells dialog so you can choose a number format or make a custom number format.
Figure 5-11:
Building a PivotTable.
When you select a cell in a PivotTable, look at the Ribbon to find the PivotTable tab, which you click to display all sorts of PivotTable tools. The PivotTable tab is for experts. PivotTable Ribbon offers additional formatting options and still more controls for your PivotTable, but it goes beyond the scope of this book. If you find PivotTables to be useful, then by all means explore the PivotTable Ribbon.
484 Book III: Excel 2011