Programme Objective

This intensive full day workshop focuses on the technical Excel functions required in everyday work scenarios and equips you with the essential skills to increase your productivity in your job. You will learn how to clean up raw data for analysis using Flash Fill, use Quick Analysis to analyze data quickly, use advanced formulas and functions to perform calculations, highlight important values in reports using color, set up worksheet on auto-pilot, re-use the user data entry forms in other applications, protect sensitive data and summarize large amounts of data with PivotTable & PivotChart reports.

Each training topic drills down into the details, giving you tons of tips and tricks to better-use your time and to avoid frustration. After the course, you will stop using Excel just as a calculator and start using Excel as a powerful analysis tool.

Participants are strongly encouraged to bring along their laptops with Excel installed (preferably Office 365—app version, at least Excel 2013).

Programme Outline

 

• Managing Data
  • Use Auto Fill to create data series.
  • Use Flash Fill to clean up employee data and payroll data, combine first name, last name with prefix into a single column from three different columns, split employees’ names and addresses into multiple columns and format telephone numbers by adding brackets or dashes.
  • Use Quick Analysis to calculate running total and % of total quickly

 

• Organizing Data
  • Use Excel Table to organize, manage and analyze related data in a systematic manner, find number of employees joined on particular date/month in specific department and country, create autofill down formulas, filter and sort data based on color and perform custom sorting based on a custom list.
  • Use Conditional Formatting to highlight above and below average performers, highlight employees who celebrate their birthday in each month, highlight the top 10 performers in the company and visualize important values as in-cell bar chart.

 

• Analyzing Data
  • Use PivotTable and PivotChart to view summarized salary for all different departments in different countries and regions, find the total salary and total expenses in different regions and different departments, compare employee details from different systems, find the employees total salary for a department or designation in a month.
  • Use Sparklines to show data trend

 

• Performing Calculations
  • Use Date & Time functions to calculate number of years in service for each employee, calculate the number of working days between two dates, find the weekday of a given date, find out the week number of a given date and etc.
  • Use advanced formulas and functions to calculate the age of each employee, calculate the tax-deductible amount based on salary ranges, calculate bonusbased on overall performance of employee, calculate sales representatives’ commissions as well as calculate overtime and overtime pay.
  • Use IF, AND, SUMIF, COUNTIF, AVERAGEIF functions to find the total number of employees in the company in different departments, count number of employees by designation and the average salary for each department.
  • Use VLOOKUP, HLOOKUP functions to extract data item from a data list

 

• Restricting Data Entry
  • Use Data Validation to validate the employment date upon data entry, set up the status of employees, create drop-down lists for selection and display interactive messages.

 

• Implementing Protection
  • Use Protect Sheet and Protect Workbook commands to protect employees’ information and to prevent unwanted changes.

Training Methodology

Lecture style, with hands-on exercises

Closing Date for Registration
1 week before programme or until full enrolment.
 

Intended For

This workshop is for all existing Microsoft Excel users especially admin, Finance and HR Professionals who intend to perform complex calculations with ease, speed and a great level of confidence.

Please note: This course is not for beginners who seldom use Excel at work, don't know how to draw chart and use basic functions.

Competency Mapping

Category 5 = 7.00 Hours

Programme Facilitator(s)