PAUSE & PRACTICE: EXCEL 10-3

For this project, you use the data model for PivotTables and a PivotChart to build a dashboard for Perfect Vacation Rentals.

File Needed: PerfectRentals-10.xlsx (Student data files are available in the Library of your SIMnet account.)

Completed Project File Name: [your initials] PP E10-3.xlsx

1. Open the PerfectRentals-10 workbook from your student data files and save it as [your initials] PP E10-3.
2. Create a PivotTable from two tables.
   1. Select cell A2 on the Properties sheet.
   2. Click the Summarize with PivotTable button [Table Design tab, Tools group]. The table is named tblProperties.
   3. Select the New Worksheet radio button.
   4. Select the Add this data to the Data Model box and click OK.
   5. Display the PivotTable Fields pane if necessary [PivotTable Analyze tab, Show group].
   6. Select the State, Rental Name, and Rate boxes In the PivotTable Fields pane.
   7. Confirm that the State field precedes the Rental Name field in the Rows area. Drag either field name to reposition them if necessary.
   8. Click All at the top of the PivotTable Fields pane.
   9. Select tblActivity to expand the field list and select the Number of Days box.
   10. Click Auto-Detect in the warning box to create a relationship between the tables.
   11. Click Close in the Auto-Detect Relationships dialog box (Figure 10-62).
   12. Rename the sheet as Dashboard.
3. Move and copy a PivotTable.
   1. Click a cell in the PivotTable.
   2. Click the Report Layout button [Design tab, Layout group] and choose Show in Compact Form. You will not see a change if your PivotTable was already in a compact layout.
   3. Click the Move PivotTable button [PivotTable Analyze tab, Actions group].
   4. Select cell E4 on the Dashboard sheet and click OK.
   5. Click the Select button [PivotTable Analyze tab, Actions group] and choose Entire PivotTable.
   6. Press Ctrl+C to copy the table.
   7. Select cell B4 and press Ctrl+V.
   8. Press Esc to remove the border.
4. Arrange and format PivotTable data.
   1. Select cell B4.
   2. Drag the Sum of Number of Days field name out of the PivotTable Fields pane.
   3. Edit the label in cell C4 to Daily Rate.
   4. Click the More button [Design tab, PivotTable Styles group] and select Light Green, Pivot Style Medium 11.
   5. Click the Grand Totals button [Design tab, Layout group] and select Off for Rows and Columns.
   6. Click the PivotTable Name box [PivotTable Analyze tab, PivotTable group], type RateTable, and press Enter.
   7. Select cell E4 and drag the Sum of Rate field name out of the PivotTable Fields pane.
   8. Edit the label in cell F4 to # of Days.
   9. Find and apply White, Pivot Style Medium 8.
   10. AutoFit column F.
   11. Click the Subtotals button [Design tab, Layout group] and choose Show all Subtotals at Bottom of Group.
   12. Name the PivotTable as DaysTable.
5. Insert a slicer for a PivotTable.
   1. Click a cell in the RateTable and click the Insert Slicer button [PivotTable Analyze tab, Filter group].
   2. Select the State box and click OK.
   3. Position the slicer window to place its top-left selection handle in cell A4.
   4. Set the width to 50 for column A.
   5. Position the slicer window to be in the horizontal center of the column.
   6. Click the More button [Slicer tab, Slicer Styles group] and apply Light Green, Slicer Style Light 3 (Figure 10-63).
6. Define report connections for a slicer.
   1. Confirm that the slicer window is selected.
   2. Click the Report Connections button [Slicer tab, Slicer group]. The slicer is connected to the RateTable.
   3. Select the DaysTable box and click OK.
   4. Select cell A19 and then click the MI label in the slicer window. Both tables are filtered.
   5. Click the Clear Filter button in the slicer window.
7. Add a PivotChart to the dashboard.
   1. Select cell E4 in the DaysTable.
   2. Click the PivotChart button [Insert tab, Tools group].
   3. Confirm that Clustered Column is selected and click OK. The chart object displays in a default location.
   4. Display the PivotChart Fields pane if necessary [PivotChart Analyze tab, Show/Hide group].
   5. Drag the State field out of the PivotChart Fields pane (Figure 10-64).
8. Format a PivotChart.
   1. Click the Change Chart Type button [Design tab, Type group].
   2. Choose the Pie category, the Doughnut chart icon, and click OK.
   3. Click the Field Buttons button [PivotChart Analyze tab, Show/Hide group] to hide the buttons on the chart.
   4. Size and position the chart object to span cells A19:A33.
   5. Select the Total title box, triple-click to select the text, and type Days by Property as the chart title.
   6. Apply a ½ pt. Black, Text 1 outline to the chart object.
9. Define filter connections for a PivotChart.
   1. Confirm that the chart object is selected.
   2. Click the Filter Connections button [PivotChart Analyze tab, Filter group].
   3. Confirm or select the State box and click OK.
   4. Select cell A1 and then click the KY label in the slicer window.
   5. Click the Clear Filter button in the slicer window.
10. Complete dashboard formatting.
    1. Select the entire RateTable.
    2. Click the Field Headers button [PivotTable Analyze tab, Show group] to hide Row Labels in cell B4.
    3. Apply All Borders from the Home tab.
    4. Select the DaysTable, hide the field headers, and apply All Borders.
    5. Select rows 1:2 and set the height to 40.
    6. Select cell A1 and type Perfect Vacation Rentals.
    7. Format the font as Calibri 36 pt. italic.
    8. Select cell A2 and type Property Rates and Days Rented.
    9. Format the font as Calibri 28 pt. italic.
    10. Turn off the gridlines display.
    11. Insert a blank row at row 1 and a blank column at column A.
    12. Turn off the Formula Bar and the Headings displays [View tab].
    13. Show data for Kentucky and Tennessee.
11. Save and close the workbook (Figure 10-65).