Excel 2016 All-in-One For Dummies (2016)

Book VI

Data Management

Find out about sorting data in the article “Sorting on Multiple Fields in Excel 2016” online at www.dummies.com/extras/excel2016aio.

Contents at a Glance

1. Chapter 1: Building and Maintaining Data Lists

1. Data List Basics

2. Sorting Data

3. Subtotaling Data

2. Chapter 2: Filtering and Querying a Data List

1. Data List Filtering 101

2. Filtering Data

3. Using the Database Functions

4. External Data Query

Chapter 1

Building and Maintaining Data Lists

In This Chapter

Setting up a data list

Adding data to a data list

Editing records in a data list

Finding records in a data list

Sorting records on values in a data list

Sorting a list on font color, fill color, or cell icons

Subtotaling data in a data list

In addition to its considerable computational capabilities, Excel is also very accomplished at maintaining vast collections of related data in what are referred to as database tables or, more often, data lists (which is a little more accurate). This chapter covers all the basic procedures for creating and then maintaining different types of data lists in the Excel worksheet.

This basic information includes how to design the basic data list and then format it as a table so that you can add new data to the list without having to redefine it and can sort its data so that it’s arranged the way you like to see the information. For data lists that contain numerical data, you also find out how to subtotal and total the data. For information on how to find data in the data list and produce subsets of the list with just the data you need, refer to Book VI, Chapter 2.

Data List Basics

In Excel, a data list, or database table, is a table of worksheet data that utilizes a special structure. Unlike the other types of data tables that you might create in an Excel spreadsheet, a data list uses only column headings (technically known as field names) to identify the different kinds of items that the data list tracks. Each column in the data list contains information for each item you track in the database, such as the client’s company name or telephone number (technically known as a field of the data list). Each row in the data list contains complete information about each entity that you track in the data list, such as ABC Corporation or National Industries (technically known as a record of the data list).

After you’ve organized your data into a data list that follows this structure, you can then use a variety of commands on the Ribbon’s Data tab to maintain the data, as well as to reorder the information it contains. In data lists with numerical fields, you can also use the Subtotal command button to calculate subtotals and totals in the list when a certain field changes.

Designing the basic data list

All you have to do to start a new data list in a worksheet is to enter the names of the fields that you want to track in the top row of the worksheet, enter the first record of data beneath, and then format the two rows of data as a table. (See Book II, Chapter 1 for details.) When entering the field names (as column headings), be sure each field name in the data list is unique and, whenever possible, keep the field name short. When naming fields, you can align the field name in the cell so that its text wraps to a new line by clicking the Wrap Text command button on the Ribbon’s Home tab after entering the name in its cell (Alt+HW). Also, you should not use numbers or formulas that return values as field names. (You can, however, use formulas that return text, such as a formula that concatenates labels entered in different cells.)

When deciding on what fields you need to create, you need to think of how you’ll be using the data that you store in your data list. For example, in a client data list, you split the client’s name into separate title, first name, and last name fields if you intend to use this information in generating form letters and mailing labels with your word processor. That way, you are able to address the person by his or her first name (as in Dear Jane) in the opening of the form letter you create, as well as by his or her full name and title (as in Dr. Jane Jackson) in the mailing label you generate.

Likewise, you split up the client’s address into separate street address, city, state, and zip code fields when you intend to use the client data list in generating form letters, and you also want to be able to sort the records in descending order by zip code and/or send letters only to clients located in the states of New York, New Jersey, or Connecticut. By keeping discrete pieces of information in separate fields, you are assured that you will be able to use that field in finding particular records and retrieving information from the data list, such as finding all the records where the state is California or the zip code is between 94105 and 95101.

To set up a new data list in a worksheet, you follow these steps:

1. Click the blank cell where you want to start the new data list and then enter the column headings (field names) that identify the different kinds of items you need to keep track of.

After creating the fields of the data list by entering their headings, you’re ready to enter the first row of data.

2. Make the first entries in the appropriate columns of the row immediately below the one containing the field names.

These entries in the first row beneath the one with the field names constitute the first record of the data list.

3. Click the Format as Table button on the Ribbon’s Home tab and then click a thumbnail of one of the table styles in the drop-down gallery.

