Unit 1: Working with TEXT Function Category
Icebreaker: Introductions
Course aim and objectives Training Contract
Scenario 1: You are required to analyse data from an evaluation survey. You need to combine text in order create groups for statistical analysis
• CONCATENATE
• LEFT, RIGHT
• UPPER, LOWER
• LEN
• FIND
• Nested Functions using the Functions listed above
Exercise based on Scenario 1
Unit 2: Working with MATH & TRIG Function Category
Scenario 2: An HR department keeps a data list of the company’s employees’ positions, salaries, sick leaves, and vacations. We need o cut down expenses and monitor the sick leaves in each department
• SUMIF and SUMIFS
• SUBTOTAL
• ROUND
Exercises based on Scenario 2
Unit 3: Working with STATISTICAL Function Category
Scenario 3: You got the evaluations of seminar and you wish to analyse the results based on demographics
• COUNTIF and COUNTIFS
• AVERAGE – MEDIAN
• MAXIF, MINIF
• Variance, Standard Deviation
• T.Test, Z.Test
• Nested functions
Exercise
Unit 4: Advanced Conditional Formatting
• Highlighting top and bottom ranks
• Highlighting based on values
• Converging cells into small charts
• Using Color Scales to view the trend
• Conditional Formatting and Functions
Exercise based on Scenario 3
Unit 5: Conditional Formatting with Functions
Scenario 5a: We are preparing timesheets for a European Project. We need to make sure that no staff costs are claimed on a weekend
Scenario 5b: We are monitoring the KPIs of our employees. We need to analyze their overall performance based on their KPIs
• Highlight dates where you have a weekend
• Highlight those employees with below average KPIs using Exercise based on Scenario 5b
Unit 6: Creating Nested Lists
Scenario 6: We are setting up timesheets to calculate hours spent on a Work Package. You need to have a consistent description of activities, which optimally should be selected from a drop down list in order to perform data analysis at a later stage.
• The Function INDIRECT
• Combining Data Validation with Functions
Exercise based on Scenario 6
Unit 7: Comparing Lists
Working with Scenario 6 & Scenario 7
Scenario 7: You are working in a bank and have exported from the system the customer list of the current and previous month. You wish to isolate the customers who left the bank and the new customers by comparing the 2 lists with VLOOKUP
Unit 7: Comparing Lists
• Using VLOOKUP, MATCH GETPIVOT DATA
• Nested functions with IF and VLOOKUP
• Automating Calculations with IF and formulae Exercise based on Scenario 7
Unit 8: Finding Duplicates
Working with Scenario 7
• Using Conditional Formatting
• Using the Remove Duplicates Tool
• Using function combinations
Exercise based on Scenario 7
Unit 9: Advanced Pivot Tables
1. Creating Dashboards
2. Creating Measures
3. The GETPIVOT DATA Function
Exercise based on Scenario 7
Course Closure
• Recap and conclusions
• Action plan