Take this
course

Office Productivity / Microsoft

Excel 2016 Advanced

Learn the advanced features and functionality of Excel 2016, including PivotTables and advanced functions.

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


Instructor: Ed McCrae

Full details

Curriculum

  • Introduction
    Introduction
    1:05
    Course Exercise Files
  • Using Advanced IF Statements
    Summarize Data with SUMIF
    4:28
    Summarize Data with AVERAGEIF
    2:36
    Summarize Data with COUNTIF
    1:53
  • Using Advanced Lookup Functions
    Using VLOOKUP with TRUE
    3:41
    Using HLOOKUP with TRUE
    1:23
    Using the INDEX Function
    3:10
    Using the MATCH Function
    2:15
    Combining INDEX and MATCH
    4:10
    Comparing Two Lists with VLOOKUP
    1:43
    Comparing Two Lists with VLOOKUP and ISNA
    3:48
  • Using Complex Logical and Text Functions
    Creating a Nested IF Function
    2:54
    Using the IFERROR Function
    2:16
    Using the LEN Function
    2:08
    Using the TRIM Function
    1:04
    Using the SUBSTITUTE Function
    2:23
  • Formula Auditing
    Showing Formulas
    1:02
    Tracing Cell Precedents and Dependents
    2:34
    Adding a Watch Window
    2:17
    Error Checking
    1:56
  • What-If Analysis Tools
    Using the Scenario Manager
    3:21
    Using Goal Seek
    1:44
    Analyzing with Data Tables
    2:29
  • Worksheet and Workbook Protection
    Understanding Protection
    1:54
    Encrypting Files with Passwords
    2:36
    Allowing Specific Worksheet Changes
    1:28
    Adding Protection to Selected Cells
    2:49
    Additional Protection Features
    1:46
  • Advanced Use of PivotTables and PowerPivot
    Using the PivotTable and PivotChart Wizard
    2:03
    Adding a Calculated Field
    1:34
    Adding a Caculated Item
    1:44
    Apply Conditional Formatting to a PivotTable
    2:36
    Using Filters in the PivotTable Fields Pane
    1:43
    Creating Filter Pages for a PivotTable
    1:17
    Enabling the PowerPivot Add-In
    4:00
  • Automating with Macros
    What are Macros?
    2:38
    Displaying the Developer Tab
    2:42
    Creating a Basic Formatting Macro
    2:59
    Running a Macro
    1:33
    Assigning a Macro to a Button
    2:08
    Creating Complex Macros
    2:30
    Viewing and Editing the VBA Code
    2:38
    Adding a Macro to the Quick Access Toolbar
    1:50
  • Working with Form Controls
    What are Form Controls?
    1:45
    Adding Spin Buttons and Check Boxes
    2:05
    Adding a Combo Box
    1:43
  • Ensuring Data Integrity
    What is Data Validation?
    1:27
    Restricting Data Entries to Whole Numbers
    1:34
    Restricting Data Entry to a List
    1:08
    Restricting Data Entry to Different Text Lengths
    1:24
    Restricting Data Entry to a Date
    1:16
    Composing Input Messages
    1:37
    Composing Error Alerts
    2:01
    Finding Invalid Data
    1:57
    Editing and Deleting Data Validation Rules
    0:58
  • Collaborating in Excel
    Working with Comments
    3:05
    Printing Comments and Errors
    1:46
    Sharing a Workbook
    3:22
    Tracking Changes in a Workbook
    2:36
    Working with Versions
    2:01
    Sharing Files via Email
    1:52
  • Importing and Exporting Data to a Text File
    Importing a Text File
    2:46
    Exporting Data to a Text File
    1:17
  • Conclusion
    Course Recap
    1:15

Skills

  • Microsoft Excel

Similar Courses

More Courses by this Instructor