Get Access to Entire Course Library of 2000+ Courses at £99 Get Now
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 | ||
| Final Exam | |||
| Final Exam – Excel 365 Advanced | 00:20: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.
