Independent Project 10-4

Central Sierra Insurance gathers data by city and state. You assemble the data to create PivotTables for a dashboard.

[Student Learning Outcomes 10.1, 10.2, 10.4, 10.5, 10.6, 10.7, 10.8]

Files Needed: CentralSierra-10.xlsx and Database-10.accdb (Student data files are available in the Library of your SIMnet account.)

Completed Project File Name: [your initials] Excel 10-4.xlsx

Skills Covered in This Project

• Get data from an Access table in the workbook.
• Get data from Excel in Power Pivot.
• Create and view a relationship in Power Pivot.
• Add a measure in a Power Pivot table.
• Add calculated fields in Power Pivot.
• Create a PivotTable from the data model.
• Work with PivotTable tools.
• Design a dashboard.

1. Create a new workbook and save it as [your initials] Excel 10-4.
2. Get data from an Access database.
   1. Click the Get Data button [Data tab, Get & Transform Data group] and find and select Database-10 from your student data files.
   2. Choose tblCentralSierra and select Transform Data.
   3. Change the heading “Working” to Employed.
   4. Load the data to your workbook. Name the sheet tblCentralSierra if necessary.
   5. Select cell G1 type Non-Student Total and press Enter.
   6. Build a formula in cell G2 to add the values in D2:F2.
3. Get data in Power Pivot.
   1. Click the Manage Data Model button [Data tab, Data Tools group] to launch Power Pivot.
   2. Click the From Other Sources button [Home tab, Get External Data group] and choose the option to get an Excel workbook file.
   3. Get the data in CentralSierra-10 from your student data files and select the Use the first row as column header box in the first Table Import Wizard.
   4. Select the F5 column header in Power Pivot and delete the column [Design tab, Columns group].
   5. Select the ID column and drag the column to display as the first column.
   6. Change the data type for the ID and Students columns to Whole Number.
4. Add a table to the data model.
   1. Leave the Power Pivot window open and return to your workbook.
   2. Select cell A2 in the table.
   3. Click the Add to Data Model button [Power Pivot tab, Tables group]. The Power Pivot window includes both tables.
5. Create a relationship in the data model.
   1. Select the ID column on the tblCentralSierra tab and confirm that it is Whole Number data type.
   2. Click the Create Relationship button [Design tab, Relationships group].
   3. Select the ID field for both tables and create the relationship.
6. Add a measure in Power Pivot. (If you make an error entering a measure, right-click the measure in the Calculations area and delete it.)
   1. Select the Non-Student Total column on the tblCentralSierra tab.
   2. Click the AutoSum drop-down arrow [Home tab, Calculations group] and choose Sum. The measure displays at the bottom of the column in the Calculations area.
   3. Insert a Sum measure for the Students column on the ClientCount tab.
7. Insert a calculated column.
   1. Confirm that the ClientCount tab is active.
   2. Click the first cell in the Add Column field and type = to start the formula.
   3. Select the measure at the bottom of the Students column. You can refer to data on different tabs in Power Pivot only if a measure is established.
   4. Type + for addition.
   5. Click the tblCentralSierra tab and click the measure for the Non-Student Total column. Adding the measures sums the values like a 3D reference in Excel.
   6. Press Enter (Figure 10-91).
   7. Double-click Calculated Column 1, type Total Clients, and press Enter.
8. Create a PivotTable based on the data model.
   1. Select the tblCentralSierra tab.
   2. Click the PivotTable button [Home tab] and place the new PivotTable on a new worksheet.
   3. Name the worksheet Dashboard.
9. Design the first PivotTable.
   1. Expand the tblCentralSierra list in the PivotTable Fields pane.
   2. Show the State, City, and Employed fields in that order.
   3. Apply Light Gray, Pivot Style Light 15 with Banded Rows and All Borders.
   4. Name the PivotTable pvtEmployed.
   5. Select the entire table and move it to cell C4. (If your PivotTable displays in three columns, choose compact form for the report layout.)
   6. Edit the label in cell D4 to Employed and AutoFit both columns (Figure 10-92).
10. Copy, position, and edit PivotTables.
    1. Select the table and copy it to cell F4.
    2. Remove the Employed field and show the Unemployed field.
    3. Name the PivotTable pvtUnemployed.
    4. Edit the label in cell G4 to Unemployed and AutoFit both columns.
    5. Copy either table to cell I4 and name the new copy pvtRetired.
    6. Show the appropriate fields, edit the label in cell J4, and AutoFit columns as needed.
11. Insert a slicer.
    1. Select a cell in pvtEmployed.
    2. Insert a slicer that uses the State field from tblCentralSierra.
    3. Apply White, Slicer Style Light 3.
    4. Edit the Report Connections so that the slicer filters all the PivotTables on the sheet.
12. Complete formatting and design for the dashboard.
    1. Set columns A:B to a width of 15.
    2. Position the slicer window to start in row 4 in the middle of columns A:B.
    3. Select cell A1 and type Central Sierra Insurance.
    4. Select cell A2 and type Client Count by State.
    5. Format both cells with 20 pt italic Calibri.
    6. Insert a blank row at row 1 and a blank column at column A.
    7. Select columns F and I and set width to 5.
    8. Select Oregon in the slicer (Figure 10-93).
13. Insert a PivotTable on the dashboard.
    1. Select and copy one of the PivotTables to cell B21.
    2. Expand the tblCentralSierra list in the PivotTable Fields pane.
    3. Show the State and Non-Student Total fields; remove all other fields.
    4. Name the PivotTable pvtStates.
    5. Select the slicer and remove the report connection for this PivotTable (Figure 10-94).
    6. Clear the filter from the Row Labels field in pvtStates.
    7. Edit the label in cell C21 to Non-Students and AutoFit column C.
    8. Insert a column at column D and set its width at 5.
    9. Select California in the slicer. The state PivotTable below the slicer is not connected to the slicer.
14. Hide the gridlines and clean up borders if necessary.
15. Save and close the workbook (Figure 10-95).