Main MenuMain Menu
Course Code: IT 149
400 Course Visits
Mastering MS Excel for Reporting
Course Sector:
Information Technology
Course Dates and Locations
Choose a date and location to book your seat
No.
Date
Days
Location
Fees
Enrollment
01
5 Days
London, UK $4,950
02
5 Days
Salalah, Oman $4,250
03
5 Days
Abu Dhabi, UAE $4,250
Introduction
Training course introducion / brief
Excel is a tool that enable most professionals to be more productive and efficient in managing data. While there are other database systems like Oracle (ORCL) and SAP (SAP) being used in HR related functions, exporting that data into Excel allows users to discover trends, summarize expenses and hours by pay period, month, or year, and better understand how the workforce is spread out by function or pay level. It allows HR professionals to create a spreadsheet full of employee data and analyze financial implications for cost planning.
This training course is designed to provide participants with techniques, applications and basic knowledge on using MS Excel for reporting and preparing HR related documents such as employee compensations, salary, leave, benefits and the like.
Course Objectives
At the end of the training course, participants will be able to
- Gain a comprehensive understanding of MS Excel techniques and applications used in reporting particularly in HR related tasks and documents.
- Create special flash and management reports by linking-up Excel with Access, web, text, SQL, and other databases
- Use pivot tables and pivot charts to prepare HR report, analysis and reconciliation most efficiently.
- Repeat tasks and generate reports efficiently by recording, running and editing macros
- Acquire numerous tips and tricks that will improve working efficiency
Course Audience
Who is this course for, and can benefit the most
- Cloud Computing Engineer
- Computer Network Specialist
- Computer Support Specialist
- Database Administrator
- Information Technology Analyst
- Information Technology Leadership
- Information Security Specialist
- Software/Application Developer
- Web Developer
- Technology sales consultant
Course Outline
The course aims and learning outcomes
DATA MASSAGING: TOOLS AND TECHNIQUES
- Consolidating data from separate files and sheets
- Advanced data validation using lists, dates and custom validation
- Cell management tools: left, right, mid, concatenate, value
- Naming, editing, and managing cells and ranges
- Subtotal, Sumif, Sumifs, Sumproduct, Count, Countif, Countifs
- Looking-up data, texts, and values using Vlookup
- The incredible table-tools technique
- Slicing dates into day names, weeks, week numbers, month names, years and quarters
- Text to columns and dynamic trimming using Trim, Len
- Managing texts and numbers using replace, find, and substitute
- Text change functions
- REPORTING, ANALYSIS AND RECONCILIATIONS USING PIVOT TABLES
- The 20 must learn rules • Creating and copying pivot tables
- number formatting techniques
- Designing report layout
- Sorting in ascending, descending and more sort options
- Filtering labels and values
- Expanding and collapsing reports
- Summarize data by sum, average, minimum, maximum, count
- Show values as % of total and % of
- Pivot table options
- Drill down option
- Showing report filter pages
- Inserting formulas
- Date analysis
- Creating pivot charts
- Dynamic chart labeling
- Mastering the slicer
- Linking pivot tables and pivot graphs with PowerPoint
- Conditional formatting with pivot tables
- Designing reports using GetPivotData
- HR REPORTS
- Listing reports with custom views
- Preparing reports using pivot tables
- Consolidation of data
CELLS AND NAME RANGES
- Creating name ranges for data
- Using name ranges for values calculations
- Creating names from selection command
- Creating a 3-D formula
- Updating name ranges
- Editing name ranges
- HR DATA FORMATTING
- Formatting numbers
- Formatting dates
- Conditional formatting
- Avoiding duplication of employee records
- Data bars for performance appraisal
- Icon sets for job level
- Color scale for employees’ grades
- Greater or less than for earnings
- Based on a formula
- Setting warnings on residency expiry date
- AUTOMATE HR REPETITIVE TASKS
- Creating macros for HR reports
- Using macros for monthly reports
- Saving macros in personnel macros workbook
- Assigning macros to quick access toolbar
- DATA CALCULATIONS
- Date and time functions for attendance
- Working days
- Years of service
- LOGICAL AND STATISTICAL FUNCTIONS
- Employee pay increase and bonus (paste special)
- Implement HR policies
- Statistical functions for HR data
- Sumif / sumifs / count / countif / countifs / subtotal
- Average / averageif / averageifs
- LOOKUP FUNCTIONS
- Vlookup and hlookup
- Approximate and exact match
- Index and match functions
CREATING MASTERS WITH DATA VALIDATION
- Employee status, locations and grades
- Employee designations, nationalities and departments
- Date and number validation
- Custom validation
- LEAVE RECORDS AND CALCULATIONS
- Is the employee eligible for leave or bonus? If function
- Accrued leave
- Leave taken records
- Leave balance
- PAYROLL AND BENEFITS
- Payroll sheet and salary slips
- Using attendance data to calculate payroll
- END OF SERVICE CALCULATIONS
- Computing gratuity
- Computing cashable leave
- HR CHARTS AND GRAPHS
- Speedometer chart
- Performance against range
- Spinner
- COMPREHENSIVE EXERCISES
- Calculating gratuity
- Salary increment calculator
- Performance review scores
Providers and Associations
Providing the best training services and benefits to our valued clients
Boost certificate of completion
BOOST's Professional Attendance Certificate “BPAC” is always given to the delegates after completing the training course, and depends on their attendance of the program at a rate of no less than 80%, besides their active participation and engagement during the program sessions.
ENDORSED EDUCATION PROVIDER
Flexible deadlines
Customized dates accordance to your schedule
Shareable Certificate
Earn certificate upon completion
COURSE METHODOLOGY
Our Training programs are implemented by combining the participants' academic knowledge and practical practice (30% theoretical / 70% practical activities).
At The end of the training program, Participants are involved in practical workshop to show their skills in applying what they were trained for. A detailed report is submitted to each participant and the training department in the organization on the results of the participant's performance and the return on training. Our programs focus on exercises, case studies, and individual and group presentations.
Since 2001, we have been pioneering the training field in the middle east, we managed to unlock potential and help individuals, teams and organisations to be the best that they can be with our full and integrated solutions.
Quick Links
Our sister companies in Egypt and United Arab Emirates
MOST TRENDING
And Recommended Training Courses
trending
Emotional Intelligence and Advanced Communication Skills for Leaders
Date: Aug 10, 2025
Location:Salalah, Oman
trending
Happiness To Have and Hold
Date: Nov 24, 2025
Location:Dubai, UAE
trending
The Business Analyst (PBA) - PMI Certified
Date: Dec 15, 2025
Location:Dubai, UAE
We believe in progress for everyone.
We helped more than 10.000 clients over 20 countries on 4 continents in boosting their knowledge, skills and careers.
Boost Training And Consulting © All Copyrights Reserved 2025