As soon as you click the Format as Table button, a marquee appears around all the cells in the new data list including the top row of field names. As soon as you click a table style in the drop-down gallery, the Format As Table dialog box appears, listing the address of the cell range enclosed in the marquee in the Where Is the Data for Your Table text box, and the My Table Has Headers check box is selected.

4. Click the OK button to close the Format As Table dialog box.

Excel formats your new data list in the selected table format and adds AutoFilter (drop-down buttons) to each of the field names in the top row.

Figure 1-1 shows you a brand new employee data list after formatting the first row with the field names and second row with the first data record as a table using Table Style Light 1. This new data list begins in row 1 of this worksheet, which contains the column headings with the names for the ten fields in this data list (ID No through Profit Sharing) all with AutoFilter buttons (thanks to the formatting as a table). Note that employees’ names are divided into separate First Name and Last Name fields in this list (columns B and C, respectively). Note too, that the first actual record of the data list is entered in row 2 of the worksheet, directly under the row with the field names. When entering your records for a new data list, you don’t skip rows but keep entering each record one above the other going down successive rows of the worksheet.

Figure 1-1: Creating an employee data list with the row of field names and first data record.

When you’re entering the row with the first data record, be sure to format all the cells the way you want the entries in that field to appear in all the subsequent data records in the data list. For example, if you have a Salary field in the data list, and you want the salaries formatted with the Currency style number format without any decimal places, be sure to format the salary entry in the first record in this manner. If you have a Zip Code field, format it with the Special Zip Code format or as Text so that Excel doesn’t drop the initial zeros from codes that begin with them such as 00234. That way, all subsequent records will pick up that same formatting for their respective fields when you enter them with Excel’s data form.

Creating calculated fields

When creating a new data list, you can make full use of Excel’s calculating capabilities by defining fields whose entries are returned by formula rather than entered manually. The sample employee list introduced in Figure 1-1 contains just such a calculated field (shown on the Formula bar) in cell I2 that contains the first entry in the Years of Service field.

The original formula for calculating years of service in cell I2 is as follows:

=YEAR(TODAY())-YEAR(H2)

This formula uses the YEAR Date function to subtract the serial number of the year in which the employee was hired (entered into the Date Hired field) in cell H2 from the serial number of the current year (returned by the TODAY function). After entering and formatting this original formula in cell I2, the data table picks up this formula and automatically copies it and applies it to any new record you add to the data list.

Modifying the structure of the data list

You may find after creating your data list that you need to modify its structure by adding or deleting some fields. To add a new field, you select the column (by clicking the column letter) where you want the field inserted, and then click the Insert command button on the Ribbon’s Home tab to insert a new column. Replace the generic Column1 field name given to the new field in the top row with a descriptive name and then enter the entries for that field for each record in the data list. To delete an entire field from the data list (field name and entries), select its column and then click the Delete command button on the Home tab.

To avoid losing data or disturbing the layout of data located outside of the data list caused by adding or deleting its fields, don’t place any data tables or other entries in rows beneath the last row of the data list. In other words, always keep the rows used by the columns of the data list free for new records by locating all related data in columns to the right of the last field.

Add new records to a data list

After creating the field names and one record of the data list and formatting them as a table, you’re ready to start entering the rest of the records in subsequent rows of the list. The most direct way to do this is to press the Tab key when the cell cursor is in the last cell of the first record. Doing this causes Excel to add an extra row to the data list, where you can enter the appropriate information for the next record.

When doing data entry directly in a data list table, press the Tab key to proceed to the next field in the new record rather than the → key. That way, when you complete the entry in the last field of the record, you automatically extend the data list, add a new record, and position the cell cursor in the first field of that record — if you press → to complete the entry, Excel simply moves the cell cursor to the next cell outside of the data list table.

Adding the Form button to the Quick Access toolbar

Instead of entering the records of a data list directly in the table, you can use Excel’s data form to make the entries. The only problem with using the data form is that its command button is not found anywhere on the Ribbon: The only way to access the data form is by adding its command button as a custom Ribbon tab or to the Quick Access toolbar.

To add the Form button to the Quick Access toolbar, you follow these steps:

1. Click the Customize Quick Access Toolbar button at the end of the toolbar and then click the More Commands option on its drop-down menu.

Excel opens the Excel Options dialog box with the Quick Access Toolbar tab selected. The Form command button you want to add is available only when you select Commands Not in the Ribbon on All Commands from the Choose Commands From drop-down list.

