$29.99
Certification

Industry recognized certification enables you to add this credential to your resume upon completion of all courses

Need Custom Training for Your Team?
Get Quote
Call Us

Toll Free (844) 397-3739

Inquire About This Course
Instructor
Intellezy Trainers, Instructor -  Excel 2016 Advanced

Intellezy Trainers

Improving individual or company performance begins with the right training resources. Keep your team’s Microsoft and Adobe skills fresh with our engaging, short-segment videos. Intellezy’s training is delivered by certified instructors and industry experts. Their friendly, engaging style breaks down complex ideas into bite-sized learning nuggets. And, because they never leave the screen, the learner’s attention is held longer. Start watching and find out how valuable 3 minutes can be.

Instructor: Intellezy Trainers

Take Excel to the Next Level with Advanced Formulas and Features.

  • Use advanced IF statements and Lookup fuctions.
  • Use advanced PivotTables and PowerPivot tools.
  • Automate with Macros.

Duration: 2h 6m

Course Description

This course will teach students advanced concepts and formulas in Microsoft Excel 2016. Students will learn functions such as SUMIF, AVERAGEIF, and COUNTIF, advanced lookup functions such as HLOOKUP and VLOOOKP and MATCH and INDEX, 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, as well as how to import and export data to and from their workbooks. Instructor: Ed McCrae Student data files are available under Downloads in the course dashboard.

What am I going to get from this course?

  • Use Advanced IF Statements
  • Use Advanced Lookup Functions
  • Use Complex Logical and Text Functions
  • Auditing Formulas
  • Working with What-If Analysis Tools
  • Protecting Worksheet and Workbook
  • Use Advanced PivotTables and PowerPivot Tools
  • Automate with Macros
  • Work with Form Controls
  • Ensure Data Integrity
  • Collaborate in Excel
  • Import and Export Data to a Text File

Prerequisites and Target Audience

What will students need to know or do before starting this course?

Excel Introduction and Intermediate courses or equivalent experience

Who should take this course? Who should not?

Students who want to expand their Excel knowledge

Curriculum

Module 1: Introduction

Lecture 1 Introduction
Resource 1 Student Data Files

Module 2: Using Advanced IF Statements

08:54
Lecture 2 Summarizing Data with SUMIF
04:27
Lecture 3 Summarizing Data with AVERAGEIF
02:35
Lecture 4 Summarizing Data with COUNTIF
01:52

Module 3: Using Advanced Lookup Functions

20:03
Lecture 5 Using VLOOKUP with TRUE
03:40
Lecture 6 Using HLOOKUP with TRUE
01:22
Lecture 7 Using the INDEX Function
03:09
Lecture 8 Using the MATCH Function
02:14
Lecture 9 Combining INDEX and MATCH
04:09
Lecture 10 Comparing Two Lists with VLOOKUP
01:42
Lecture 11 Comparing Two Lists with VLOOKUP and ISNA
03:47

Module 4: Using Complex Logical and Text Functions

10:40
Lecture 12 Creating a Nested IF Function
02:53
Lecture 13 Using the IFERROR Function
02:15
Lecture 14 Using the LEN Function
02:07
Lecture 15 Using the TRIM Function
01:03
Lecture 16 Using the SUBSTITUTE Function
02:22

Module 5: Formula Auditing

07:45
Lecture 17 Showing Formulas
01:01
Lecture 18 Tracing Precedents and Dependents
02:33
Lecture 19 Adding a Watch Window
02:16
Lecture 20 Error Checking
01:55

Module 6: What-If Analysis Tools

07:31
Lecture 21 Using the Scenario Manager
03:20
Lecture 22 Using Goal Seek
01:43
Lecture 23 Analyzing with Data Tables
02:28

Module 7: Worksheet and Workbook Protection

10:28
Lecture 24 Understanding Protection
01:53
Lecture 25 Encrypting Files with Passwords
02:35
Lecture 26 Allowing Specific Worksheet Changes
01:27
Lecture 27 Adding Protection to Selected Cells
02:48
Lecture 28 Additional Protection Features
01:45

Module 8: Advanced Use of PivotTables and PowerPivot

14:50
Lecture 29 Using the PivotTable and PivotChart Wizard
02:02
Lecture 30 Adding a Calculated Field
01:33
Lecture 31 Adding a Calculated Item
01:43
Lecture 32 Applying Conditional Formatting to a PivotTable
02:35
Lecture 33 Using Filters in a PivotTable
01:42
Lecture 34 Creating Filter Pages for a PivotTable
01:16
Lecture 35 Enabling the PowerPivot Add-In
03:59

Module 9: Automating with Macros

14:24
Lecture 36 What are Macros?
02:37
Lecture 37 Displaying the Developer Tab
02:41
Lecture 38 Creating a Basic Formatting Macro
02:58
Lecture 39 Running a Macro
01:32
Lecture 40 Assigning a Macro to a Button
02:07
Lecture 41 Creating Complex Macros
02:29
Lecture 42 Viewing and Editing the VBA Code
Lecture 43 Adding a Macro to the Quick Access Toolbar

Module 10: Working with Form Controls

Lecture 44 What are Form Controls?
Lecture 45 Adding Spin Buttons and Check Boxes
Lecture 46 Adding a Combo Box

Module 11: Ensuring Data Integrity

13:13
Lecture 47 What is Data Validation?
01:26
Lecture 48 Restricting Data Entry to Whole Numbers
01:33
Lecture 49 Restricting Data Entry to a List
01:07
Lecture 50 Restricting Data Entry to a Date
01:15
Lecture 51 Restricting Data Entry to Specific Text Lengths
01:23
Lecture 52 Composing Input Messages
01:36
Lecture 53 Composing Error Alerts
02:00
Lecture 54 Finding Invalid Data
01:56
Lecture 55 Editing and Deleting Data Validation Rules
00:57

Module 12: Collaborating in Excel

14:35
Lecture 56 Working with Comments
03:04
Lecture 57 Printing Comments and Errors
01:45
Lecture 58 Sharing a Workbook
03:21
Lecture 59 Tracking Changes in a Workbook
02:35
Lecture 60 Working with Versions
01:59
Lecture 61 Sharing Files Via Email
01:51

Module 13: Importing and Exporting Data to a Text File

04:00
Lecture 62 Importing a Text File
02:44
Lecture 63 Exporting Data to a Text File
01:16

Module 14: Conclusion

Lecture 64 Course Recap
350