• LOGIN
  • No products in the basket.

Login

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.

Course Curriculum

Course Introduction
Introduction 00:01:00
Section 01
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
Section 02
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
Section 03
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
Section 04
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
Section 05
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
Section 06
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
Section 07
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
Section 08
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
Section 09
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
Section 10
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
Section 11
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
Section 12
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
Section 13
Lesson 01-Importing a Text File 00:04:00
Lesson 02-Exporting Data to a Text File 00:01:00
Course Recap
Recap 00:02:00
Additional Materials
Resource – Excel 365 Advanced 00:00:00

FAQs

This course is for anyone who's interested in this topic and wants to learn more about it. This course will also help you gain potential professional skills.

No prior qualifications are needed to take this course.

You can study this course from wherever and whenever you want. You can study at your own pace and from any device. Just log in to your account from any device and start learning!

Yes, there is a test at the end of the course. Once you’ve completed all the modules of the course, you will have to give a multiple-choice test. The questions will be based on the topics of the modules you studied. And of course, you can take the test at any time, from any device and from anywhere you want.

Don’t worry if you fail the test, you can retake it as many times as you want.

You don’t have to wait a minute after your payment has been received, you can begin immediately. You will create your login details during the checkout process and we will also send you an email confirming your login details.

We make the payment process easy for you. You can either use your Visa, MasterCard, American Express, Solo cards or PayPal account to pay for the online course. We use the latest SSL encryption for all transactions, so your order is safe and secure.

After you complete the course, you’ll immediately receive a free printable PDF certificate. Hard Copy certificate is also available, and you can get one for just £9! You may have to wait for 3 to 9 days to get the hard copy certificate.

© iStudy
Select your currency
GBP Pound sterling