2. Select Commands Not in the Ribbon from the Choose Commands From drop-down list and then click the Form button to select it.

3. Click the Add button to add the Form button to the end of the Quick Access toolbar.

4. Click OK to close the Excel Options dialog box and return to the worksheet with the data list.

Using the data form

The first time you click the custom Form button you’ve added to the Quick Access toolbar, Excel analyzes the row of field names and entries for the first record and creates a data form that lists the field names down the left side of the form, with the entries for the first record in the appropriate text boxes next to them.

Figure 1-2 shows you the data form that Excel creates for the sample Employee data list shown earlier in Figure 1-1. As you can see in this figure, the data form consists of a dialog box (whose title bar contains the name of the current worksheet file, which just happens to be Employee Data List) that contains a vertical listing of each field defined for the data list.

Figure 1-2: Opening the data form in the new data list to add a new record.

When you click the custom Form button on the Quick Access toolbar to display the data form, Excel automatically displays the field entries for the first record entered (which just happens to be the only record in the list at this point). On the right side of the dialog box, the data form indicates the current record number out of the total number of records in the data list (1 of 1 in this case). This part of the form also contains a number of command buttons that enable you to add a new record, find a particular record for editing, or delete a record from the data list.

When the data form is displayed in the active document, you can use the scroll bar to the right of the fields to move through the records in the data list, or you can use various direction keys. Table 1-1 summarizes the use of the scroll bar and these keys. For example, to move to the next record in the data list, you can press the ↓ or the Enter key or click the scroll arrow at the bottom of the scroll bar. To move to the previous record in the data list (assuming that there’s more than one), you can press the ↑ key or Shift+Enter key or click the scroll arrow at the top of the scroll bar. To select a field in the current record for editing, you can click that field’s text box or press the Tab key (next field) or press Shift+Tab (previous field) until you select the field (and its current entry).

Table 1-1 Techniques for Navigating the Data Form

Note that the data form does not allow you to select and edit calculated fields (such as the Years of Service field shown earlier in Figure 1-2). Although calculated fields and their current entries are listed in the data form, the form doesn’t bother to provide a text box for the fields for making editing changes. To modify the contents of a calculated field, you would need to modify the original formula in the appropriate field in the first record and recopy the edited formula down to the other existing records in the list.

Adding new records with the data form

To add a new record to the data list, you can either move to the end of the data list (by dragging the scroll box to the very bottom of the scroll bar or by pressing Ctrl+↓ or Ctrl+PgDn) or simply click the New command button. Any way you do it, Excel displays a blank data form (marked New Record at the right side the dialog box), which you can then fill out. After entering the information for a field, press the Tab key to advance to the next field in the record. (Be careful not to press the Enter key because doing so inserts the new record into the data list.)

When you’re making an entry in a new field, you can copy the entry from the same field in the previous record into the current field by pressing Ctrl+” (double quotation mark). You can use this keystroke shortcut, for example, to carry forward entries in the text box for the State field when you are entering a series of records that all use the same state.

When you’ve entered all the information you have for the new record, press the ↓ or the Enter key or click the New button again. Excel then inserts the new record as the last record in the data list and displays a blank data form where you can enter the next record. When you finish adding records to the data list, press the Esc key or click the Close button to close the Data Form dialog box.

Editing records in the data form

The data form makes it easy to edit records in your data list. In a smaller data list, you can use the navigation keys or the scroll bar in the data form to locate the record that requires editing. In a larger data list, you can use the Criteria command button to quickly locate the record you need to change, as described in the next section.

When you’ve displayed the data form for the record that needs editing, you can then perform your editing changes by selecting the text boxes of the necessary fields and making your changes, just as you would edit the entry in its cell in the worksheet.

Finding records with the data form

You can use the Criteria button in the data form to find the records in your data list that you need to edit (or delete as described in the next section). When you click the Criteria button in the data form, Excel clears all the field text boxes so that you can enter the criteria to search for. For example, assume that you need to edit Sherry Caulfield’s profit sharing status. You don’t have her paperwork in front of you, so you can’t look up her employee number. You do know, however, that she works in the Boston office and, although you don’t remember exactly how she spells her last name, you do know that it begins with a C instead of a K.

