Pivot Tables

Pivot tables allow you to quickly reorganize, summarize, analyze, and present data in Excel. A pivot table can be created with just a few clicks. Below is an Excel spreadsheet containing sales data for a car dealership. It contains the last name of the salesperson, the amount the car sold for, the percent profit on the car sale, the amount of profit, the month of sale, and the city where the car was sold.

Car Sales Data

  • Open the Excel spreadsheet above which contains car sales data
  • Make the active cell one of the cells in the data
  • Click the 'Insert' tab and select 'Pivot Table' in the upper left corner
  • The 'Create Pivot Table' dialogue box opens. The data should look like the image below
  • Click 'OK'
  • A blank pivot table should appear
  • Click and hold 'Last' and drag it to the 'Row Labels' field
  • Click and hold 'Profit' and drag it to the 'Values' field
  • You just created a pivot table showing the profit earned by each salesperson
  • Create a second pivot table that shows the sales by month for each salesperson
  • Return to the data sheet
  • Create another pivot table by clicking the 'Insert' tab and clicking 'Pivot Table' and click OK
  • You should be looking at a blank pivot table
  • Drag the 'Last' into the 'Column Labels' field
  • Drag 'Month' into the 'Row Labels' field
  • Drag 'Profit' into the 'Values' field
  • You are now looking at the profit earned by month for each salesperson
  • Create a third pivot table that shows the sales by month for only Bakersfield
  • Click on the 'Data' sheet again to create a third pivot table
  • Drag 'Sales Value' into the 'Values' field
  • Drag 'City' into the 'Row Labels' field
  • Drag 'Month into the 'Row Labels' field and below the 'City' field
  • You are now looking at the sales by each city by month
  • Refine the pivot table by showing only Bakersfield
  • Click the drop down menu on the row labels (see below)
  • Then uncheck all boxes except 'Bakersfield (see below)
  • Now you should have just the sum of sales for Bakersfield by month
  • Create a fourth pivot table showing the count of car sales that did not earn any money by salesperson
  • Click on the 'Data' sheet again to create a fourth pivot table
  • Drag 'Profit Percent' into the 'Report Filter' field
  • Drag 'Last' into the 'Row Labels' field
  • Drag 'Profit' into the 'Values' field
  • Click the 'Sum of Profit' field to get to the 'Value Field Settings...' menu
  • Click 'Value Field Settings...' option
  • On the 'Value Field Settings' dialogue box select 'Count'
  • Click 'OK'
  • Change the 'Profit Percent (All)' to '0.00' by clicking on the 'Profit Percent (All) drop down list and clicking '0.00' and pressing 'OK'
  • Your table now shows the count of sales that earned no profit by salesperson
  • Turn in your Excel spreadsheet as an attachment for your homework for the week

In the subject line for your email, include the assignment number, your name and the last 4 digits of your RunnerID in this form:
Homework 13, 1234, Samantha Spade
(Replace Samantha Spade with your name and 1234 with the last four digits of your student ID). Please follow these instructions carefully.