search
About this course

This course will teach students advanced concepts and formulas in Microsoft Excel 365. Students will learn functions such as SUMIF, AVERAGEIF, and COUNTIF, advanced lookup functions, and complex logical and text functions. Additionally, students will experiment with auditing formulas and error checking, use the What-If Analysis tools, learn the options for worksheet and workbook protection, review advanced use of PivotTables and PowerPivot add-in, work with Macros, use form controls, and ensure data integrity in their worksheets and workbooks. Students will also learn about Excel's many collaboration features and import and export data to and from their workbooks.

What you will learn?
Advanced concepts and formulas and collaboration features in Excel 365.
Learn functions such as SUMIF, AVERAGEIF, and COUNTIF.
Explore advanced lookup functions, and complex logical and text functions.
Experiment with auditing formulas and error checking.
Use the What-If Analysis tools.
Learn the options for worksheet and workbook protection.
Learn the options for worksheet and workbook protection.
Learn how to ensure data integrity in worksheets and workbooks.
Import and export data to and from workbooks.
schedule
5 hours on-demand video
emoji_events
Certificate of completion
bar_chart
Advanced level
error_outline
    Prerequisites
  • Excel 365 Introduction and Intermediate courses or equivalent experience.
check_circle
No preparation required
lock
1 year access

US$77.03

verified 100% moneyback guarantee
If you cancel within 10 days and you’re not completely satisfied, we’ll give you all your money back. No questions asked.
Training 2 or more people?

Get your team access to 2,543 top CPD On Demand courses anytime, anywhere.

Browse course content
83 Video
|
1 Quiz
|
schedule
5 hours
Guide
Section 1. Introduction-Part1
lock
Introduction
1m 22s
Section 2. Customizing Excel-Part1
lock
Customizing the Ribbon
9m 11s
lock
Customizing the Quick Access Toolbar
5m 54s
lock
Customizing the General and Formula Options
4m 8s
lock
Customizing the AutoCorrect Options
3m 16s
lock
Customizing the Save Defaults
4m 6s
lock
Customizing Advanced Excel Options
3m 29s
Section 3. Analyzing Data with Logical Functions-Part1
lock
Working with the Most Common Logical Functions
2m 58s
lock
Understanding IF Functions
6m 1s
lock
Evaluating Data with the AND Function
5m 0s
lock
Evaluating Data with the OR Function
2m 54s
lock
Creating a Nested IF Function
5m 25s
lock
Using the IFS Function
3m 16s
lock
Summarizing Data with SUMIF
3m 23s
lock
Summarizing Data with AVERAGEIF
1m 59s
lock
Summarizing Data with COUNTIF
1m 53s
lock
Summarizing Data with MAXIFS and MINIFS
2m 37s
lock
Using the IFERROR Function
3m 24s
Section 4. Working with Lookup Functions-Part1
lock
What are Lookup Functions?
2m 50s
lock
Using VLOOKUP
8m 12s
lock
Using HLOOKUP
2m 47s
lock
Using VLOOKUP with TRUE
3m 45s
lock
Using HLOOKUP with TRUE
1m 39s
lock
Using the INDEX Function
2m 37s
lock
Using the MATCH Function
2m 33s
lock
Combining INDEX and MATCH
4m 9s
lock
Comparing Two Lists with VLOOKUP
2m 0s
lock
Comparing Two Lists with VLOOKUP and ISNA
4m 12s
lock
Using the New XLookup Function
7m 14s
lock
Using Dynamic Array Functions
6m 20s
lock
Other New Functions
4m 3s
Section 5. Using Text Functions-Part1
lock
What are Text Functions?
1m 27s
lock
Using CONCAT, CONCATENATE, AND TEXTJOIN
3m 46s
lock
Using Text to Columns
2m 26s
lock
Using LEFT, RIGHT, and MID Functions
2m 57s
lock
Using UPPER, LOWER, and PROPER Functions
2m 8s
lock
Using the LEN Function
2m 58s
lock
Using the TRIM Function
1m 18s
lock
Using the SUBSTITUTE Function
2m 0s
Section 6. Working with Date and Time Functions-Part1
lock
What are Date and Time Functions?
2m 25s
lock
Using TODAY, NOW, and DAY Functions
3m 24s
lock
Using NETWORKDAYS and YEARFRAC Functions
3m 17s
Section 7. Formula Auditing-Part1
lock
Showing Formulas
2m 34s
lock
Tracing Precedents and Dependents
4m 20s
lock
Adding a Watch Window
3m 35s
lock
Error Checking
4m 5s
Section 8. What-If Analysis-Part1
lock
Using the Scenario Manager
7m 22s
lock
Using Goal Seek
2m 33s
lock
Analyzing with Data Tables
3m 43s
Section 9. Worksheet and Workbook Protection-Part1
lock
Understanding Protection
1m 50s
lock
Encrypting Files with Passwords
5m 1s
lock
Allowing Specific Worksheet Changes
2m 17s
lock
Adding Protection to Selected Cells
2m 34s
lock
Additional Protection Features
3m 4s
Section 10. Automating with Macros-Part1
lock
What are Macros?
2m 52s
lock
Displaying the Developer Tab
2m 56s
lock
Creating a Basic Formatting Macro
5m 14s
lock
Assigning a Macro to a Button
2m 34s
lock
Creating Complex Macros
3m 49s
lock
Viewing and Editing the VBA Code
4m 5s
lock
Adding a Macro to the Quick Access Toolbar
2m 55s
Section 11. Working with Form Controls-Part1
lock
What are Form Controls?
1m 52s
lock
Adding a Spin Button and Check Boxes
4m 23s
lock
Adding a Combo Box
7m 17s
Section 12. Ensuring Data Integrity-Part1
lock
What is Data Validation?
1m 51s
lock
Restricting Data Entry to Whole Numbers
2m 14s
lock
Restricting Data Entry to a List
3m 40s
lock
Restricting Data Entry to a Date
1m 47s
lock
Restricting Data Entry to Specific Text Lengths
1m 29s
lock
Composing Input Messages
2m 25s
lock
Composing Error Alerts
2m 40s
lock
Finding Invalid Data
1m 33s
lock
Editing and Deleting Validation Rules
1m 22s
Section 13. Collaborating in Excel-Part1
lock
Working with Comments
3m 5s
lock
Printing Comments and Errors
2m 11s
lock
Sharing a Workbook
4m 1s
lock
Co-Authoring in Excel
2m 24s
lock
Tracking Changes in a Workbook
2m 32s
lock
Working with Versions
3m 4s
lock
Sharing Files via Email
2m 43s
Section 14. Importing and Exporting Data to a Text File-Part1
lock
Importing a Text File
4m 1s
lock
Exporting Data to a Text File
1m 26s
Section 15. Conclusion-Part1
lock
Course Recap
1m 34s
Review Questions
25 questions
Learn from the best
Intellezy Trainers / INSTRUCTOR
Offering eLearning and Instructor-led Training at your fingertips!

Intellezy collaborates with organizations to help implement and adopt technology to its maximum potential. From our change management consulting to our learning and development services, Intellezy uses both culture and education to drive the ROI of any organization. Our online videos and quick reference guides are designed to educate and empower individuals, right when they need it. In today’s rapidly evolving workplace, it is imperative to make sure you have the skills and expertise required to succeed. Our library, recognized by top influencers such as eLearning Journal and The Craig Weiss Group, provides dynamic and task-focused videos right at your fingertips, right when you need them.