To locate her record, you can at least narrow the search down to all the records where the Location field contains Boston and the employee’s Last Name begins with the letter C. To do this, you open the data form for the Employee data list, click the Criteria command button, and then enter the following in the Last Name field:

C*

Then, in the Location field, you enter

Boston

When entering the criteria for locating matching records in the data form, you can use the question mark (?) and the asterisk (*) wildcard characters, just as you do when using the Excel Find feature to locate cells with particular entries. (See Book II, Chapter 3, for a review of using these wildcard characters.)

When you click the Find Next button or press the Enter key, Excel locates the first record in the data list where the last name begins with the letter C and the location is Boston. This is William Cobb’s record. Then, to locate the next record that matches your criteria, you click the Find Next button or press Enter, which brings you to Sherry Caulfield’s record. Having located Sherry’s record, you can then change her profit sharing status by selecting the Profit Sharing text box and replacing No with Yes. Excel inserts the editing change that you make in the record’s data form into the data list itself as soon as you close the Data Form dialog box by clicking the Close button.

When using the Criteria button in the data form to find records, you can use the following logical operators when entering search criteria in fields that use numbers or dates:

· Equal to (=): Finds records with the same text, value, or date you enter.

· Greater than (>): Finds records after the text characters (in the alphabet) or the date, or larger than the value you enter.

· Greater than or equal to (>=): Finds records the same as the text characters, date, or value you enter or after the characters (in the alphabet), after the date, or larger than the value.

· Less than (<): Finds records before the text characters (in the alphabet) or date or smaller than the value you enter.

· Less than or equal to (<=): Finds records the same as the text characters, date, or value you enter or before the characters (in the alphabet) or the date, or larger than the value.

· Not equal to (<>): Finds records not the same as the text, value, or date you enter.

For example, to find all the records where the employee’s annual salary is $50,000, you can enter =50000 or simply 50000 in the Salary field text box. However, to find all the records for employees whose annual salaries are less than or equal to $35,000, you enter <=35000 in the Salary field text box. To find all the records for employees with salaries greater than $45,000, you would enter >45000 in the Salary field text box instead. If you wanted to find all of the records where the employees are female and make more than $35,000, you would enter F in the Gender field text box and >35000 in the Salary field text box in the same Criteria data form.

When specifying search criteria that fit a number of records, you may have to click the Find Next or Find Prev button several times to locate the record you want to work with. If no record fits the search criteria you enter in the Criteria data form, your computer will beep at you when you click the Find Next or Find Prev button.

To change your search criteria, select the appropriate text box(es) and delete the old criteria and then enter the new criteria. To switch back to the current record without using the search criteria you enter, click the Form button. (This button replaces the Criteria button as soon you click the Criteria button.)

Deleting records with the data form

In addition to adding and editing records with the data form, you can also delete them. To delete a record, you simply display its data form and then click the Delete button. Be very careful when deleting records, however, because you cannot restore the records you delete with Excel’s Undo feature. For this reason, Excel displays an alert dialog box whenever you click the Delete button, indicating that the record displayed in the data form is about to be permanently deleted. To continue and remove the record, you need to click OK or press Enter. To save the current record, press the Esc key or click the Cancel button instead.

Keep in mind that although you can use the Criteria data form to locate a group of records that you want to delete, you can remove only one record at a time with the Delete button.

Eliminating records with duplicate fields

You can use Excel’s Eliminate Duplicates feature to quickly find and remove duplicate records from a list (or rows from a table). This is a great feature especially when you’re dealing with a really large data list in which several different people do the data entry and which should not have any duplicate records (such as client lists, personnel files, and the like).

To have Excel remove all duplicate records from a data list or table, you follow these simple steps:

1. Position the cell cursor in one of the cells of the data list or table.

2. Click the Remove Duplicates command button on the Ribbon’s Data tab or press Alt+AM.

Excel selects all the cells in the data list while at the same time displaying the Remove Duplicates dialog box similar to the one shown in Figure 1-3.

When this dialog box first opens, Excel automatically selects all the fields in the list (by placing check marks in the check boxes in front of their names in the Columns list box). When all the fields are selected and you click OK in this dialog box, Excel deletes only complete duplicates (in other words, copies) of the records in the list.

