IYS Digital Foundations

Using the files below, follow the instructions to complete your final project. Your submission will include many of the skills that you’ve learned throughout this term and include:

• All required formatting
• The theme as specified by the assignment
• Appropriate Styling
• Autofill
• AutoFit
• Accounting Number format
• AutoSum
• Sorting
• Footers
• Multiple Sheets
• Merged cells
• Sum
• Absolute cell reference
• VLOOKUP
• Average
• Conditional formatting
• Table with headers
• Total row
• Pivot tables
• Data Validation

Project Files

• Excel Final InstructionsActions
• Excel Final Worksheet

A Few Guidelines

• You will need to submit an Excel file for your assignment – not another type of file.
• The Excel file must allow me to see the formulas and functions used in order to qualify for credit. So if a question asks for the average body weight of the cars, and only the answer is provided and not the equation itself, full credit cannot be granted.
• You can ask questions on this assignment in the discussion forum, but responses should provide direction not the answer themselves. (e.g. review the module on SumIf() function)
• The work you submit must be your own. 0 credit will be earned if the work submitted is not your own.

When You Are Finished

• When you upload your XLSX assignment file, be sure you keep it in the same format/structure as the original assignment file.
• Be sure to add your last name to the assignment submission.

Important Note

• Since there are multiple ways to accomplish goals in Excel, make sure you use the learning modules within the course to answer and solve these problems. To ensure full credit, you must demonstrate that you’re using the elements taught in the learning modules. Let me know if you have any questions about this.
• Please do not wait until the last minute to ask questions. Ideally, students can work through this assignment while reviewing the learning modules.

