Outlines

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 : Pivot Table

• Understanding Pivot Table requirements

• Creating A Pivot Table

• Changing the Layout of the Pivot Table

• Using Filters and Report Filters

• Grouping Data in Pivot table

• Calculation in Pivot Table

• Using Slicers & Timeline

Module 2 : IF Function

• IF Logic

• IFERROR vs IFNA

• Using IF in VLOOKUP

• Using IF in a formula

• Nested IF with AND Function

• Nested IF with OR Function

• Complex IF formulas

Module 3 : Data Validation

• Setting Data Validation Rules

• Message & Error Alert

• Using INDIRECT Function with Data Validation

Module 4 : What If Analysis Tools

• Finding a solution with Goal Seek

• Viewing multiple scenarios with Scenario Manager

• Using Data Tables

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

Module 7 : Lookup Function

• VLookup vs INDEX & MATCH

• 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