If you want the program to remove records where there’s any duplication of entries in particular fields (such as the ID No field), you remove the check marks from all the columns except for those whose duplication are sufficient reason for deleting the entire record (as described in Step 3). Otherwise, you proceed directly to Step 4.

3. (Optional) Remove the check marks from all fields in the Columns list box except for those whose duplicates are reason for deleting the record.

If only one or two fields out of many need to be selected in the Columns list box, click the Unselect All button to remove the check marks from all field check boxes and then individually click the fields that can’t have duplicate entries.

4. Click OK to have Excel close the Remove Duplicates dialog box and remove the duplicate records (rows) from the selected data list.

Figure 1-3: Using the Remove Duplicates dialog box to remove duplicate records from a data list.

Sorting Data

You can use the Sort & Filter Data command button on the Ribbon’s Home tab and the AutoFilter buttons on the field names to quickly sort data. You can sort the records in your data list by sorting its rows, and you can sort the fields in the data list by sorting its columns.

In sorting, you can specify either ascending or descending sort order for your data. When you specify ascending order (which is the default), Excel arranges text in A-to-Z order and values from smallest to largest. When you specify descending order, Excel reverses this order and arranges text in Z-to-A order and values range from largest to smallest. When sorting on a date field, keep in mind that ascending order puts the records in oldest to newest order, while descending order gives you the records in newest to oldest date order.

Keep in mind that, although sorting is most often applied to rearranging and maintaining data list records and fields, you can use the Sort & Filter command button to reorder data in any worksheet table, whether or not the table follows the strict data list structure.

More about ascending and descending sort orders

When you use the ascending sort order on a field in a data list that contains many different kinds of entries, Excel places numbers (from smallest to largest) before text entries (in alphabetical order), followed by any logical values (FALSE and TRUE), error values, and finally, blank cells. When you use the descending sort order, Excel arranges the different entries in reverse: numbers are still first, arranged from largest to smallest; text entries go from Z to A; and the TRUE logical value precedes the FALSE logical value.

Sorting records on a single field

When you need to sort the data list only on one particular field (such as the ID No, Last Name, or Location field), you simply click that field’s AutoFilter button and then select the appropriate sort option from its drop-down list:

· Sort A to Z or Sort Z to A in a text field

· Sort Smallest to Largest or Sort Largest to Smallest in a number field

· Sort Oldest to Newest or Sort Newest to Oldest in a date field

Excel then reorders all the records in the data list in accordance with the new ascending or descending order in the selected field. If you find that you’ve sorted the list in error, simply click the Undo button on the Quick Access toolbar or press Ctrl+Z right away to return the list to its previous order.

Excel 2016 shows when a field has been used in sorting the data list by adding an up or down arrow to its filter button. An arrow pointing up indicates that the ascending sort order was used and one pointing down indicates that the descending sort order was used.

Sorting records on multiple fields

When you need to sort a data list on more than one field, you use the Sort dialog box (shown in Figure 1-4). And you need to sort on more than one field when the first field contains duplicate values and you want to determine how the records with duplicates are arranged. (If you don’t specify another field to sort on, Excel just puts the records in the order in which you entered them.)

Figure 1-4: Set up to sort records alphabetically by location and salary.

The best and most common example of when you need more than one field is when sorting a large database alphabetically in last-name order. Say that you have a database that contains several people with the last name Smith, Jones, or Zastrow (as is the case when you work at Zastrow and Sons). If you specify the Last Name field as the only field to sort on (using the default ascending order), all the duplicate Smiths, Joneses, and Zastrows are placed in the order in which their records were originally entered. To better sort these duplicates, you can specify the First Name field as the second field to sort on (again using the default ascending order), making the second field the tie-breaker, so that Ian Smith’s record precedes that of Sandra Smith, and Vladimir Zastrow’s record comes after that of Mikhail Zastrow.

To sort records in a data list using the Sort dialog box, follow these steps:

1. Position the cell cursor in one of the cells in the data list table.

2. Click the Sort button in the Sort & Filter group on the Data tab or press Alt+ASS.

Excel selects all the records of the database (without including the first row of field names) and opens the Sort dialog box, as shown in Figure 1-4. Note that you can also open the Sort dialog box by selecting the Custom Sort option on the Sort & Filter drop-down button’s menu or by pressing Alt+HSU.

3. Select the name of the field you first want the records sorted by from the Sort By drop-down list.

