PAUSE & PRACTICE: EXCEL 4-2

For this project, you filter data for Perfect Vacation Rentals. You use a Number Filter to display records within a range of values, build an advanced filter, and create subtotals in the workbook. You import data using Power Query to remove rows.

Files Needed: your initials PP E4-1.xlsx and ImportText-04.txt (PP E4-2) (Student data files are available in the Library of your SIMnet account.)

Completed Project File Name: your initials PP E4-2.xlsx

1. Open the your initials PP E4-1 workbook completed in Pause & Practice 4-1 and save it as your initials PP E4-2.
2. Click the Sort&Filter sheet tab and select cell A5.
3. Use a Number AutoFilter.
   1. Click the Filter button [Data tab, Sort & Filter group] to display AutoFilter arrows.
   2. Click the AutoFilter arrow for “@ $225 Average.”
   3. Point to Number Filters and select Between. This is an AND filter.
   4. Type 60000 in the is greater than or equal to box.
   5. Type 70000 in the is less than or equal to box (Figure 4-56).

1. 1. Click OK. Eight records display.
2. Click the AdvFilter sheet tab.
3. Set a criteria range.
   1. Type mi in cell K7.
   2. Type wi in cell K8. These two criteria are OR requirements.
   3. Type 3 in cell L7.
   4. Type 3 in cell L8. The criteria will locate Michigan and Wisconsin properties with three bedrooms.
4. Run an Advanced Filter.
   1. Click cell A5.
   2. Click the Advanced button [Data tab, Sort & Filter group].
   3. Select the Copy to another location radio button.
   4. Verify that cells $A$4:$F$28 are identified in the List range box.
   5. Click the Criteria range box and select cells K6:L8. The sheet name is included in the entry box.
   6. Click the Copy to box and select cells I11:N11 (Figure 4-57).
   7. Click OK. Two records are extracted and displayed.
   8. AutoFit column I.

1. 1. Show subtotals in a list.
      1. Click the Subtotals sheet tab.
      2. Click a cell in the Location column.
      3. Click the Sort A to Z button [Data tab, Sort & Filter group]. The records are sorted by city name.
      4. Click a cell in the State column.
      5. Click the Sort A to Z button [Data tab, Sort & Filter group]. The records are sorted by state with city names also in ascending order.
      6. Select cell A5 and click the Subtotal button [Data tab, Outline group].
      7. Click the At each change in arrow and choose State.
      8. Click the Use function arrow and verify or choose Sum.
      9. Select Days Rented Last Year and @ $225 Average in the Add subtotal to list box.
      10. Click OK.
   2. Format outline results.
      1. Select cell C7 and move the label to cell A7.
      2. Move the labels in cells C11, C15, C20, C24, C28, C32, and C36:C37 to the corresponding cells in column A.
      3. Select the moved cells and right-align them.
      4. Select cells A36:G37 and apply All Borders.
      5. Click the Level 2 outline button.
      6. Hide columns B:E (Figure 4-58).
   3. Import data from a text file in Power Query.
      1. Select cell A5 on the Text Import tab.
      2. Click the From Text/CSV button [Data tab, Get & Transform Data group].
      3. Navigate to and select ImportText-04.txt from your student data files in the Import Data dialog box.
      4. Click Import. The preview window displays the data.
      5. Click Transform Data to start Power Query. The data displays with a default header row.
   4. Manage data in Power Query.
      1. Click the Remove Rows button [Home tab, Reduce Rows group]. You can specify how many rows to remove from the data before it is imported (Figure 4-59).

1. 1. 1. Choose Remove Bottom Rows. Notice that the last three rows of data are Missouri records.
      2. Type 3 in the Number of rows entry box and click OK. The Missouri records are removed.
   2. Load data from Power Query to the worksheet.
      1. Click the arrow with the Close & Load button [Home tab, Close group].
      2. Select Close & Load To to display the Import Data dialog box (Figure 4-60).

1. 1. Verify that Table is selected.
   2. Select Existing worksheet.
   3. Confirm that $A$5 displays in the Import Data dialog box.
   4. Click OK.
   5. Close the Queries & Connections pane.
   6. Select cells A4:F4 and drag and drop (or cut and paste) the labels to cells A5:F5 and replace the default column labels.
   7. Set the width of column F to 12.
   8. Set the height of row 5 to 34.
   9. Delete row 4.
   10. Select cells A4:F25 and apply All Borders.
2. Select cell A1. Save and close the workbook. (Figure 4-61).