MS Excel Advanced

Course Code: IT 208

17689 Course Visits

MS Excel Advanced

Course Sector:

Information Technology

Request in house training Call Me Back

Course Dates and Locations

Choose a date and location to book your seat

No.

Date

Days

Location

Fees

Enrollment

No available dates now, will be added soon..

Introduction

Training course introducion / brief

Excel is a tool that enables most professionals to be more productive and efficient in managing data. While there are other database systems like Oracle (ORCL) and SAP (SAP), exporting 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 professionals to create a spreadsheet full of employee data and analyze financial implications for cost planning.

This training Program provides participants with techniques, applications and basic knowledge on using MS Excel for reporting and preparing documents within their work scope.

Gain a comprehensive understanding of MS Excel techniques and applications used in reporting particularly.
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 professional reports, 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.

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, 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

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

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 REPETITIVE TASKS

Creating macros for 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

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

CHARTS AND GRAPHS

Speedometer chart
Performance against range
Spinner

COMPREHENSIVE EXERCISES

Calculating gratuity
Salary increment calculator
Performance review scores

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.

Download Company profile

Download Calendar

Trending Courses

The most bespoke and flexible training courses

Open courses table

Jun

- 05 -
Days

The Risk Management Professional (PMI-RMP Exam Preparation)

Istanbul, Turkey

TRAINING COURSE: MS Excel Advanced