For our next set of PivotTables, we are going to move away from academic data and look at data for a sales representative who works for a fictional pharmaceutical company. This sales rep spends his time talking with doctors and distributing samples of the product he represents. Occasionally the sales rep will host a lunch for a doctor and his/her staff. This is a way of guaranteeing that the sales rep will get some face-to-face time to discuss the products with the doctor during the few minutes of down-time the doctor has each day.
We are curious whether providing lunch to a doctor and her staff has any bearing on how many prescriptions the doctor writes for a specific product. The lunch field simply indicates "yes," lunch was provided or "no," it wasn't. When we include this field in a PivotTable, we will re-label the rows so that they make more sense.
First we need to open the Excel workbook we will be using.
Step1. To open an existing file, on the Ribbon,
Click the File tab
NOTE for MacOS Users: To open an existing file, Click the File menu, Click Open..., Click.
Step2. To open the file,
navigate to the PivotTable_Analysis folder,
Step3. Confirm that any cell in the table is active.
Step4. Switch to the Insert tab, if necessary.
Step5. To begin creating the PivotTable, in the Tables group,
Step6. To create the PivotTable in a new worksheet,
Step7. To name the worksheet tab,
Double-Click the current worksheet tab
Step8. To rename the tab, type:
Using Search to Add Fields to the PivotTable
When working with large amounts of data and numerous fields, it may be helpful to search for the field we need instead of scrolling through the Field List. The search feature can also be helpful when working on a smaller screen that gives us a limited view of the Field List.
We want to begin by creating two large groups, one for visits which included lunch and a second for visits without lunch. We will use Excel's search feature to find and add the Lunches field as the first row field.
Step1. To search for the Lunches field, in the Field List,
Click, type: lunches
Step2. To add the Lunches field to the PivotTable, in the Field List,
Press & Drag Lunches to the Rows area
Step3. To clear the search field, in the Field List,
Step4. To search for the Doctor Name field, in the Field List,
Click, type: doctor name
Step5. To add the Doctor Name field, from the Field List,
Press & Drag Doctor Name to the Rows area below Lunches
NOTE: Because our sales representative visits each doctor multiple times, most doctors appear in both groups.
Step6. To clear the search field, in the Field List,
Step7. To add the Rx field, from the Field List,
Press & Drag Rx to the Values area