Microsoft Excel For Advanced Users

Why Choose Garranto Academy for Your Advanced Excel Training?

Garranto Academy offers industry-aligned training with real-world exercises, expert instructors, and job-ready practical lessons.

Course Overview:

This advanced Microsoft Excel 365 course equips participants with the skills to work confidently with complex functions, data tools, and automation features. Learners will master conditional functions such as SUMIF, AVERAGEIF, and COUNTIF, along with advanced lookup, logical, and text functions for deeper data analysis. The course covers formula auditing, error checking, and powerful What-If Analysis tools to support informed decision-making. Participants will explore worksheet and workbook protection options, enhance data management through PivotTables and the PowerPivot add-in, and gain hands-on experience with Macros and form controls. The training also emphasizes data integrity, collaboration features, and importing and exporting data, preparing learners to handle advanced Excel tasks efficiently and professionally.

What You'll Learn in Our Advanced Excel Certification Course?

Course Objectives:

Upon successful completion of this course, learners will be able to:

• Customizing Excel

• Analyzing Data with Logical Functions

• Working with Lookup Functions

• Using Text Functions

• Working with Date and Time Functions

• Formula Auditing

• What-If Analysis

• Worksheet and Workbook Protection

• Automating with Macros

• Working with Form Controls

• Ensuring Data Integrity

• Collaborating in Excel

• Importing and Exporting Data to a Text File

Prerequisites

• Excel 365 Introduction course or equivalent

• Excel 365 Intermediate course completion

• Prior experience with core Excel functions

Course Outlines:

Module 1: Introduction

• Introduction

Module 2: Customizing Excel

• Customizing the Ribbon

• Customizing the Quick Access Toolbar

• Customizing the General and Formula Options

• Customizing the AutoCorrect Options

• Customizing the Save Defaults

• Customizing Advanced Excel Options

Module 3: Analyzing Data with Logical Functions

• Working with the Most Common Logical Functions

• Understanding IF Functions

• Evaluating Data with the AND Function

• Evaluating Data with the OR Function

• Creating a Nested IF Function

• Using the IFS Function

• Summarizing Data with SUMIF

• Summarizing Data with AVERAGEIF

• Summarizing Data with COUNTIF

• Summarizing Data with MAXIFS and MINIFS

• Using the IFERROR Function

Module 4: Working with Lookup Functions

• What are Lookup Functions?

• Using VLOOKUP

• Using HLOOKUP

• Using VLOOKUP with TRUE

• Using HLOOKUP with TRUE

• Using the Index Function

• Using the MATCH Function

• Combining INDEX and MATCH

• Comparing Two Lists with VLOOKUP

• Comparing Two Lists with VLOOKUP and ISNA

Module 5: Using Text Functions

• What are Text Functions?

• Using CONCAT, CONCATENATE, AND TEXTJOIN

• Using Text to Columns

• Using LEFT, RIGHT, and MID Functions

• Using UPPER, LOWER, and PROPER Functions

• Using the LEN Function

• Using the TRIM Function

• Using the SUBSTITUTE Function

Module 6: Working with Date and Time Functions

• What are Date and Time Functions?

• Using TODAY, NOW, and DAY Functions

• Using NETWORKDAYS and YEARFRAC Functions

Module 7: Formula Auditing

• Showing Formulas

• Tracing Precedents and Dependents

• Adding a Watch Window

• Error Checking

Module 8: What-If Analysis

• Using the Scenario Manager

• Using Goal Seek

• Analyzing with Data Tables

Module 9: Worksheet and Workbook Protection

• Understanding Protection

• Encrypting Files with Passwords

• Allowing Specific Worksheet Changes

• Adding Protection to Selected Cells

• Additional Protection Features

Module 10: Automating with Macros

• What are Macros?

• Displaying the Developer Tab

• Creating a Basic Formatting Macro

• Assigning a Macro to a Button

• Creating Complex Macros

• Viewing and Editing the VBA Code

• Adding a Macro to the Quick Access Toolbar

Module 11: Working with Form Controls

• What are Form Controls?

• Adding a Spin Button and Check Boxes

• Adding a Combo Box

Module 12: Ensuring Data Integrity

• What is Data Validation?

• Restricting Data Entry to Whole Numbers

• Restricting Data Entry to a List

• Restricting Data Entry to a Date

• Restricting Data Entry to Specific Text Lengths

• Composing Input Messages

• Composing Error Alerts

• Finding Invalid Data

• Editing and Deleting Validation Rules

Module 13: Collaborating in Excel

• Working with Comments

• Printing Comments and Errors

• Sharing a Workbook

• Co-Authoring in Excel

• Tracking Changes in a Workbook

• Working with Versions

• Sharing Files via Email

Module 14: Importing and Exporting Data to a Text File

• Importing a Text File

• Exporting Data to a Text File

Module 15: Conclusion

• Course Recap

Course Outcomes:

Upon completing the "Microsoft Excel For Advanced Users" course, participants will:

• Apply advanced logical, lookup, text, and date formulas

• Solve complex business problems using advanced Excel functions

• Analyze data with What-If Analysis and auditing tools

• Ensure accuracy using data validation and error-checking techniques

• Automate tasks by creating and running Excel Macros

• Build interactive sheets using Form Controls and protection

• Collaborate effectively with sharing, comments, and data import/export

Key Benefits of Becoming an Advanced Microsoft Excel User

Mastering advanced Excel skills empowers you to analyze complex data, automate workflows, and make smarter business decisions faster.

How Advanced Excel Skills Can Transform Your Productivity

Advanced Excel techniques streamline your daily tasks, reduce manual effort, and unlock deeper insights through analytics and automation.