Course Description
Learning Objectives
Introduction , 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 , Conclusion
Pre-Requisites
Excel 365 Introduction and Intermediate courses or equivalent experience.
Description
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.
-
Introduction 00:01:00
-
Lesson 01-Customizing the Ribbon 00:09:00
-
Lesson 02-Customizing the Quick Access Toolbar 00:06:00
-
Lesson 03-Customizing the General and Formula Options 00:04:00
-
Lesson 04-Customizing the AutoCorrect Options 00:03:00
-
Lesson 05-Customizing the Save Defaults 00:04:00
-
Lesson 06-Customizing Advanced Excel Options 00:03:00
-
Lesson 01-Working with the Most Common Logical Functions 00:03:00
-
Lesson 02-Understanding IF Functions 00:06:00
-
Lesson 03-Evaluating Data with the AND Function 00:05:00
-
Lesson 04-Evaluating Data with the OR Function 00:03:00
-
Lesson 05-Creating a Nested IF Function 00:05:00
-
Lesson 06-Using the IFS Function 00:03:00
-
Lesson 07-Summarizing Data with SUMIF 00:03:00
-
Lesson 08-Summarizing Data with AVERAGEIF 00:02:00
-
Lesson 09-Summarizing Data with COUNTIF 00:02:00
-
Lesson 10-Summarizing Data with MAXIFS and MINIFS 00:03:00
-
Lesson 11-Using the IFERROR Function 00:03:00
-
Lesson 01-What are Lookup Functions 00:03:00
-
Lesson 02-Using VLOOKUP 00:08:00
-
Lesson 03-Using HLOOKUP 00:03:00
-
Lesson 04-Using VLOOKUP with TRUE 00:04:00
-
Lesson 05-Using HLOOKUP with TRUE 00:02:00
-
Lesson 06-Using the Index Function 00:03:00
-
Lesson 07-Using the MATCH Function 00:03:00
-
Lesson 08-Combining INDEX and MATCH 00:04:00
-
Lesson 09-Comparing Two Lists with VLOOKUP 00:02:00
-
Lesson 10-Comparing Two Lists with VLOOKUP and ISNA 00:04:00
-
Lesson 11-Using the New XLookup Function-v2 00:07:00
-
Lesson 12-Using Dynamic Array functions-v2 00:06:00
-
Lesson 13-Other New Functions-v2 00:04:00
-
Lesson 01-What are Text Functions 00:01:00
-
Lesson 02-Using CONCAT, CONCATENATE, AND TEXTJOIN 00:04:00
-
Lesson 03-Using Text to Columns 00:02:00
-
Lesson 04-Using LEFT, RIGHT, and MID Functions 00:03:00
-
Lesson 05-Using UPPER, LOWER, and PROPER Functions 00:02:00
-
Lesson 06-Using the Len Function 00:03:00
-
Lesson 07-Using the Trim Function 00:01:00
-
Lesson 08-Using the SUBSTITUTE Function 00:02:00
-
Lesson 01-What are Date and Time Functions 00:02:00
-
Lesson 02-Using TODAY, NOW, and DAY Functions 00:03:00
-
Lesson 03-Using NETWORKDAYS and YEARFRAC Functions 00:03:00
-
Lesson 01-Showing Formulas 00:03:00
-
Lesson 02-Tracing Precedents and Dependents 00:04:00
-
Lesson 03-Adding a Watch Window 00:04:00
-
Lesson 04-Error Checking 00:04:00
-
Lesson 01-Using the Scenario Manager 00:07:00
-
Lesson 02-Using Goal Seek 00:03:00
-
Lesson 03-Analyzing with Data Tables 00:04:00
-
Lesson 01-Understanding Protection 00:02:00
-
Lesson 02-Encrypting Files with Passwords 00:05:00
-
Lesson 03-Allowing Specific Worksheet Changes 00:02:00
-
Lesson 04-Adding Protection to Selected Cells 00:03:00
-
Lesson 05-Additional Protection Features 00:03:00
-
Lesson 01-What are Macros 00:03:00
-
Lesson 02-Displaying the Developer Tab 00:03:00
-
Lesson 03-Creating a Basic Formatting Macro 00:05:00
-
Lesson 04-Assigning a Macro to a Button 00:03:00
-
Lesson 05-Creating Complex Macros 00:04:00
-
Lesson 06-Viewing and Editing the VBA Code 00:04:00
-
Lesson 07-Adding a Macro to the Quick Access Toolbar 00:03:00
-
Lesson 01-What are Form Controls 00:02:00
-
Lesson 02-Adding a Spin Button and Check Boxes 00:04:00
-
Lesson 03-Adding a Combo Box 00:07:00
-
Lesson 01-What is Data Validation 00:02:00
-
Lesson 02-Restricting Data Entry to the Whole Numbers 00:02:00
-
Lesson 03-Restricting Data Entry to a List 00:04:00
-
Lesson 04-Restricting Data Entry to a Date 00:02:00
-
Lesson 05-Restricting Data Entry to Specific Text Lengths 00:01:00
-
Lesson 06-Composing Input Messages 00:02:00
-
Lesson 07-Composing Error Alerts 00:03:00
-
Lesson 08-Finding Invalid Data 00:02:00
-
Lesson 09-Editing and Deleting Validation Rules 00:01:00
-
Lesson 01-Working with Comments-v2 00:03:00
-
Lesson 02-Printing Comments and Errors 00:02:00
-
Lesson 03-Sharing a Workbook 00:04:00
-
Lesson 04-Co-Authoring in Excel 00:02:00
-
Lesson 05-Tracking Changes in a Workbook 00:03:00
-
Lesson 06-Working with Versions 00:03:00
-
Lesson 07-Sharing files Via Email-v2 00:03:00
-
Lesson 01-Importing a Text File 00:04:00
-
Lesson 02-Exporting Data to a Text File 00:01:00
-
Recap 00:02:00
-
Resource – Excel 365 Advanced
Course Reviews
No Reviews found for this course.
or
Get all courses for only £492000+ Globally Recognised Premium Courses
This course includes:
