Conditional Formatting Job Aid Excel Project I need help filling these questions from an excel spreadsheet. The information is given and there are a few qu

Conditional Formatting Job Aid Excel Project I need help filling these questions from an excel spreadsheet. The information is given and there are a few questions which shouldn’t take long to answer. Pivot Table: Job Aid
1
PIVOT TABLE
Purpose: “Pivot Table” can help you to summarize your big data within seconds. Let’s go
through some images to understand how it works.
Let’s say in a hospital, there are 5 types of jobs: nurse, nurse assistant I, nurse II, nurse manager,
and X-Ray Technician I. Now you have the data and you need to know how many employees
there are for each job.
1. Select all the data in the Excel file (the image does not show all, but actually there are
1000 employees in the data set)
2. Then go to “Insert” tab and select “Pivot Table”
2
3. After you have clicked the “Pivot Table”, you will see a box like below. All you need to
do is to click “Ok”.
4. Then you will see:
3
5. At the right-hand side, you will see the “Pivot Table Fields”, which is the core of a Pivot
Table. Some frame of references here:
a. Rows: Variable that you put in here will show up at the as the Row in the Pivot Table
(use very often)
b. Values: Variable that you put in here will show its’ value. The value can be in the
form of “count”, “sum”, “percentage”, etc.
c. Filter: Variable that you put in here will not show up in the Pivot Table (does not use
this often)
d. Columns: Variable that you put in here will show up at the as the Column in the Pivot
Table (does not use this often)
4
6. Now click on the “job” and drag it to the Rows (image at left). You will see the Pivot
Table of job category shows up (image at the right).
7. After that, click the job at the top again and drag it to the Values. As you can see, the
counts of employees in each job category are shown in the Pivot Table
5
USEFUL TIPS FOR VALUES. SOMETIMES YOU WILL RUN INTO SITUATION
THAT YOU NEED TO COUNT THE SUM, AVERAGE, MIN, MAX, ETC. YOU CAN
DO THAT THROUGH THE VALUE. FOLLOW THE STEPS BELOW:
1. Click on the drop-down arrow of the variable “count of job” in the value box. Then select
“Value Field Setting”
2. After clicking the “Value Field Settings”, you will see a box like below. All you need to
do is to select the math formula that you want and the Pivot table will do the math for you
6
WHEN YOU HAVE MORE THAN ONE VARIABLE
Most of the time your boss will ask you to do some “cuts” or “branching”, which means they
want to compare two variables. For example, after you have found out how many people in each
job category, your boss wants to know the cut on gender (i.e., male and female employees in
each job category). You can do the following steps:
1. Do the Step 1 to 7 for the job category, and then drag the variable “gender” and put it in
the Rows. It can be either above or below the “job”. Then, you have it!
7
Conditional Formatting:
Job Aid
1
CONDITIONAL FORMATTING
Purpose: The purpose of conditional formatting is to highlight cells with a certain color or
picture, depending on the cell’s value. As the “conditional” in the “conditional formatting”
namesake implies, the highlighting depends on conditions or rules. These rules can range from
preset ones already included in excel or ones that you create manually. Either way, it’s a simple
way to highlight any interesting information and can be used in conjunction with filters (e.g.,
filter by color) or functions (e.g., IF function).
Let’s go over some basics before we practice some conditional formatting:
Conditional Formatting can be easily accessed in the Home tab of Microsoft Excel
Clicking the Conditional Formatting will bring a drop list of options.
2
The first two options (i.e., Highlight Cells Rules and Top/Bottom Rules) are preset rules and
formatting that can be used.
The next three options (i.e., Data Bars, Color Scales, and Icon Sets) are preset types of
formatting that can be used. One thing to note here is that you also have to manually specify a
rule in “More Rules” for these to work.
3
The last three options (i.e., New Rule, Clear Rules, Manage Rules) are used for creating or
managing conditional rules that are set.
Now that you know the basics, lets practice some Conditional Formatting!
4
HOW TO DO CONDITIONAL FORMATTING
Say your boss gives you a spread sheet that includes employees’ name, phone number, and city
where they were recruited from. Your boss asks you to provide the total number of people
recruited from each city BUT only if there are more than 1 employees from that city. Here is the
spread sheet:
1) Highlight from D2 to D11 before using the Conditional Formatting
5
2) Click on Conditional Formatting, Highlight Cells Rules, then Duplicate Values…
3) Then you will see cities with more than one employees are highlighted as below.
6
4) If you do not like the color, you can change it using Custom Format… Then, Format
Cells will pop up.
7
5) After you select your color, proceed by clicking “OK”, and then clicking “OK” again on
the next window.
Select a color
that you like!
Then click “OK”
at the bottom.
6) We’re done! If everything worked, then you will notice that only the cities with multiple
employees are highlighted. Your sample should look similar to this:
8
Job Code
00007-00
00008-00
00009-00
00015-00
00701-00
00701-62
00702-00
00703-00
02005-00
02009-00
02203-00
02218-00
02219-00
02221-00
02501-00
02504-00
02505-00
02514-00
02518-00
02518-26
02518-28
02519-00
02520-00
04202-00
04203-00
06109-00
06116-00
06205-00
06206-00
06301-00
06302-00
06303-00
06401-00
06402-00
06403-00
06404-00
06503-00
06511-00
06513-00
09018-00
09523-00
09740-00
Company Job Title
Architect Assistant
Architect
Senior Architect
Senior Landscape Architect
Engineer Assistant
Engineer Assistant-Fire Protection
Engineer
Senior Engineer
Computer Aided Drafting Technician
Drafter
Surveyor Assistant
Surveyor
Surveyor Trainee
Survey Crew Chief
Project Assistant
Design Technician II
Senior Design Technician
Project Coordinator II
Project Coordinator III
Project Coordinator III-Planning
Project Coordinator III-Public Projects
Senior Project Coordinator
Project Coordinator
Senior Chemist
Chemist
Senior Public Health Nurse
Licensed Vocational Nurse
Sanitarian Trainee
Sanitarian
Environmental Specialist
Environmental Specialist II
Environmental Specialist III
Environmental Coordinator
Environmental Coordinator II
Environmental Coordinator III
Senior Environmental Coordinator
Psychologist
Public Health Nutritionist
Nutritionist
Branch Delivery Assistant
Library Associate
Librarian
Grade
H
K
N
K
I
H
J
M
E
E
D
J
H
E
C
F
H
I
J
I
J
M
F
I
I
K
F
D
H
C
E
H
I
I
J
K
N
F
E
A
D
F
09741-00
09743-00
12410-00
12413-00
12427-00
12439-00
12439-06
14030-00
14033-00
14301-00
14302-00
14303-00
14407-00
14410-00
14520-00
16404-00
16404-05
16404-48
16404-51
16405-00
16405-05
16405-42
16405-48
16405-51
16406-00
16412-00
16412-05
16412-06
16412-20
16412-28
16412-32
16412-42
16412-48
16412-51
16415-00
16605-28
16606-00
16606-06
16606-10
16606-28
16606-34
16606-60
18011-00
Librarian II
Senior Librarian
Veterinary Assistant
Veterinarian
Senior Parks and Recreation Manager
Animal Keeper II
Animal Keeper II-Code Enforcement
Forestry Worker
Pesticide Applicator
Assistant Greens Superintendent
Greens Superintendent
Senior Greens Superintendent
Therapeutic Recreation Assistant
Therapeutic Recreation Specialist
Recreation Program Specialist
Senior Plans Examiner
Senior Plans Examiner-Building Inspection
Senior Plans Examiner-Electrical
Senior Plans Examiner-Plumbing
Senior Inspector
Senior Inspector -Building Inspection
Senior Inspector -Development
Senior Inspector -Electrical
Senior Inspector -Plumbing
Assistant Building Official
Inspector III
Inspector III -Building Inspection
Inspector III -Code Enforcement
Inspector III -Housing
Inspector III -Public Projects
Inspector III -Transportation
Inspector III -Development
Inspector III -Electrical
Inspector III -Plumbing
Arborist
Inspector-Public Projects
Inspector II
Inspector II-Code Enforcement
Inspector II-Constr Insp
Inspector II-Public Projects
Inspector II-Water
Inspector II-Signs & Markings
Accountant III
H
I
C
M
N
B
B
B
D
I
I
J
A
F
B
I
I
I
I
I
I
I
I
I
M
I
I
H
H
H
H
I
I
I
H
D
F
F
I
F
F
F
I
18012-00
18013-00
18014-00
18014-58
18024-00
18032-00
18033-00
18102-00
18103-00
18104-00
18105-00
20001-00
20001-66
20003-00
20006-00
20022-00
20033-00
20034-00
20119-00
20120-00
20150-00
20173-00
20203-00
20207-00
20208-00
20814-00
20828-00
20836-00
20836-75
20911-00
20941-00
20942-00
20943-00
20963-00
22011-00
22012-00
22013-00
22014-00
22017-00
22019-00
22020-00
22021-00
22202-00
Accountant II
Senior Accountant
Financial Accountant
Financial Accountant-Auditing
Audit Accountant
Budget Analyst II
Senior Budget Analyst
Assistant City Auditor II
Assistant City Auditor III
Assistant City Auditor IV
Assistant City Auditor V
Instructor
Instructor-Strategic CS
Senior Contract Compliance Administrator
Contract Compliance Administrator
Human Resources Assistant
Human Resources Analyst II
Senior Human Resources Analyst
Fitness Specialist
Fitness Supervisor
Test Validation Specialist
Quality Assurance Specialist
Senior Buyer
Buyer
Buyer III
Human Services Program Specialist
Safety Officer II
Senior Risk Analyst
Senior Risk Analyst-Risk Mgt
Fund Analyst
Employees’ Pension Benefits Specialist
Senior Employees’ Retirement Fund Benefits Specialist
Senior Employees’ Retirement Fund Accounting Specialist
Senior Benefits Specialist
Senior Assistant City Attorney I
Executive Assistant City Attorney
Assistant City Attorney I
Paralegal
Legal Office Administrator
Senior Assistant City Attorney II
Assistant City Attorney II
Senior Executive Assistant City Attorney
Associate Municipal Judge
H
I
J
K
N
H
I
I
L
N
P
E
D
I
H
E
H
I
F
I
J
D
I
E
H
F
J
I
I
H
F
I
I
I
L
Q
I
H
K
N
J
S
N
22204-00
22407-00
22408-00
24002-00
24003-00
24100-00
24101-00
24103-00
24107-00
24108-00
24110-00
24111-00
24121-00
24300-00
24301-00
24302-00
24304-00
24307-00
24309-00
24311-00
24312-00
24322-00
24327-00
24334-00
24380-00
24390-00
24404-00
24423-00
24433-00
24447-00
24460-00
24718-00
24722-00
24725-00
24729-00
24741-00
24806-00
24830-00
24850-00
26001-00
26002-00
26003-00
26022-00
Administrative Judge
Title Examiner
Abstractor
Executive Assistant
Council Assistant
City Manager
Assistant City Manager
First Assistant City Manager
Chief Planning Officer
Chief Wellness Officer
Intern
Intern II
General Manager
City Attorney
City Auditor
City Secretary
Civil Service Board Secretary
Employees’ Retirement Fund Administrator
Employees’ Retirement Fund Investment Officer
Fire Chief
Police Chief
Park and Recreation Director
Chief Information Officer
Chief Financial Officer
Director I
Director II
Third Tier Executive
Municipal Judge
Police Deputy Chief
Fire Deputy Chief
Employees’ Retirement Fund Pension Officer
Assistant Director Parks and Recreation
Civil Service Assistant Director
Assistant City Secretary
First Assistant City Auditor
Employees’ Retirement Fund Assistant Administrator
City Controller
Assistant Director I
Assistant Director II
Senior Relocation Specialist
Relocation Specialist
Loan Services Representative
Communication Outreach Representative
O
I
D
I
I
S
S
S
S
S
A
C
P
S
S
P
S
S
O
S
S
S
S
S
Q
S
N
N
N
L
M
Q
P
M
Q
S
R
O
P
H
E
E
E
26027-00
26028-00
26030-00
26031-00
26032-00
26094-00
26097-00
26115-06
28041-00
28043-00
28044-00
28056-00
29004-00
30005-00
30010-00
30010-01
30010-04
30010-05
30010-06
30010-08
30010-14
30010-15
30010-33
30011-00
30011-03
30011-04
30011-05
30011-06
30011-07
30011-08
30011-12
30011-14
30011-15
30011-16
30011-17
30011-21
30011-23
30011-28
30011-29
30011-30
30011-31
30011-36
30011-37
Community Service Worker
Senior Community Service Worker
Caseworker
Caseworker II
Senior Caseworker
Fair Housing Conciliator
Fair Housing Investigator
Neighborhood Code Representative-Code Enforcement
Planner
Senior Planner
Chief Planner
Planning Technician
Economic Development Analyst
Senior Executive Assistant
Manager
Manager -Animal Control
Manager -Business
Manager -Building Inspection
Manager -Code Enforcement
Manager -Community Service
Manager -Events
Manager -Facilities
Manager -Warehousing
Manager II
Manager II -Aviation
Manager II -Business
Manager II -Building Inspection
Manager II -Code Enforcement
Manager II -Communications
Manager II -Community Service
Manager II -Environment
Manager II -Events
Manager II -Facilities
Manager II -Field Services
Manager II -Fleet Services
Manager II -Human Resources
Manager II -Nursing
Manager II -Public Projects
Manager II -Public Safety
Manager II -Recreation
Manager II -Security
Manager II -WRR
Manager II -Park Maint
B
C
H
H
I
H
E
H
I
J
K
F
I
N
I
I
I
I
H
J
H
H
H
J
K
J
J
J
K
K
J
J
K
J
J
K
M
K
M
K
I
I
I
30011-39
30011-50
30011-51
30011-62
30011-68
30011-70
30011-71
30011-75
30011-77
30012-00
30012-01
30012-03
30012-04
30012-06
30012-12
30012-14
30012-16
30012-17
30012-20
30012-21
30012-24
30012-26
30012-27
30012-28
30012-30
30012-31
30012-33
30012-34
30012-39
30012-41
30012-42
30012-50
30012-52
30012-54
30012-66
30012-71
30013-00
30013-04
30013-34
30013-42
30017-00
30017-05
30017-08
Manager II -Solid Waste
Manager II -Library
Manager II -Plumbing
Manager II -Fire Protection
Manager II -Landfill
Manager II -SAP
Manager II -Aquatics
Manager II -Risk Mgt
Manager II -Safety
Manager III
Manager III-Animal Control
Manager III-Aviation
Manager III-Business
Manager III-Code Enforcement
Manager III-Environment
Manager III-Events
Manager III-Field Services
Manager III-Fleet Services
Manager III-Housing
Manager III-Human Resources
Manager III-Nutrition
Manager III-Planning
Manager III-Property Mgmt
Manager III-Public Projects
Manager III-Recreation
Manager III-Security
Manager III-Warehousing
Manager III-Water
Manager III-Solid Waste
Manager III-Cash Mgmt
Manager III-Development
Manager III-Library
Manager III-Food Protection
Manager III-Outreach
Manager III-Strategic CS
Manager III-Aquatics
Senior Program Manager
Senior Program Manager-Business
Senior Program Manager-Water
Senior Program Manager-Development
Coordinator III
Coordinator III-Building Inspection
Coordinator III-Community Service
J
I
K
K
J
J
J
K
K
L
N
N
L
L
L
L
M
N
L
M
N
M
L
M
L
M
L
L
L
L
M
J
L
M
M
L
O
N
N
O
I
I
I
30017-14
30017-20
30017-24
30017-39
30017-47
30017-69
30017-70
30017-77
30019-00
30019-03
30019-08
30019-14
30019-30
30019-39
30020-00
30020-14
30020-26
30020-30
30020-34
30020-39
30020-54
30021-00
30021-12
30021-14
30021-20
30021-30
30021-47
30021-54
30021-65
30021-70
30021-71
30022-00
30022-05
30022-07
30022-26
30022-34
30022-42
30022-63
30023-00
30023-15
30023-16
30023-18
30023-22
Coordinator III-Events
Coordinator III-Housing
Coordinator III-Nutrition
Coordinator III-Solid Waste
Coordinator III-Emergency Mgmt
Coordinator III-Training
Coordinator III-SAP
Coordinator III-Mosquito Ctrl
Coordinator
Coordinator-Aviation
Coordinator-Community Service
Coordinator-Events
Coordinator-Recreation
Coordinator-Solid Waste
Coordinator II
Coordinator II-Events
Coordinator II-Planning
Coordinator II-Recreation
Coordinator II-Water
Coordinator II-Solid Waste
Coordinator II-Outreach
Coordinator IV
Coordinator IV-Environment
Coordinator IV-Events
Coordinator IV-Housing
Coordinator IV-Recreation
Coordinator IV-Emergency Mgmt
Coordinator IV-Outreach
Coordinator IV-Forestry
Coordinator IV-SAP
Coordinator IV-Aquatics
Senior Coordinator
Senior Coordinator-Building Inspection
Senior Coordinator-Communications
Senior Coordinator-Planning
Senior Coordinator-Water
Senior Coordinator-Development
Senior Coordinator-Statistical Analysis
Supervisor
Supervisor-Facilities
Supervisor-Field Services
Supervisor-Heavy Equipment
Supervisor-Maintenance
H
F
H
I
F
H
I
I
E
H
E
E
E
F
F
F
H
F
F
H
H
J
L
I
I
I
I
K
I
I
H
K
K
J
K
I
K
I
H
H
H
I
I
30023-25
30023-29
30023-31
30023-34
30023-37
30023-39
30023-44
30023-45
30023-48
30023-53
30023-68
30024-00
30024-01
30024-03
30024-14
30024-16
30024-17
30024-18
30024-25
30024-30
30024-31
30024-33
30024-34
30024-37
30024-39
30024-45
30024-48
30024-49
30024-51
30024-66
30024-71
30024-76
30025-00
30025-02
30025-07
30025-10
30025-12
30025-14
30025-15
30025-16
30025-18
30025-20
30025-25
Supervisor-Office
Supervisor-Public Safety
Supervisor-Security
Supervisor-Water
Supervisor-Park Maint
Supervisor-Solid Waste
Supervisor-Electronic Tech
Supervisor-Climate Control
Supervisor-Electrical
Supervisor-Process Control
Supervisor-Landfill
Supervisor II
Supervisor II-Animal Control
Supervisor II-Aviation
Supervisor II-Events
Supervisor II-Field Services
Supervisor II-Fleet Services
Supervisor II-Heavy Equipment
Supervisor II-Office
Supervisor II-Recreation
Supervisor II-Security
Supervisor II-Warehousing
Supervisor II-Water
Supervisor II-Park Maint
Supervisor II-Solid Waste
Supervisor II-Climate Control
Supervisor II-Electrical
Supervisor II-Water Meters
Supervisor II-Plumbing
Supervisor II-Strategic CS
Supervisor II-Aquatics
Supervisor II-Veterinary
Supervisor III
Supervisor III-Auto Services
Supervisor III-Communications
Supervisor III-Constr Insp
Supervisor III-Environment
Supervisor III-Events
Supervisor III-Facilities
Supervisor III-Field Services
Supervisor III-Heavy Equipment
Supervisor III-Housing
Supervisor III-Office
H
H
H
I
H
H
H
H
I
H
H
I
I
I
H
I
J
I
I
I
I
I
I
H
I
I
I
I
I
H
H
H
I
I
I
J
I
I
I
I
I
J
I
30025-28
30025-30
30025-34
30025-37
30025-44
30025-51
30025-52
30025-53
30026-00
30026-12
30026-14
30026-15
30026-22
30026-24
30026-29
30026-30
30026-33
30026-34
30026-49
30307-00
30308-00
30801-00
30802-00
32207-00
32208-00
32209-00
32220-00
32401-00
32402-00
32403-00
32405-00
32406-00
34006-00
35545-00
35546-00
35547-00
35548-00
36006-00
36010-00
36016-00
36017-00
36023-00
36025-00
Supervisor III-Public Projects
Supervisor III-Recreation
Supervisor III-Water
Supervisor III-Park Maint
Supervisor III-Electronic Tech
Supervisor III-Plumbing
Supervisor III-Food Protection
Supervisor III-Process Control
Supervisor IV
Supervisor IV -Environment
Supervisor IV -Events
Supervisor IV -Facilities
Supervisor IV -Maintenance
Supervisor IV -Nutrition
Supervisor IV -Public Safety
Supervisor IV -Recreation
Supervisor IV -Warehousing
Supervisor IV -Water
Supervisor IV -Water Meters
Senior Real Estate Specialist
Chief Real Estate Specialist
Display Artist
Graphics Designer
Senior Legal Secretary
Council Secretary
Executive Secretary
Senior Executive Secretary
Office Assistant
Office Assistant II
Senior Office Assistant
Administrative Specialist
Administrative Specialist II
Senior Payroll Specialist
Geographic Information System Analyst
Geographic Information System Analyst II
Geographic Information System Analyst III
Senior Geographic Information System Analyst
Network Analyst II
Network Analyst
Computer Operator
Computer Operator II
Traffic Management Specialist
Computer Operations Analyst
I
I
I
I
I
I
I
I
J
J
I
I
I
J
I
I
I
K
I
I
J
E
J
E
E
E
H
B
C
E
E
H
H
H
I
I
J
J
I
D
E
H
J
36100-00
36402-00
36402-09
36402-67
36403-00
36404-00
36407-00
36409-00
36409-29
36415-00
36417-00
36502-00
36504-00
36504-53
36505-00
36505-07
36505-67
36507-00
36507-09
36508-53
36509-00
36510-00
36524-00
36525-00
36526-00
36526-00
36526-29
36526-70
36528-00
36531-00
36533-00
36534-00
36538-00
36540-00
36609-00
38009-00
38015-00
40004-00
40006-00
40009-00
40019-00
40032-00
40035-00
Departmental Technology Analyst
Information Technology Engineer
Information Technology Engineer-Computer Ops
Information Technology Engineer-Public Information
Senior Information Technology Engineer
Information Technology Architect
Information Technology Business Analyst III
Information Technology Business Analyst V
Information Technology Business Analyst V-Public Safety
Information Technology Project Manager I
Information Technology Project Manager III
Information Technology Analyst
Senior Information Technology Analyst
Senior Information Technology Analyst-Process Control
Information Technology Manager
Information Technology Manager-Communications
Information Technology Manager-Public Information
Senior Systems Programmer
Senior Systems Programmer-Computer Ops
Systems Programmer-Process Control
Web Designer
W…
Purchase answer to see full
attachment