Please be informed that this site uses cookies.

+971 56 800 6744

+971 2 622 2605

Course Description

Having Working knowledge of MS Excel has become imperative for every working professional as the use of MS Excel across organization is domain and department agnostic. A strong knowledge of MS Excel would help professionals to success in their respective career paths. This advanced Excel training course covers how to work more efficiently with workbooks and worksheets, enhance the appearance of data and worksheets, perform advanced analysis using functions, statistics, macros and validation at advanced level and formulas to pull and analyze data.

Course Objectives

Upon the successful completion of this course, each participant will be able to:

  • ​Work faster in Excel by learning Shortcuts
  • Understand and use formula cell referencing to create more complex formulas
  • like logical test with IF Condition
  • Format data on the based on Criteria’s with Conditional Formatting
  • Create data validation (Drop downs)
  • Work with Pivot Tables and Charts
  • Explore, Analyze, Visualize and Manipulate Data
  • Write Formulas like SUMIF & COUNTIF

Who Should Attend?

This course is intended for current Microsoft Excel users who wish to extend their knowledge and skills beyond building simple workbooks and need to improve their understanding and use of Microsoft Excel as an important tool to produce more effective and efficient work.

Course Agenda

Registration​

Welcome & Introduction

Pre-Test

Excel Best Practices & Tips

  • Create Worksheets with the Future in Mind
  • Keep Styling Consistent
  • Regular Shortcuts

Understanding Excel References

  • Relative and Absolute References
  • Mixed References within Formulas
  • Named Cell Ranges and Functions
  • Removing a Named Range
  • Structural Data and Structural References

Auditing Formulas

  • Tracing Precedent Cells
  • Tracing Dependent Cells
  • Identifying Cells with Missing Dependents
  • Showing All Formulas in a Worksheet
  • Evaluate Formula

Time & Date Functions

  • Get Current Date and Time
  • Convert Dates to / from Text
  • Retrieve Dates in Excel
  • Calculate Date Difference
  • Calculate Service
  • Calculate Age in Year, Month, Days
  • Calculate Workdays

Logical Functions

  • Evaluate Multiple Logical Conditions
  • AND & OR Logical Function
  • IF with AND & OR Logical Functions
  • Conditional Sum, Conditional Average and Conditional Count
  • Highest and Lowest with Conditions

Lookup Functions

  • Vertical lookup in columns
  • Horizontal Lookup in Rows
  • Applying XLOOKUP Function to Lookup Value from a Range
  • Filter Values Using Function

Data Validation

  • Whole Number Validation
  • Decimal Number Validation
  • List Validation
  • Date Validation
  • Text Length Validation
  • Validation Input Message and Error Alert
  • Removing Data Validation

Advanced Conditional Formatting

  • Applying Default Rules
  • Modifying Applied Rules
  • Creating and Applying Custom Rules

Data Visualization (Diagrams and Charts)

  • Data Visualization Using Conditional Formatting
  • Chartless Visualization
  • Creating Basic Charts
  • Changing the Chart Type for a Data Series
  • Adding a Data Series to a Chart
  • Removing a Data Series from a Chart
  • Modifying the Chart Title, Legend & Data Labels
  • Chart Axis Scales
  • Inserting Images into Chart Columns
  • Formatting the Chart Area
  • Combined Line and Column Chart
  • Adding a Secondary Axis
  • Stacked Comparison

Import Data into Excel (Power Query)

  • Importing Your Data with Power Query
  • The Query Editor
  • Preview Data
  • Applied Steps
  • Data Loading Options

Pivot Tables

  • Creating Pivot Table from Single File
  • Creating Pivot Table from Multiple Files
  • Use the Field List to Arrange Fields in a Pivot Table
  • Filtering and Sorting Pivot Tables
  • Grouping Data
  • Use Slicers to Filter PivotTable Data
  • Create a PivotTable Timeline to Filter Dates
  • Change the Source Data for a Pivot Table
  • Calculate Values in a Pivot Table
  • Create Pivot Charts

Macro Basics

  • Record Macros
  • Assigning a Macro to a Button on the Quick Access Toolbar
  • Deleting Macros

Post Test

End of the Course

Assessment Methodology

All courses conducted by EdTech will begin with a Pre-evaluation and end with a Post-evaluation. The instructor will evaluate the knowledge and skills of the participants according to the feedback given by participants. This will help to recognize the benefits and the level of knowledge gained by participants through the course.

Training Methodology

Facilitated by a highly qualified specialist, who has extensive knowledge and experience; this program will be conducted using extensively interactive methods, encouraging participants to share their own experiences and apply the program material to real-life work situations in order to stimulate group discussions and improve the efficiency of the subject coverage.

Percentages of the total course hour classification are:

  • ​40% Theoretical lectures, Concepts and approach
  • 20% Motivation to develop individual skill and Techniques
  • 20% Case Studies and Practical Exercises
  • 20% Topic General Discussions and interaction

Course Manual

Participants will be provided with comprehensive presentation material as reference manual. This presentation material is a compilation of core valuable information, references, presentation methods and inspiring reading which will be used as a part of the material guide.

Course Certificate

At the completion of the course, all participants who successfully accomplished the required contact hours will receive an EdTech Training Participation Certificate as a testimony to their commitment to professional development and further education.

Why Edtech ?

  • Industry Experienced; Internationally Qualified Trainers
  • Hands-on Practical Sessions & Assignments
  • Intensive Study materials
  • Flexible Schedules
  • Realistic training methodology
  • High-Quality Training in Affordable Course Fees
  • Achievement Certificate, as approved by the Ministry of Education (Abu Dhabi Center for Technical and Vocational Education Training - ACTVET), HABC, AWS, IAOSHE, SHRM, etc.