If you want the records arranged in descending order, remember also to select the descending sort option (Z to A, Smallest to Largest, or Oldest to Newest) from the Order drop-down list to the right.

4. (Optional) If the first field contains duplicates and you want to specify how the records in this field are sorted, click the Add Level button to insert another sort level, select a second field to sort on from the Then By drop-down list, and select either the ascending or descending option from its Order drop-down list to its right.

5. (Optional) If necessary, repeat Step 4, adding as many additional sort levels as required.

6. Click OK or press Enter.

Excel closes the Sort dialog box and sorts the records in the data list using the sorting fields in the order of their levels in this dialog box. If you see that you sorted the database on the wrong fields or in the wrong order, click the Undo button on the Quick Access toolbar or press Ctrl+Z to immediately restore the data list records to their previous order.

By default, when you perform a sort operation, Excel assumes that you’re sorting a data list that has a header row (with the field names) that is not to be reordered with the rest of the records in doing the sort. You can, however, use the Sort feature to sort a cell selection that doesn’t have such a header row. In that case, you need to specify the sorting keys by column letter, and you need to be sure to deselect the My Data Has Headers check box to remove its check mark in the Sort dialog box.

Also, the Sort dialog box contains an Options button that, when clicked, opens a Sort Options dialog box, which contains options for doing a case-sensitive sort on fields that contain text. This dialog box also contains options for changing the orientation of the sort from the normal top-to-bottom order to left-to-right order when you want to sort columns in a list.

Figure 1-5 illustrates sorting the employee data list first in ascending order by location and then in descending order by salary. For this sort, the Location field is designated as the field (column) to sort on in the first level and the Salary field as the other field (column) as the second level. Also, to have the records within each location sorted from highest to lowest salary, I selected Largest to Smallest from the Order drop-down list to the right of the first Then By combo box.

Figure 1-5: Employee data list sorted by location and salary.

After clicking OK in the Sort dialog box, you will note in Figure 1-5 how the records are now organized first in ascending order by the city listed in the Location field (Atlanta, Boston, Chicago, and so on) and within each city in descending order by Salary (38,900, 32,200, 29,200, and so on).

Sometimes, you may need to sort on a whole bunch of fields to get the desired order. For example, suppose that you are working with a personnel data list like the one shown in Figure 1-6, and you want to organize the records in alphabetical order, first by department, then by supervisor, and finally by last name, first name, and middle name. To sort the records in this data list on these five fields, you have to define each of the columns as a separate level in the Sort dialog box as follows:

· First by Department field in A to Z order

· Then by Supervisor field in A to Z order

· Then by Last Name field in A to Z order

· Then by First Name field in A to Z order

· Then by Middle Name field in A to Z order

Figure 1-6: The Sort dialog box with five levels of sorting keys for sorting the Personnel data list.

Figure 1-6 shows you the Sort dialog box after defining these as the columns on five separate levels on which to sort the personnel data list. Figure 1-7 shows you the result. As you can see after performing this sort operation, the records are now arranged in ascending order by department, then by supervisor within department, and finally by the last name, first name, and middle name under each supervisor.

Figure 1-7: The Personnel data list after sorting by department, supervisor, last, first, and finally middle names.

Sorting the columns of a data list

You can use Excel’s column sorting capability to change the order of the fields in a data list without having to resort to cutting and pasting various columns. When you sort the fields in a data list, you add a row at the top of the list that you define as the primary sorting level. The cells in this row contain numbers (from 1 to the number of the last field in the data list) that indicate the new order of the fields.

Figures 1-8 and 1-9 illustrate how you can use column sorting to modify the field order of a data list in the sample Personnel data list. As you see in Figure 1-8, I began this process by inserting a new row (row 1) above the row with the field names for this data list. The cells in this row contain numbers that indicate the new field order. After the fields are sorted using the values in this row, the SSN field remains first (indicated by 1), the Department field becomes second (2), Supervisor field third (3), followed by First Name (4), Middle Name (5), Last Name (6), Title (7), and Salary (8).

Figure 1-8: Setting up the Personnel data list to sort by columns by adding a primary sort order row.

Figure 1-9: Personnel data list after sorting the columns using the values entered in the first row.

You can’t sort data you’ve formally formatted as a data table in this manner until you convert the table back into a normal cell range because the program won’t recognize the row containing the column’s new order numbers as part of the table on which you can perform a sort. In this example, to get around the problem, you take the following steps:

