Provide the necessary knowledge and skills to deal with and analyze advanced 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 data validation skill and data filtering
• Deal professionally with logical, Statistical, search and reference, text functions
• Use data cleaning tools
• Professional graphic design
• Take advantage of the capabilities of pivot tables and charts
• Create dashboard
• Create interactive worksheets and analyze data using what-if

Target Audience:

Individuals who need to use Pivot Tables and Pivot chars and more advanced functions to analysis a data in Excel.

• Analyze Data Using Advanced Functions in Excel
• Working with Data
• Creating and Working with PivotTables and Pivot charts
• Creating Dashboard
• Using data Analysis Tools

Course Methodology:

This training course will be carried out through the use of best practices and the right combination of engaging and purposeful tools such as:

• Practical, relevant case studies
• Group activities and workshops
• Related role plays
• Experiential learning
• Brainstorming
• Stimulating mental activities

Conditional formatting (Advanced)

• Examples
• Exercises

Data Validation

• Examples
• Exercises
• Create dependent drop-down list

Text to Column

• Examples
• Exercises

Logical functions

• IF function

Statistical functions

• SUMIFS function
• AVERAGEIFS function
• COUNTIFS function

Text functions

• TRIM function
• LEFT function
• RIGHT function
• MID function

Advanced search and reference functions

• VLOOKUP function
• XLOOKUP function

Advanced graphics

• Create a chart at a glance
• Create a stacked vertical bar
• Working with pie charts
• Mixed charts
• Create progress charts toward the goal
• Save charts as templates
• Freezing schemes
• Sparklines

Tables

• Create table
• Exercises
• Rename table name
• Add columns and rows
• Filter data
• Create formula
• Use total row
• Remove duplicate row

Pivot tables and charts

• Examples
• Exercises
• Rename the pivot table
• Some operations on pivot tables
• Combine pivot tables
• Calculated fields and items
• Using Slicer
• Refreshing Data
• Pivot charts

Dashboard

• What is dashboard
• Create dashboard
• Format dashboard

Data analysis using What-if analysis?

• Analyze data with targeting
• Analyze data using scenarios
• Analyze data with Solver