Page 232 - Excel Workbook for Dummies

23_798452 ch16.qxp 3/13/06 7:40 PM Page 215
Chapter 16: Building and Maintaining Data Lists 215
Start by filtering the data list to just the records where the employee is part of
the profit sharing plan.
2. Click the drop-down list box in the Profit Sharing field name and then select Yes
on the drop-down list.
Excel hides all the records where the Profit Sharing is No, leaving displayed only
those where the Profit Sharing entry is Yes.
3. Click (All) near the top of the Profit Sharing field’s drop-down list to restore the
display of all the records in the data list.
When you filter the records in a data list, Excel simply temporarily hides the
records.
4. Click (Top 10...) on the Years of Service drop-down list to open the Top 10
AutoFilter dialog box and select OK.
The Top 10 AutoFilter dialog box enables you to display only the records with
the top or bottom 10 (more or less) values (by selecting Items in the rightmost
drop-down list box) or percentage (by selecting Percent in this drop-down list
box) in the selected field.
5. Click the Sort Descending item near the top of the Years of Service drop-down
list to sort the filtered records so that they are displayed in order from the
longest to shortest top ten years of service.
6. Redisplay all the records in the data list and then click the (Custom) item on the
Salary drop-down list to open the Custom AutoFilter dialog box.
Use the Custom AutoFilter dialog box to filter out all the records except for those
where the annual salary is between $40,000 and $75,000.
7. Select Is Greater Than or Equal To in the first drop-down list box and then click
the second combo box to its immediate right and type 40000. Select Is Less Than
or Equal To in the second drop-down list box below the AND and OR option but-
tons (of which the AND should be selected) and then type 75000 in the combo
box to its immediate right. Click OK.
8. Sort the filtered records so that they are displayed in descending order by
salary.
Because the AutoFilter only temporarily hides the display of the records in a
data list, you need to copy the filtered records to a new part of the worksheet or
to a new worksheet when you want to retain the subset for future reference and
use.
9. Copy the filtered and sorted records currently displayed in the cell range A1:J33
(including the field names in row 1) on the Employee Data List worksheet to the
same range on the Sheet2 worksheet. Rename the Sheet2 worksheet to Salary
Subset and then click the Employee Data List worksheet to reselect it.
10. Redisplay all the records in the data list on the Employee Data List worksheet
and then filter the list so that only the records where the department is either
Engineering or Information Services are displayed and the records are sorted in
ascending order by department name.
Be sure to select the OR option button in the Custom AutoFilter dialog box. Note
that you can select the department names, Engineering and Information
Services, from the two Combo boxes after selecting the Equals or Contains oper-
ator in the associated drop-down list boxes in this dialog box.
11. Copy the filtered and sorted records (including the field names to the equivalent
cell range in Sheet3) and then rename Sheet3 to Department Subset.