New Perspectives Excel 365/2021 | Module 7: End of Module Project 1
Pierce Software
SUMMARIZE DATA WITH PIVOTTABLES
GETTING STARTED
Save the file NP_EX365_2021_EOM7-1_FirstLastName_1.xlsx as
NP_EX365_2021_EOM7-1_FirstLastName_2.xlsx
O Edit the file name by changing "1" to "2".
If you do not see the .xlsx file extension, do not type it. The file extension will be
added for you automatically.
With the file NP_EX365_2021_EOM7-1_FirstLastName_2.xlsx open, ensure that
your first and last name is displayed in cell B6 of the Documentation worksheet.
If cell B6 does not display your name, delete the file and download a new copy.
PROJECT STEPS
1.
2.
3.
4.
5.
Tiana Morales is the director of the customer call center for Pierce Software. For the past
three months, she has been keeping track of the support calls the center receives, and
asks for your help in analyzing the data.
Go to the Customer Call Log worksheet, which contains the Calls table where Tiana has
been entering support call data. In the Call Type column, she needs to list the type of
call corresponding to the code in the Call Code column. In cell E5, enter a formula using
the HLOOKUP function to look up the call type according to the call code. Use a
structured reference to look up the value in the Call Code column of the Calls table.
Retrieve the value in row 2 of the CallTypes table (range A3:F4 in the Survey Questions
worksheet) using a structured reference to the entire CallTypes table. Because each call
type covers a range of values, find an approximate match. Fill the formula into the range
E6:E143, if necessary.
Customers can rate their service representative by completing a four-question survey at
the end of each call. Tiana entered the scores in columns Question 1, Question 2,
Question 3, and Question 4 of the Calls table. She wants to calculate the average rating
for each call in the Average column. In cell J5, enter a formula using the AVERAGE
function and structured references to average the values in the Question 1 to Question 4
columns of the Calls table. Fill the formula into the range J6:J143, if necessary.
In the range L4:N13, Tiana set up an area to summarize some data about the service
representatives handling the customer support calls. First, she wants to count the
number of calls each service representative handled. In cell M5, enter a formula using
the COUNTIF function and a structured reference to the Service Rep column in the Calls
table to count the number of calls Barry (cell L5) handled. Fill the formula into the range
M6: M13 without formatting.
Tiana also wants to average the combined scores for each employee. In cell N5, enter a
formula using the AVERAGEIF function and structured references to the Service Rep and
Average columns in the Calls table to average the scores Barry (cell L5) received for all
the calls he handled. Fill the formula into the range N6: N13 without formatting.
Go to the Call Type by Rep worksheet. Tiana wants to compare the call data by type for
each service rep. In cell A3, insert a PivotTable based on the Calls table. Use RepCalls
CENGAGE New Perspectives Excel 365/2021 | Module 7: End of Module Project 1
6.
7.
8.
9.
as the name of the PivotTable. Display the service rep names as column headings, and
the call types as both row headings and values.
Apply Light Yellow, Pivot Style Medium 12 to the PivotTable to coordinate with the table
on the Customer Call Log worksheet. Center the data in the range B4:K10 to make it
easier to read.
Tiana notices that Service Rep Bruno has no calls in the How to category, which she
knows is incorrect. Return to the Customer Call Log worksheet, and then change the Call
Code in cell D10 to 12, which assigns a call to Bruno in the How to category. Go to the
Call Type by Rep worksheet and refresh the data in the PivotTable.
Go to the Scores by Date worksheet. In cell A3, insert another PivotTable based on the
Calls table. Use Scores as the name of the PivotTable. Display the Date values as
column headings, which automatically groups the data by month. Display the Service
Rep names as row headings and the Average score data as values.
Change the summary function to Average for the data in the Values area and apply the
Number number format with 2 decimal places to those values.
10. In cell A3, use the text Average Scores to identify the values. In cell B3, use the text
Months to identify the column headings. In cell A5, use the text Service Reps to
identify the row headings. Apply Light Yellow, Pivot Style Medium 12 to the PivotTable to
match the other PivotTable.
11. Tiana wants to focus on scores for calls involving technical problems in February. Create
a slicer for the Scores PivotTable based on the Call Type field. Resize the slicer to a
height of 2.5". Move the slicer so its upper-left corner is in cell F3 and its lower-right
corner is in cell G14. Use the slicer to filter the Scores PivotTable by the Tech problem
call type.
12. Create a timeline slicer based on the Date field in the Scores PivotTable. Resize the
timeline slicer to a width of 7". Move the slicer so that its upper-left corner appears
within cell A16 and its lower-right corner appears in cell G23. Use the timeline slicer to
show data for February only.
13. Resize columns B:C to 12.00 and center the data in the range B3:C15.
Your workbook should look like the Final Figures on the following pages. Save your changes,
close the workbook, and then exit Excel. Follow the directions on the website to submit your
completed project.
CENGAGE New Perspectives Excel 365/2021 | Module 7: End of Module Project 1
Final Figure 1: Customer Call Log Worksheet
Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2020 Cengage Learning.
All Rights Reserved.
A
Pr
Pierce Software
2 Customer Call Log: January-March
3
Customer Survey Scores
M
N
Question Question Question Question
4
Call ID▾ Date▾ Service Rep▾
Call Code
Call Type▾
1
2
3
4
Average ▾
5
20191 1/4/2024 Wagner
7
Feature request
4
5
2
4
3.75
6
20154 1/4/2024 Thanh
1
Bug report
2
1
3
1
1.75
Rep
Barry
Bruno
Total Calls Average Score
7
21170 1/4/2024 Darego
17
Tech problem
5
5
5
5
5
5.00
Calder
8
21145 1/5/2024 Calder
16
Tech problem
4
4
5
5
5
5
4.25
Darego
9
22103 1/5/2024 Shanahan
21
21
Cancellation
4
4
4
4.00
Shanahan
22246
10
22246 1/6/2024 Bruno
12
How to
4
5
3
4
4.00
Thanh
7
11
22137 1/7/2024 Thanh
7
Feature
Feature request
3
3
3.00
Volmann
12
22234 1/2/2024 Shana
22234
1/7/2024 Shanahan
13
23228
23220
1/7/2024 Wagner
#/7/2024 Wayne!
14
23215 1/8/2024 Calder
15
24117 1/9/2024 Darego
16
24140 1/9/2024 Bruno
25224 1/11/2024 Thanh
23126 1/12/2024 Volmann
25210 1/12/2024 Barry
20189 1/14/2024 Wagner
20120 1/14/2024 Shand
20126 1/14/2024 Shanahan
26157 1/15/2024 Bruno
128129322222
Bug report
2
1
1
1.25
Wagner
Bug report
3
1
2.00
Zimmerman
2222222ND
20
3.65
20
3.35
15
3.60
13
3.71
12
3.25
26
3.41
9
3.89
7
17
3.04
3.46
Feature request
4
3
3.00
25
Cancellation
5
4.50
Feature request
3
3
2.75
Bug report
4
4.25
Bug report
5
5
4.75
How to
3
2.00
Cancellation
1
1
1.00
20
Tech problem
3.25
Cance
Cancellation
2
3
2
2.25
25201 1/16/2024 Thanh
16
Tech problem
4
4
3.50
26149 1/16/2024 Calder
19
Tech problem
5
4
5
5
4.75
23223 1/16/2024 Calder
3
Bug report
5
5
5
5
5.00
24241 1/16/2001 Prin
Tech problem
2
2.00
Documentation Customer Call Log
Call Type by Rep
Scores by Date
Survey Questions
(+
Final Figure 2: Call Type by Rep Worksheet
P
A
Pierce Software
B
с
D
E
F
G
H
I
]
K
L
2 Call Types by Service Rep
3 Count of Call Type Column Labels ▾
☑
Bruno Calder Darego Shanahan Thanh Volmann Wagner Zimmerman Grand Total
4 Row Labels
Barry
5 Bug report
5
5
4
4
1
7
2
1
6
35
6 Cancellation
3
4
3
1
3
2
2
2
20
7 Feature request
3
3
2
1
1
5
1
16
8 How to
6
1
1
1
1
2
2
3
17
9 Tech problem
3
7
5
6
6
10
3
3
8
51
10 Grand Total
20
20
15
13
12
26
9
7
17
139
11
12
13
14
15
16
17
18
19
Documentation
Customer Call Log
Call Type by Rep
Scores by Date
Survey Questions
+
་
CENGAGE New Perspectives Excel 365/2021 | Module 7: End of Module Project 1
Final Figure 3: Scores by Date Worksheet
A
B
C
D
E
F
G
1
Pierce Software
2 Average Monthly Scores
3 Average Scores
4
Months
Call Type
XX
+ Feb
Grand Total
5 Service Reps
6 Barry
7 Bruno
4.50
4.50
4.25
4.25
8 Calder
3.63
3.63
9 Darego
3.33
3.33
Bug report
Cancellation
Feature request
How to
10 Shanahan
3.08
3.08
11 Thanh
3.67
3.67
Tech problem
12 Volmann
4.00
4.00
13 Wagner
2.75
2.75
14 Zimmerman
2.88
2.88
15 Grand Total
3.46
3.46
16 Date
17
Feb 2024
MONTHS ▾
18
2024
19
22222
23
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
20
21
24
Call Type by Rep
Scores by Date Survey
CENGAGE New Perspectives Excel 365/2021 | Module 7: End of Module Project 1
Final Figure 4: Survey Questions Worksheet
A
B
C
Pierce Software
2 Call Type Lookup Table
3 Call Code
4 Call Type
5
0
6
Bug report Feature request
6 Customer Survey Scores
Very satisfied
D
E
F
G
11
16
How to
Tech problem
21
Cancellation
7
5
8
4
Satisfied
9
3
Neutral
10
2
Dissatisfied
11
1
12
Very dissatisfied
13 Customer Survey Questions
14 How satisfied are you with:
16 Question 2
15 Question 1 The service rep's product knowledge
How much time it took to reach a service rep
The overall process of contacting Pierce Software
How the service rep handled your call
17 Question 3
18 Question 4
19
20
21
22
Documentation
Customer Call Log
Call Type by Rep
Scores by Date
Survey Questions
CENGAGE
