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
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
