We want to create a PivotTable that will show us the total number of prescriptions each doctor wrote over the entire year. We also want to see what percentage of the grand total each doctor wrote. When our PivotTable is complete, we will apply conditional formatting to see the doctors who wrote the most prescriptions.
We will begin by creating our PivotTable.
Creating a PivotTable Using the % of Column Feature
We will use the SalesCalls worksheet, specifically the Doctor Name and Rx fields, to create our PivotTable. This PivotTable will show us not only how many prescriptions each doctor wrote, but the percentage of the total.
Step1. Use the SalesCalls worksheet to create a PivotTable in a new worksheet named: RxByDoctor.
Step2. Add the Doctor Name field to the Rows area and the Rx field to the Values area.
Step3. To change the name of the Sum of Rx field, in the PivotTable,
Click in cell B3, type: Total Rx Enter
Step4. To add the Rx field to the Values area, from the Field List,
Press & Drag Rx to the Values area below Total Rx
Step5. To modify the Rx field, in the Values area,
Click the Sum of Rx field, Click Value Field Settings...
NOTE for MacOS Users: To modify the Rx field, in the Values area,
Step6. To switch to the "Show Values As" tab, in the Value Field Settings dialog box,
Click the "Show Values As" tab
NOTE for MacOS Users: To switch to the "Show data as" tab, Click the "Show data as" tab.
Step7. To change the way the value appears,
Click, Click % of Column Total
NOTE for MacOS Users: To change the way the value appears, Click, Click % of column.
Step8. To change the name of the column, in the Custom Name field,
Press & Drag the text, type: % of Total Rx Enter
Step9. To save the SalesAnalysis.xlsx workbook, press:
Utilizing the Conditional Formatting Feature
Conditional formatting is a way to add a visual cue to specific information. We can highlight cells that meet certain criteria, or conditions. Conditional formatting can be used for many different things: marking specific values, detecting patterns, and identifying problems.
We will use conditional formatting to highlight the cells containing the top 10% of prescriptions written. To begin, we will need to select the Total Rx column.
Before we select the data, we want to turn off the Grand Totals. By including the grand totals in our selection, our conditional formatting would not work because the grand total would always be the biggest number.
The Grand Totals options are on the Design tab.
Step1. Move to the Design tab, if necessary.
Step2. To turn off the Grand Totals, in the Layout group,
Click, Click Off for Rows and Columns
Step3. To begin selecting the data,
Click in cell C4
Step4. To select the "% of Total Rx" column, press:
Control key+Shift key+Down Arrow key
Step5. To move to the Home tab, on the Ribbon,
Click the Home tab
Step6. To begin applying conditional formatting, in the Styles group,
Step7. To open the correct Top/Bottom Rule,
Point, Click Top 10%...
Step8. To accept the defaults,
Step9. To deselect the values without closing the Field List
Click in a cell in the PivotTable
NOTE: For more information about conditional formatting, consider taking Excel 2016: Using VLOOKUP & Conditional Functions.
Step10. Save the workbook.
Step11. To close the workbook,
Click the File tab, Click Close
NOTE for MacOS Users: To close the workbook, on the Menu bar, Click File, Click Close.