CIS Excel P

Excel Project

Independent Challenge 3

 You are the North American sales manager for a medical equipment company with sales offices in the United States and  Canada. You use Excel to keep track of the staff in the U.S. and Canadian offices. Management asks you to provide a  summary table showing information on your sales staff, including their locations, status, and titles. You will create a  PivotTable and PivotChart summarizing this information.

 a. Start Excel, open EX 12-5.xlsx from the location where you store your Data Files, then save it as EX 12-Sales  Employees.  

b. Create a PivotTable on a new worksheet that shows the number of employees in each city, with the names of the  cities listed across the columns, the titles listed down the rows, and the status indented below the titles.  (Hint: Remember that the default summary function for cells containing text is Count.) Use FIGURE 12-29 as a guide.  Rename the new sheet PivotTable.  

c. Change the structure of the PivotTable to display the data as shown  in FIGURE 12-30.  

d. Add a report filter using the Region field. Display only the U.S.  employees.

 e. Create a clustered column PivotChart from the PivotTable and  move the chart to its own sheet named PivotChart. Rearrange the  fields to create the PivotChart shown in FIGURE 12-31.  f. Add the title U.S. Sales Staff above the chart.

 g. Add the Pivot Style Light 12 style to the PivotTable.

 h. Insert a new row in the Employees worksheet above row 7. In the  new row, add information reflecting the recent hiring of Cathy  Olsen, a senior sales manager at the Boston office. Update the  PivotTable to display the new employee information.

i. Add the label Total Miami Staff in cell G1 of the Employees sheet. Widen column G to fit the label.

 j. Enter a function in cell H1 that retrieves the total number of  employees located in Miami from the PivotTable. Change the  page orientation of the Employees sheet to landscape.

 k. Use a slicer to filter the PivotTable to display only the data for  the cities of Boston, Miami, and Los Angeles.  

l. Add another slicer for the Status field to display only the senior  staff members.  

m.Verify that the number of Miami staff in cell H1 of the  Employees sheet is now 7.

n. Remove the slicers, but do not remove the filters.  

o. Add your name to the center section of all three worksheet footers, save the workbook, then preview the  PivotTable, the first page of the Employees worksheet, and the PivotChart.

 p. Close the workbook and exit Excel. Submit the workbook to your instructor.