Hands-On Exercises

Download as a Word document: Hands-On Exercises Ch08 (235.0K)

Sunny Side Marina

In this exercise, you manipulate data into information by sorting and filtering an existing list.

1. Open the file named EX08HE01.
2. Save the workbook as "Inventory" to your personal storage location. (Figure 8-1)

<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg:: ::/sites/dl/free/0072470941/26807/Ch08_Image1ho.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (50.0K)</a>

Figure 8-1

3. Sort the list by part number:
   SELECT: Cell A2, or any cell in the first column
   CLICK: Sort Ascending button
4. To sort the list by multiple columns, use the sort dialog box:
   CHOOSE: Data, Sort
5. To better organize the data, sort first by DEPT, then by Stk#
   SELECT: DEPT in the first Sort by drop down box
   SELECT: STK# in the second Sort By drop down box
   Ensure that the Header Row option button is selected, before clicking OK command button.
6. Move the cell pointer to cell A1
   PRESS: [Ctrl] + [Home]
7. Use the AutoFilter command to filter out items in Department 4, that cost more than $8.00:
   CHOOSE: Data, Filter, AutoFilter
   CLICK the down arrow next to Dept
   SELECT: 4
   CLICK: the down arrow next to Cost
   SELECT: Custom
8. The Custom AutoFilter allows you to specify a condition:
   SELECT: is greater than or equal to in the upper left drop down list box.
   PRESS: [Tab]
   TYPE: 8
   CHOOSE: OK
   Only three items in department four cost more than $8.00. (Figure 8-2)

<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg:: ::/sites/dl/free/0072470941/26807/Ch08_Image2ho.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (89.0K)</a>

Figure 8-2

9. To remove the filter:
   CHOOSE: Data, Filter, Show All
10. To turn off the AutoFilter:
    CHOOSE: Data, Filter, AutoFilter
11. Close and save the file, then exit Excel.

Data File: EX08HE01 (15.0K)

Turtle River Casino

In this exercise you will practice modifying lists by removing fields, adding records, and using the AutoComplete Features.

1. Open the file EX08HE02.
2. Save the workbook as "Contacts" to your personal storage location.
3. To begin, enter your own information in row 9. Make up a company name. When you get to the date column:
   PRESS: [Ctrl] + ; to enter the current date
4. Enter a new record using the Auto Features
   PRESS: [Alt] + [Down Arrow] to drop a list of previous entries
   SELECT: Prudential Properties
   PRESS: Enter
5. To complete the entry:
   PRESS: [Tab]
   TYPE: Jennifer
   PRESS: [Tab]
   TYPE: Richards
   PRESS: [Tab]
   TYPE: 555-6983
   PRESS: [Tab]
   TYPE: 455 Front St.
   PRESS: [Tab] 2 times
6. To enter a city that has been entered previously:
   TYPE: T
   Excel will AutoComplete the entry for you
   PRESS: [Tab]
   TYPE: M
   PRESS: [Tab]
   TYPE: 49683
   PRESS: [Tab}
   PRESS: [Ctrl]+; to enter the current date
7. On your own, Sort the list first by City, then by date, and finally by street using the Sort Dialog box.
8. Column F isn’t being used, and from the looks of the data, it won’t be in the future. On your own, delete the column with the label Street2 (Column F). Your worksheet should look similar to Figure 8-3.

<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg:: ::/sites/dl/free/0072470941/26807/Ch08_Image3ho.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (118.0K)</a>

Figure 8-3

Data File: EX08HE02 (14.0K)