Outlines
MICROSOFT EXCEL 2010/2013/ 2016/2019/365 : ADVANCED
Course Objectives
The course should teach learners advanced formatting techniques, such as using custom styles, conditional formatting, and themes to make their spreadsheets more visually appealing and easier to read.
Participants will learn how to use advanced functions and formulas, such as IF functions, date and text functions to perform complex calculations and analysis. Advanced data analysis techniques, such as creating pivot tables and pivot charts, using data validation and sorting, and creating scenarios and goal seeking will also be covered.
The course also teaches how to collaborate with others on spreadsheets, including protecting and sharing workbooks, tracking changes and comments, and merging changes from multiple users.
Course Outline
Module 1 : Logical Functions
Function : IF , AND, OR
Nested IF & AND Function
Nested IF & OR Function
Module 2 : Pivot Tables
Creating A Pivot Table
Changing the Layout of the Pivot Table
Using Filters and Report Filters
Grouping Data in Pivot table
Formulas in Pivot Table? Yes you can!
Using Slicers
Module 3 : Data Validation
Setting Data Validation Rules
Message & Error Alert
INDIRECT Function with Data Validation
Module 4 : What If Analysis Tools
Finding a solution with Goal Seek
Viewing multiple scenarios with Data Tables
Finding the Optimum Solution with Solver
Module 5 : Working With Data From Multiple Worksheets & Workbooks
Consolidating Data from Multiple Sources
Consolidating With Formulas
Linking Cells with other Sheets & Workbooks
Repairing Broken Links
Module 6 : Text Function
REPLACE
SUBSTITUTE
LEN
TRIM
Using the above functions correctly
Module 7 :Data Function
MATCH, INDEX, CHOOSE
Creating An Array Formula
Module 8 : Auditing Formulas in Worksheets
Tracing data in cells & Trouble Shooting Errors in Formulas
Setting Up Watch and Evaluating Formulas
Module 9 : Passwords & Security
Adding Open & Modify Password
Protecting cells with password
Protecting Worksheets
Hiding Formula