1. Click a cell in the data table and then click the Convert to Range command button on the Design tab of the Table Tools contextual tab.

Excel displays an alert dialog box asking you if you want to convert the table to a range.

2. Click the Yes button in the alert dialog box to do the conversion.

3. Select all the records in the Personnel data list along with the top row containing the numbers on which to sort the columns of the list as the cell selection.

In this case, you select the cell range A1:H20 as the cell selection.

4. Click the Sort command button on the Data tab (or press Alt+ASS).

Excel opens the Sort dialog box. You can also open the Sort dialog box by selecting Custom Sort from the Sort & Filter button’s drop-down list or by pressing Alt+HSU.

5. Click the Options button in the Sort dialog box.

Excel opens the Sort Options dialog box.

6. Select the Sort Left to Right option button and then click OK.

7. Click Row 1 in the Row drop-down list in the Sort dialog box.

The Sort On drop-down list box should read Values, and the Order drop-down list box should read Smallest to Largest, as shown in Figure 1-8.

8. Click OK to sort the data list using the values in the top row of the current cell selection.

Excel sorts the columns of the Personnel data list according to the numerical order of the entries in the top row (which are now in a 1-to-8 order) as shown in Figure 1-9. Now, you can get rid of the top row with these numbers.

9. Select the cell range A1:H1 and then click the Delete button on the Home tab.

Excel deletes the row of numbers and pulls up the Personnel data list so that its row of field names is now in row 1 of the worksheet. Now, all that’s left to do is to reformat the Personnel data list as a table again so that Excel adds AutoFilter buttons to its field names and the program dynamically keeps track of the data list’s cell range as it expands and contracts.

10. Click the Format as Table command button on the Home tab (or press Alt+HT) and then click a table style from the Light, Medium, or Dark section of its gallery.

Excel opens the Format As Table dialog box and places a marquee around all the cells in the data list.

11. Make sure that the My Table Has Headers check box has a check mark in it and that all the cells in the data list are included in the cell range displayed in the Where Is the Data for Your Table text box before you click OK.

Figure 1-9 shows the personnel data list after sorting its fields according to the values in the first row. After sorting the data list, you then delete this row and modify the widths of the columns to suit the new arrangement and reformat the list as a table before you save the worksheet.

When sorting the columns in a data list, you must remember to click the Options button and select the Sort Left to Right option button in the Orientation section of the Sort Options dialog box. Otherwise, Excel sorts your records instead of your columns, and in the process, the row of field names becomes sorted in with the other data records in your list!

Sorting a data list on font and fill colors and cell icons

Although you normally sort the records of a data list or rows of a table on the values (entries) contained in one or more columns of the list or table, Excel 2016 also enables you to sort on the font or fill color or cell icons that you assign to them as well. These colors and icons are assigned by using the Conditional Formatting feature to mark those values in the columns of a data list or table that are within or outside certain parameters with a distinctive font or fill colors or cell icon. (See the section on the Conditional Formatting feature in Book II, Chapter 2 for details.)

To sort a data list on a font color, fill color, or cell icon in a single field of the table, you click its AutoFilter button and then choose the Sort by Color option from the drop-down menu. Excel then displays a continuation menu on which you click the font color, fill color, or cell icon to use in the sort:

· To sort the records so that those with a particular font color in the selected column — assigned with the Conditional Formatting Highlight Cell Rules or Top/Bottom Rules options — appear at the top of the data list, click its color swatch in the Sort by Font Color section on the continuation menu.

· To sort the records so that those with a particular fill color in the selected column — assigned with the Conditional Formatting Highlight Cell Rules, Top/Bottom Rules, Data Bars, or Color Scales options — appear at the top of the data list, click its color swatch in the Sort by Font Color section on the continuation menu.

· To sort the records so that those with a particular cell icon in the selected column — assigned with the Conditional Formatting Icon Sets options — appear at the top of the data list, click the icon in the Sort by Cell Icon section of the continuation menu.

You can also sort the data list on more than one color or cell icon in the Sort dialog box opened by selecting the Custom Sort option from the Sort & Filter button’s drop-down list on the Ribbon’s Home tab or on the Sort by Color continuation menu.

