Albany State University Technical Support Calls Report 1 Download and open the file named EXP19_Ch04_Cap_TechSupport.xlsx. Grader has automatically add

Albany State University Technical Support Calls Report 1

Download and open the file named EXP19_Ch04_Cap_TechSupport.xlsx. Grader has automatically added your last name to the beginning of the filename.

2

Freeze Panes so the first row containing column headings (Row 5) on the SupportCalls worksheet will remain static when scrolling. Ensure that Rows 1-4 are visible.

3

Convert the data to a table, name the table SupportCalls, and apply the Gold, Table Style Medium 12.

4

Remove duplicate records.

5

Add a new column to the table named Duration.

6

Create a formula using unqualified structured references to calculate the days required to resolve the incident (Date Resolved – Date Created).

7

Add a total row to display the Average days required to resolve an issue.

8

Sort the table by Agent Name in alphabetical order, add a second level to sort by Description, and create a custom sort order as follows: Won’t power on, Virus, Printing Issues, Software Update, Forgotten Password. Add a third level to sort by Duration smallest to largest. (Mac users, to create the custom list, from the Excel menu, click Preferences. In the dialog box, click Custom Lists.)

9

Filter the table to only display closed incidents as indicated in the status column.

10

Use Quick Analysis to apply Data Bars conditional formatting to the Duration column (range I6:I85). Mac users, on the Home tab, click Conditional Formatting, and under Solid Fill, click Blue Data Bar.

11

Create a new conditional format that applies Red fill and bold font to incident (range A6:A85) that required 30 or more days to resolve.

12

Change page breaks so page 2 begins with the Computer ID column (column E).

13

Set the print scale to 85%.

14

Add a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side.

15

Save and close the workbook. Submit the workbook as directed. Technical Support Calls
Threshold
Incident Number
939-76160
712-90501
354-52747
354-52747
859-45748
610-12016
459-84377
218-35195
140-57449
399-98477
657-68422
840-29369
285-88931
254-39938
733-99609
207-19047
608-25364
967-11630
398-79106
619-44822
752-21845
131-31453
553-87138
727-78890
284-56116
812-94308
422-24141
290-81093
309-20285
614-70314
972-55567
862-62517
751-81316
884-29640
347-52802
760-87938
605-47583
692-91711
878-43585
164-64518
30
Date created
1/1/2021
1/1/2021
1/7/2021
1/7/2021
1/11/2021
1/13/2021
1/13/2021
1/13/2021
1/14/2021
1/15/2021
1/16/2021
1/17/2021
1/26/2021
2/4/2021
2/4/2021
2/4/2021
2/7/2021
2/9/2021
2/10/2021
2/10/2021
2/11/2021
2/13/2021
2/14/2021
2/15/2021
2/17/2021
2/17/2021
2/18/2021
2/19/2021
2/20/2021
2/20/2021
2/25/2021
3/2/2021
3/3/2021
3/4/2021
3/5/2021
3/8/2021
3/9/2021
3/10/2021
3/13/2021
3/14/2021
days
Last Name
Norris
Harvey
Neal
Neal
Lee
Glover
Barnett
Ross
Pearson
King
Washington
Hopkins
Bell
Stone
Alexander
Cook
Baldwin
Mendez
Hardy
Silva
Hanson
Medina
Brown
Watts
Ramos
Powers
Carpenter
Gregory
Coleman
Gutierrez
Carroll
Pena
Ramirez
Watts
Larson
Burke
Garner
Richards
Hale
Hines
Agent Name
Cox
Rousaux
Rousaux
Rousaux
Vong
Leggett
Vong
Rousaux
Vong
Vong
Hays
Leggett
Hays
Broadnax
Vong
Broadnax
Leggett
Vong
Vong
Cox
Leggett
Vong
Hays
Dodson
Broadnax
Cox
Leggett
Cox
Hays
Leggett
Cox
Broadnax
Hays
Broadnax
Vong
Hays
Cox
Broadnax
Dodson
Rousaux
Computer ID
3141
5995
9380
9380
5070
5891
1251
6735
6235
7426
6405
3708
1756
2971
9454
1850
4317
6724
4549
8950
7444
5469
7227
4468
9018
5600
4978
8041
3198
3801
8582
5690
9751
4478
8874
6674
9589
3574
2853
1675
330-67419
774-85680
859-12568
683-70001
747-57548
247-70541
905-42019
905-42019
519-68338
573-57033
179-51542
554-76104
359-25878
831-91089
600-91856
830-21577
608-17014
756-15938
176-51222
533-90278
418-53177
971-93713
424-30728
677-54555
875-36070
692-66855
628-20629
834-13215
295-57913
868-40830
719-34522
374-70234
844-13469
649-90703
240-64918
727-38195
975-62266
862-52194
904-44724
556-31818
824-74907
677-39516
677-39516
286-68356
207-49346
816-11026
3/15/2021
3/16/2021
3/18/2021
3/22/2021
3/24/2021
3/24/2021
3/26/2021
3/26/2021
3/26/2021
3/28/2021
3/29/2021
3/30/2021
3/31/2021
4/4/2021
4/5/2021
4/7/2021
4/7/2021
4/15/2021
4/16/2021
4/17/2021
4/17/2021
4/25/2021
4/26/2021
4/28/2021
5/2/2021
5/2/2021
5/3/2021
5/3/2021
5/5/2021
5/5/2021
5/5/2021
5/7/2021
5/9/2021
5/9/2021
5/9/2021
5/14/2021
5/15/2021
5/16/2021
5/17/2021
5/18/2021
5/18/2021
5/20/2021
5/20/2021
5/23/2021
5/27/2021
5/28/2021
Miller
Murray
Soto
Newman
Murray
Rodgers
Ward
Ward
Perry
Parker
Gray
Carroll
Medina
Herrera
Scott
Black
Patton
Leonard
Sims
Hoffman
Reyes
Perry
Walker
Lowe
Delgado
Fletcher
Rodriquez
Fields
Nguyen
Griffin
Shelton
Johnson
Curtis
Butler
Frazier
Robbins
Kim
Clark
Ortega
Graham
Soto
Marshall
Marshall
Johnson
Mckinney
Parks
Cox
Broadnax
Hays
Leggett
Broadnax
Dodson
Rousaux
Rousaux
Leggett
Dodson
Hays
Vong
Cox
Broadnax
Dodson
Rousaux
Vong
Rousaux
Vong
Broadnax
Hays
Vong
Dodson
Vong
Dodson
Rousaux
Hays
Leggett
Cox
Dodson
Leggett
Cox
Rousaux
Vong
Cox
Hays
Broadnax
Broadnax
Vong
Rousaux
Hays
Vong
Vong
Vong
Rousaux
Vong
5423
4498
8586
6580
1992
2340
5173
5173
3850
7970
1577
6399
2044
4998
3696
8038
5821
6999
9260
3168
2955
3637
9955
6330
3488
7476
4504
8397
6494
7125
6925
1834
3345
2651
6903
9354
9834
5897
8203
9795
7464
1975
1975
3482
6211
6632
Description
Forgotten Password
Forgotten Password
Software Update
Software Update
Virus
Forgotten Password
Printing Issues
Virus
Won’t power on
Printing Issues
Software Update
Software Update
Printing Issues
Won’t power on
Virus
Virus
Software Update
Forgotten Password
Printing Issues
Software Update
Virus
Printing Issues
Virus
Printing Issues
Virus
Won’t power on
Forgotten Password
Printing Issues
Won’t power on
Software Update
Printing Issues
Won’t power on
Virus
Won’t power on
Printing Issues
Forgotten Password
Forgotten Password
Forgotten Password
Virus
Won’t power on
Status
Open
Open
Hold
Hold
Closed
Hold
Hold
Closed
Hold
Closed
Closed
Open
Hold
Closed
Open
Closed
Open
Hold
Closed
Closed
Open
Closed
Open
Closed
Closed
Open
Closed
Hold
Closed
Closed
Hold
Hold
Closed
Hold
Closed
Hold
Closed
Open
Closed
Closed
Date Resolved
2/6/2021
1/19/2021
1/24/2021
1/24/2021
1/18/2021
1/18/2021
2/6/2021
1/27/2021
2/2/2021
1/26/2021
2/9/2021
2/13/2021
2/25/2021
2/20/2021
2/17/2021
2/12/2021
3/6/2021
2/28/2021
2/23/2021
2/12/2021
2/27/2021
2/18/2021
3/9/2021
2/28/2021
2/28/2021
2/21/2021
2/26/2021
3/10/2021
3/20/2021
3/3/2021
2/28/2021
3/15/2021
3/24/2021
3/22/2021
3/26/2021
3/28/2021
3/14/2021
4/8/2021
3/21/2021
6/12/2021
Printing Issues
Virus
Won’t power on
Forgotten Password
Won’t power on
Software Update
Printing Issues
Printing Issues
Forgotten Password
Won’t power on
Software Update
Virus
Virus
Won’t power on
Virus
Forgotten Password
Won’t power on
Printing Issues
Won’t power on
Virus
Virus
Won’t power on
Won’t power on
Won’t power on
Won’t power on
Virus
Virus
Virus
Won’t power on
Forgotten Password
Virus
Forgotten Password
Won’t power on
Won’t power on
Forgotten Password
Virus
Virus
Won’t power on
Printing Issues
Software Update
Printing Issues
Printing Issues
Printing Issues
Forgotten Password
Virus
Won’t power on
Hold
Open
Closed
Closed
Closed
Closed
Open
Open
Hold
Hold
Closed
Open
Hold
Closed
Open
Hold
Open
Hold
Hold
Open
Hold
Hold
Closed
Hold
Closed
Open
Hold
Open
Closed
Closed
Open
Open
Open
Open
Hold
Hold
Hold
Hold
Hold
Closed
Open
Hold
Hold
Open
Open
Closed
4/5/2021
3/31/2021
4/5/2021
3/26/2021
3/25/2021
4/11/2021
4/17/2021
4/17/2021
4/12/2021
4/21/2021
4/9/2021
4/24/2021
4/18/2021
4/30/2021
4/15/2021
4/13/2021
4/15/2021
4/23/2021
5/10/2021
4/19/2021
5/17/2021
4/30/2021
5/2/2021
5/27/2021
5/28/2021
5/29/2021
5/16/2021
6/2/2021
5/13/2021
5/7/2021
5/10/2021
5/22/2021
5/20/2021
6/8/2021
6/8/2021
6/8/2021
5/21/2021
5/26/2021
5/27/2021
6/5/2021
6/14/2021
5/31/2021
5/31/2021
6/14/2021
6/5/2021
7/21/2021
Grader – Instructions
Excel 2019 Project
Exp19_Excel_Ch04_Cap_Tech_Support
Project Description:
You have been hired as a student assistant in the IT department of your University. As part of your responsibilities, you have
been asked to enhance the Excel workbook used to analyze the departments performance. The workbook contains records of
all support issues resolved over the past year. You will convert the data to a table, format the table, sort and filter the table,
insert calculations to evaluate key performance indicators, and prepare the worksheet for printing
Steps to Perform:
Step
Points
Possible
Instructions
1
Download and open the file named EXP19_Ch04_Cap_TechSupport.xlsx. Grader has
automatically added your last name to the beginning of the filename.
0
2
Freeze Panes so the first row containing column headings (Row 5) on the SupportCalls
worksheet will remain static when scrolling. Ensure that Rows 1-4 are visible.
5
3
Convert the data to a table, name the table SupportCalls, and apply the Gold, Table Style
Medium 12.
13
4
Remove duplicate records.
6
5
Add a new column to the table named Duration.
4
6
Create a formula using unqualified structured references to calculate the days required to
resolve the incident (Date Resolved – Date Created).
10
7
Add a total row to display the Average days required to resolve an issue.
6
8
Sort the table by Agent Name in alphabetical order, add a second level to sort by Description,
and create a custom sort order as follows: Won’t power on, Virus, Printing Issues,
Software Update, Forgotten Password. Add a third level to sort by Duration smallest to
largest. (Mac users, to create the custom list, from the Excel menu, click Preferences. In the
dialog box, click Custom Lists.)
12
9
Filter the table to only display closed incidents as indicated in the status column.
10
10
Use Quick Analysis to apply Data Bars conditional formatting to the Duration column (range
I6:I85). Mac users, on the Home tab, click Conditional Formatting, and under Solid Fill, click
Blue Data Bar.
12
11
Create a new conditional format that applies Red fill and bold font to incident (range A6:A85)
that required 30 or more days to resolve.
12
12
Change page breaks so page 2 begins with the Computer ID column (column E).
2
13
Set the print scale to 85%.
4
14
Add a footer with your name on the left side, the sheet name code in the center, and the file
name code on the right side.
4
15
Save and close the workbook. Submit the workbook as directed.
0
Total Points
Created On: 09/19/2019
1
100
Exp19_Excel_Ch04_Cap – Tech Support 1.6
?
B
?
D
E
F
G
H
I
Technical Support Calls
2
3 Threshold
30
days
4
5 Incident Number – Date created Last Name Agent Name Computer ID
34 727-78890
2/15/2021 Watts Dodson
4468
35 247-70541
3/24/2021 Rodgers Dodson
2340
36 868-40830
5/5/2021 Griffin Dodson
7125
37 859-12568
3/18/2021 Soto
Hays
8586
38 309-20285
2/20/2021 Coleman
Hays
3198
40 751-81316
3/3/2021 Ramirez
Hays
9751
46 179-51542
3/29/2021 Gray
Hays
1577
47 657-68422
1/16/2021 Washington Hays
6405
52 614-70314
2/20/2021 Gutierrez Leggett
3801
55 683-70001
3/22/2021 Newman
Leggett
6580
57 422-24141
2/18/2021 Carpenter Leggett
4978
60 164-64518
3/14/2021 Hines
Rousaux
1675
62 218-35195
1/13/2021 Ross
Rousaux
6735
67 556-31818
5/18/2021 Graham Rousaux
9795
74 816-11026
5/28/2021 Parks
Vong
6632
77 859-45748
1/11/2021 Lee
Vong
5070
80 131-31453
2/13/2021 Medina Vong
5469
82 399-98477
1/15/2021 King
Vong
7426
84 398-79106
2/10/2021 Hardy Vong
4549
85 347-52802
3/5/2021 Larson Vong
8874
89 Total
90
91
92
93
SupportCalls +
Accessibility: Investigate
Description
Printing Issues
Software Update
Forgotten Password
Won’t power on
Won’t power on
Virus
Software Update
Software Update
Software Update
Forgotten Password
Forgotten Password
Won’t power on
Virus
Software Update
Won’t power on
Virus
Printing Issues
Printing Issues
Printing Issues
Printing Issues
Status
Closed
Closed
Closed
Closed
Closed
Closed
Closed
Closed
Closed
Closed
Closed
Closed
Closed
Closed
Closed
Closed
Closed
Closed
Closed
Closed
– Date Resolved – Duration –
2/28/2021
13
4/11/2021
18
5/7/2021
2
4/5/2021
18
3/20/2021
28
3/24/2021
21
4/9/2021
11
2/9/2021
24
3/3/2021
11
3/26/2021
4
2/26/2021
8
6/12/2021
90
1/27/2021
14
6/5/2021
18
7/21/2021
54
1/18/2021
7
2/18/2021
5
1/26/2021
11
2/23/2021
13
3/26/2021
21
16.387097

Purchase answer to see full
attachment