EXERCISE 4: CREATING A "PIVOT TABLE" Pivot Tables allow you to create reports on a new spreadsheet from data stored on a separate, often large spreadsheet. Employers tell us that knowing how to create and manipulate Pivot Tables is an essential skill for their businesses. For this exercise you do not need to type in the data. It is provided to you. Open the "Excel Student Spreadsheets BUS 1011." Look to the bottom of the spreadsheet and click the tab titled "Pivot Table Data." There you will find a spreadsheet that has two additional Columns: "2 Products," which lists the two key products sold by the stores-Apple's iPhone and the Samsung Galaxy, and "4 Quarters," which lists sales (revenues) for each of the four quarters in a year. Manipulating this data will allow you to answer the following questions: 1. Which store-the one in Queens, Brooklyn or the Bronx-was the most profitable? Which one was the least profitable? 2. Which quarter has the most sales (revenue) overall? 3. Which product-the Apple iPhone or the Samsung Galaxy-was more profitable overall? Az To answer these questions: Create PivotTable Choose the data that you want to analyze Select a table or range Table/Range: Pivot Table Data SA13:127 Use an external data source Choose Connection... . 2 Click on any cell (say A3) Click on "Insert," then click "PivotTable" on the extreme left. The following "Create PivotTable" box should appear. Simply click "OK," choosing the defaults. A new worksheet will be created. Rename the tah "DT Analucic " Manipulating this data will allow you to answer the following questions: 1. Which store-the one in Queens, Brooklyn or the Bronx-was the most profitable? Which one was the least profitable? 2. Which quarter has the most sales (revenue) overall? 3. Which product-the Apple iPhone or the Samsung Galaxy-was more profitable overall? To answer these questions: Create PivotTable Choose the data that you want to analyze Select a table or range Table/Range: Pivot Table Data'!$A$3:$F$27 Use an external data source Choose Connection... Connection name: Choose where you want the PivotTable report to be placed New Worksheet Existing Worksheet Location: ? x OK Cancel Click on any cell (say A3) Click on "Insert," then click "PivotTable" on the extreme left. The following "Create PivotTable" box should appear. Simply click "OK," choosing the defaults. A new worksheet will be created. Rename the tab "PT Analysis." . On the right-hand side of the spreadsheet, a box titled "PivotTable Field List/Builder" appears. For Macs: Click "Summarize with Pivot Table of "Data." "Create Pivot Table" appears. Pivot Table Field List Choose fields to add to report: Profit= Revenue- Expenses 3 Stores 2 Products 4 Quarters Drag fields between areas below: Y Report Filter Row Labels 3 Stores Column Labels Values Sum of Profit- Defer Layout Update Update Let's start by answering the question: Which store-the one in Queens, Brooklyn or the Bronx-was the most profitable? . Click on/check the box for "Profits." . Then, click on/check the box for "3 Stores." Note that "Sum of Profit =" should appear under Values. "3 Stores" should appear under Row Labels. A Pivot Table should appear in the upper left-hand corner. Drag-and-drop fields/variables to manipulate the Pivot Table and arrive at the rest of the answers. Now you can answer the questions: . Which store is the most profitable overall? Which store is the least profitable? . . Which quarter has the highest sales revenue overall? Which product was more profitable overall? As an added step, try clicking on the "2 Products" box in the PivotTable Field List. This is, you will now have a total of four boxes selected- Profit, 3 Stores, 4 Quarters and 2 Products. Note: you DON'T need to create separate Pivot Tables for each question, simply manipulate the one you created and answer the questions