When you want to sort the records in a data list on more than one font or fill color or cell icon, you select the field with the color or icon from the Column drop-down list; select Font Color, Fill Color, or Cell Icon in the Sort On drop-down list; and then click the color swatch or icon to use in the first level of the sort in the Order drop-down list.

If you need to add another sort level, you click the Add Level button and then repeat this procedure of selecting the field in the Column drop-down list, selecting the Font Color, Fill Color, or Cell Icon in the Sort On drop-down list, and selecting the specific color or icon in the Order drop-down list. When you finish defining all the levels for the sort, click OK to have Excel go ahead and sort the list’s records.

You can sort the records in the data list order by all the fill colors or cell icons assigned by applying the Conditional Formatting Color Scales and Cell Icons options. For each of three or five sorting levels you define in the Sort dialog box, the name of the field in the Column drop-down list button remains the same in all levels along with the Fill Color or Cell Icon option in the Sort On drop-down list button. Only the actual color or icon selected in the Order drop-down list button changes, reflecting the order in which you want to see the records appear in the sorted data list.

Subtotaling Data

You can use Excel’s Subtotals feature to subtotal data in a sorted data list. You sort the list on the field for which you want subtotals shown before you designate the field that contains the values you want subtotaled — these are almost always not the same fields in the data list.

For example, to subtotal the salaries within each department in my sample Employee data list, you first sort the list in A-to-Z order on the Dept column. Then, you designate this Dept field as the one for which you want the subtotals calculated (so that a subtotal appears at each change in department) and the Salary field as to be subtotaled so that Excel uses the SUM function on its data entries.

Keep in mind when you use the Subtotals feature, you aren’t restricted to having the values in the designated field added together with the SUM function. You can instead have Excel return the number of entries with the COUNT function, the average of the entries with the AVERAGE function, the highest entry with the MAXIMUM function, the lowest entry with the MINIMUM function, or even the product of the entries with the PRODUCT function.

Excel does not allow you to subtotal a data list formatted as a table. Before you can use the Subtotal command button, you must first convert your table into a normal range of cells. To do this, click a cell in the table and then click the Design tab on the Table Tools contextual tab on the Ribbon. Finally, click the Convert to Range command button in the Tools group followed by the Yes button in the alert dialog box asking you to confirm this action. Excel then removes the filter buttons from the columns at the top of the data list while still retaining the original table formatting.

Figures 1-10 and 1-11 illustrate how easy it is to use the Subtotals feature to obtain totals in a data list. In Figure 1-10, I sorted the sample Employee data list first by the Dept field in ascending order and then by the Salary field in descending order (Largest to Smallest) and converted the data list to a range. I then clicked the Subtotal command button in the Outline group on the Ribbon’s Data tab to open the Subtotal dialog box shown in Figure 1-10.

Figure 1-10: Using the Subtotal dialog box to subtotal the salaries for each department.

Figure 1-11: Bottom of the data list showing the subtotals and grand total for department salaries.

Here, I selected the Dept field as the field for which the subtotals are to be calculated in the At Each Change In drop-down list box, Sum as the function to use in the Use Function drop-down list box, and the Salary check box as the field whose values are to be summed in the Add Subtotal To list box.

Figure 1-11 shows the results I obtained after clicking OK in the Subtotal dialog box. Here, you see the bottom of the data list showing the salary subtotals for the Administration, Engineering, Human Resources, and Information Services departments along with the grand total of the salaries for all the departments. The grand total is displayed at the bottom of the data list because I left the Summary below Data check box selected in the Subtotal dialog box — if I hadn’t wanted a grand total, I would have removed the check mark from this check box.

As you can see in Figure 1-11, when you use the Subtotals command, Excel outlines the data at the same time that it adds the rows with the departmental salary totals and the grand total. This means that you can collapse the data list down to just its departmental subtotal rows or even just the grand total row simply by collapsing the outline down to the second or first level. (Remember that you can toggle between showing and hiding the outline symbols at the left edge of the data list by pressing Ctrl+8.)

In a large data list, you may want Excel to insert page breaks (often referred to as breaks) every time data changes in the field on which the list is being subtotaled (that is, the field designated in the At Each Change In drop-down list box). To do this, you simply select the Page Break between Groups check box in the Subtotal dialog box to put a check mark in it before you click OK to subtotal the list.