Provide the necessary knowledge and skills to deal with numbers, formulas, functions and graphs, using Microsoft Excel, so that you can take advantage of the capabilities and features of the program to perform your business and daily tasks efficiently and effectively

Course Objectives:

At the end of this course, the participants should be able to

• Master the conditional coordination skill
• Master the data validation skill and data filtering
• Deal professionally with logical, Statistical and search and functions
• Use references options
• Design charts
• Take advantage of the tables
• Use Macro
• Protect data

Target Audience:

Individuals who need to use advanced functions in Microsoft Excel, use automated tools (Macro) and protect data

• Use advanced functions (logical and lookup)
• Use advanced filtering, sorting, and conditional formatting
• Work with data validation tools
• Use cell references in a formulas
• Create tables and charts
• Working with Macros
• Protect data

Course Methodology:

This training course will be held in a computer lab where every participant will have his own computer to be able to learn and implement the different skills, where the trainer will perform the skill and the participants will follow by performing the same skill in their computers.

Conditional formatting (advanced formula-based)

• Examples
• Exercises

Data Validation

• Examples
• Exercises

Advanced data sorting and filtering

• Examples
• Advanced filtering

Reference types in the formula

• Create Range
• Relative reference
• Mixed reference
• Absolute reference
• 3D references

Logical functions

• IF function
• IFS function
• IFERROR function
• AND function
• OR function

Lookup functions

• VLOOKUP function
• XLOOKUP function

Charts

• Create a chart at a glance
• Create a stacked vertical bar
• Working with pie charts
• Sparklines

Tables

• Examples
• Exercises
• Rename table name
• Format table
• Create formula in a new column
• Remove duplicate row

Automate processes with macros

• Steps to create a micro
• How to put a button for the micro in the taskbar
• Use relative cell references when recording a micro
• Examples

• Add a worksheet control
• Examples
• Exercises

• Protect sheet
• Protect workbook