Excel Project 1
ISYS 100 – Excel Final Project
Project Description:
For your final Excel project, you will use much of the functionality you’ve learned throughout the semester to
create a management report analyzing a fictitious company’s workforce.
Instructions:
Perform the following tasks:
Step
Points
Possible
Instructions
1
Start Excel. Download and open the file named ISYS 100 Excel Final.
0
2
Some of the columns are too narrow. Use AutoFit to resize all columns to accommodate the
data within them.
2
3
Reformat the column headings in row 1 to the Heading 4 cell style.
2
4
Format the columns as follows:
Left-align all of the Employee ID values in column A.
Reformat the salaries in column I using the Accounting number format with 0 decimal
places.
5
5
Sort all the data by Employee Name in ascending order (A-Z).
5
6
Add a new worksheet for our management report, title it Report.
2
7
In cell A1 of the newly-created Summary tab, enter the title “Workforce Dashboard.” Merge &
Center this title across columns A through F and give the cell a style of Heading 2.
5
8
In Cell A3, insert a new PivotTable. The data for this report is the entire data tab (the range
A1:I83).
5
9
In the PivotTable fields pane, add Department and Job Title to the Rows field well. Add
EmployeeId, Experience, and Salary to the Values field well.
10
10
By default, each of the Values (EmployeeId, Experience, and Salary) will be summed together.
Instead, we’d like a COUNT of EmployeeId, and AVERAGE of Experience & Salary.
10
1
ISYS 100 Excel Final Project
Excel Project 1
Step
Points
Possible
Instructions
11
Rename the columns of the pivot table as follows:
“Row Labels” -> “Department/Title”
“Count of EmployeeId -> “Employee Count”
“Average of Experience” -> Avg. Experience
“Average of Salary” -> “Avg. Salary”
5
12
Format the PivotTable’s Avg. Experience column to have only one decimal place, and format
the Avg. Salary column as Currency with zero decimal places.
5
13
Collapse all of the Departments in the PivotTable by using the
5
14
In addition to the summary PivotTable, we’d like to be able to easily look up information for a
specific employee. In cell F3, enter the text “Choose Employee:”
2
15
We’d like to create a dropdown list of all Employee Names. In cell G3, use Data Validation to
allow a List of values. The source for this list will be all the names on the Data tab.
5
16
In Cells F5 through F8, enter the following values: Department, Job Title, Experience, Salary.
2
17
We would like to use our dropdown menu in cell G3 to populate the cells G5 through G8. This
can best be achieved using a VLOOKUP. The inputs for the VLOOKUP function are as follows:
Lookup Value: G3
Table Array: Data tab, columns B:I
Column Index: 2 (for the Department value)
Range Lookup: FALSE
20
18
Apply bold formatting to all labels in column F. Change the background color of G3 to light
yellow to give users a visual cue that they should input or select a value.
5
19
Format Experience to include only 1 decimal place, and format Salary as Currency. Left align
all of the values in column G. Resize Column G’s width to 30.
3
20
Finally, select the range F3:G8 and add All Borders.
2
button.
Total Points
2
100
ISYS 100 Excel Final Project
EmployeeIdName
Department
Job Title
Gender
BirthDate
12706824 Aaron Rosas
Information Technology
Programmer/Analyst M
2
5/16/1981
15245358 Kevin Valenzuela Executive
Chief Technology Officer
M
10/3/1981
15608133 Gary Allen
Information Technology
Senior Software Architect
M
12/2/1981
17326386 Patricia Roman
Executive
Executive Assistant F
7/20/1983
18936338 Gary Atkins
Information Technology
Programmer/AnalystM
3/3/1980
20396722 Joan Beil
Information Technology
Senior Software Architect
F
4/2/1981
21373843 Ruth Cantu
Information Technology
Project Manager
F
4/20/1980
22028558 Stephen Norris
Information Technology
IT Procurement Specialist
M
2/23/1985
22455716 Terry Copeland
Information Technology
Programmer/Analyst M
2
6/10/1981
23244796 Patrick Hodge
Information Technology
Programmer/Analyst M
2
5/13/1977
24318524 Jason Bernard
Information Technology
Information Security Manager
M
1/27/1980
24946300 Robert Mckay
Information Technology
Programmer/AnalystM
8/17/1984
25981794 Bradley Robbins Finance/AccountingFinancial Analyst
M
11/29/1980
26933839 Victoria Davila
Information Technology
Graphic Designer
F
4/20/1986
28199172 Gregory Schneider Information Technology
Business Intelligence M
Intern
11/7/1998
28798838 Harold Meyer
Information Technology
Programmer/Analyst M
3
8/30/1983
29337288 Willie King
Information Technology
Programmer/Analyst M
3
12/7/1985
30072038 Wayne Rivera
Finance/AccountingFinancial Analyst
M
8/12/1980
31082199 Peter Riley
Information Technology
Programmer/AnalystM
8/4/1982
32210904 Bryan Choi
Information Technology
Information Security Specialist
M
2
3/4/1981
32399210 Jordan Ingram
Information Technology
Project Manager
M
5/6/1979
32857501 Nicole Rich
Finance/AccountingPayroll Manager
F
6/5/1979
33251355 Raymond Harrison Finance/AccountingAR Clerk
M
5/22/1990
33787094 Janet Yang
Information Technology
Programmer/AnalystF
10/30/1980
34544421 Timothy Webb
Information Technology
Programmer/Analyst M
2
3/5/1984
34840397 Wayne Schneider Information Technology
Senior Software Architect
M
1/16/1964
34993452 Christopher CallahanInformation Technology
Programmer/AnalystM
11/27/1979
35542840 Nicholas Grimes Marketing
Marketing Analyst M
5/22/1994
35565204 Dennis Glenn
Information Technology
Programmer/Analyst M
3
1/31/1986
38324586 Lawrence Roman Information Technology
Application Development
M Intern
4/19/1999
39341580 Henry Mckenzie Information Technology
Programmer/Analyst M
3
11/1/1983
39664771 Jessica Horne
Executive
Chief Human Resources
F Officer
1/19/1974
40963404 Brandon Gentry
Executive
Chief Executive Officer
M
11/2/1967
41484206 Sean Daugherty
Information Technology
Database Administrator
M
11/9/1974
41527934 Jack Shields
Information Technology
Associate Database Developer
M
9/27/1996
42489844 Sara Villegas
Human Resources HR Generalist
F
8/26/1991
42585759 Emily Lawson
Information Technology
Programmer/Analyst F3
3/2/1982
45758394 Melissa Hodges
Information Technology
Associate Database Developer
F
1/9/1994
46727270 Tracy Miller
Information Technology
Programmer/AnalystF
9/20/1985
47116065 Steven Mccoy
Information Technology
Programmer/AnalystM
1/11/1987
47241994 Jeffrey Elliott
Information Technology
Information Security Specialist
M
2
4/5/1981
48276450 Lawrence Riley
Information Technology
Programmer/Analyst M
3
7/19/1975
49230165 Megan Cline
Information Technology
Programmer/Analyst F4
7/27/1992
49338994 Abigail Blair
Information Technology
Programmer/Analyst F4
7/4/1974
49915728 Jose Tate
Information Technology
Programmer/Analyst M
3
4/17/1972
50924297 Walter Knapp
Information Technology
Business Intelligence M
Analyst
10/23/1979
51594175 Mark Callahan
Information Technology
Programmer/Analyst M
2
2/5/1986
53662153 Laura Casey
Finance/AccountingAP Clerk
F
2/16/1995
53995575 Brian Cummings Information Technology
Information Security Specialist
M
3
12/8/1975
55681222 Gregory Whittaker Information Technology
Project Manager
M
1/13/1971
57148151 Brandon Hill
Information Technology
Senior Software Architect
M
2/3/1965
60087905 Patrick Page
Information Technology
Director of Application
MDevelopment 11/24/1970
60660092 Patricia Murillo
Information Technology
Application Development
F
Intern
3/8/1999
62974798 Elizabeth Barton Information Technology
Programmer/Analyst F4
7/17/1979
63005979 Carl Miles
Human Resources HR Manager
M
4/12/1981
63350007 Stephen Fischer
Information Technology
Programmer/Analyst M
3
3/28/1979
65876191 Zachary French
Information Technology
Programmer/Analyst M
2
9/7/1976
65924391 Austin Phillips
Information Technology
Business Intelligence M
Analyst
10/26/1971
66879312 Charles Mathews Information Technology
IT Procurement Specialist
M
8/23/1983
67929245 Martha Vo
Information Technology
Programmer/Analyst F2
6/30/1981
68525228 Paul Gallegos
Marketing
Marketing Intern
M
8/11/2000
69127274 Lisa Hamilton
Information Technology
Senior Database Administrator
F
10/8/1970
71612555 Eugene Montoya Information Technology
Programmer/AnalystM
8/7/1987
73395952 Hannah Stuart
Information Technology
Programmer/AnalystF
6/10/1981
73591928 Natalie Cortez
Information Technology
Programmer/AnalystF
6/8/1981
73759981 Deborah Zavala
Information Technology
Senior Software Architect
F
9/14/1970
74778282 Brandon Randall Information Technology
Information Security Specialist
M
3
1/8/1986
75263083 Zachary Stevens Executive
Chief Financial OfficerM
12/1/1966
75488243 Benjamin Calhoun Marketing
Marketing Analyst M
7/23/1990
80462436 Aaron Mcguire
Information Technology
Programmer/Analyst M
3
3/30/1978
80555779 Larry Zhang
Information Technology
Graphic Designer
M
9/2/1983
82180136 Dorothy Combs
Information Technology
Programmer/AnalystF
1/17/1985
82398547 Frances Moon
Executive
General Counsel
F
9/29/1976
82465005 Ralph Skinner
Information Technology
Application Development
M Intern
10/3/1997
82844345 Shannon Flynn
Information Technology
Programmer/Analyst F4
9/8/1978
82893099 Stephen Davenport Marketing
Director of Business Development
M
12/19/1971
82909852 Sharon Flynn
Information Technology
Programmer/Analyst F3
11/8/1983
83520973 Aaron Carlson
Information Technology
Project Manager
M
1/30/1977
84045675 Ruth Horn
Information Technology
Programmer/AnalystF
2/6/1986
84763096 Joyce Fields
Information Technology
Information Security Specialist
F
9/13/1977
87275074 Susan Mccoy
Human Resources HR Generalist
F
2/18/1989
87636454 Samuel Quinn
Information Technology
Associate ApplicationM
Developer
10/9/1993
HireDate
ExperienceSalary
9/20/2016
7.06
67222
12/7/2014
8.85
192320
3/15/2017
6.58
88261
10/10/2013
10.01
49387
12/3/2013
9.86
66147
2/7/2016
7.68
91886
4/30/2007
16.45
93246
1/22/2018
5.72
47431
8/29/2018
5.12
68125
5/2/2014
9.44
66630
4/10/2012
11.51
93427
5/24/2015
8.38
57766
4/9/2017
6.51
61189
5/19/2016
7.40
50652
6/1/2019
4.36
34000
9/25/2012
11.05
70814
8/9/2013
10.18
60610
11/7/2016
6.93
61840
4/16/2018
5.49
53614
11/10/2016
6.92
65788
4/18/2009
14.48
86880
12/3/2014
8.86
59055
9/18/2015
8.07
41042
5/20/2015
8.39
55243
11/26/2014
8.88
66514
10/10/2014
9.01
93322
4/2/2018
5.53
61135
2/25/2014
9.63
38824
10/1/2016
7.03
74974
6/1/2019
4.36
36000
10/22/2009
13.97
70534
4/28/2012
11.46
135000
7/29/2012
11.20
275500
10/30/2016
6.95
63500
10/6/2016
7.02
44684
6/28/2018
5.29
38630
10/28/2014
8.96
77022
5/27/2018
5.38
46280
9/21/2015
8.06
66403
11/7/2016
6.93
57857
2/14/2016
7.66
80151
8/9/2013
10.18
76530
1/9/2014
9.76
93739
11/29/2016
6.87
87089
4/14/2014
9.49
78308
11/19/2012
10.90
68800
11/14/2017
3/25/2016
2/1/2011
8/20/2009
6/14/2017
12/24/2004
6/1/2019
11/11/2003
5/16/2003
11/16/2008
3/7/2017
1/9/2014
11/7/2017
3/26/2015
6/1/2019
4/15/2011
4/5/2018
1/12/2016
10/17/2013
11/4/2015
10/13/2012
2/28/2001
7/28/2016
10/12/2007
12/28/2016
2/7/2015
12/12/2011
6/1/2019
6/19/2008
8/30/2015
10/4/2007
12/28/2016
9/18/2015
8/9/2015
1/12/2015
5/31/2015
5.91
7.55
12.70
14.14
6.33
18.80
4.36
19.92
20.41
14.91
6.60
9.76
5.93
8.54
4.36
12.49
5.52
7.75
9.99
7.94
11.00
22.62
7.21
16.00
6.79
8.68
11.83
4.36
15.31
8.12
16.02
6.79
8.07
8.18
8.75
8.37
65121
38767
71408
88848
89648
106450
36000
85139
82875
68443
66738
64300
46610
58013
28000
72120
54556
60619
57568
86959
66356
216000
39980
83596
48502
57074
185750
36000
90312
118200
65948
92698
63630
67114
38317
52500