• LOGIN
  • No products in the cart.

Take Excel to the next level with advanced formulas, What-If Analysis tools, advanced PivotTables, Macros and much more.


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.

Learning Outcomes

  • 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

Pre-requisites

Excel Introduction and Intermediate courses or equivalent experience.


Who is this course intended for?

Students who want to expand their Excel knowledge.

Course Curriculum

Introduction
Introduction 00:00:00
Using Advanced IF Statements
Summarizing Data with SUMIF 00:00:00
Summarizing Data with AVERAGEIF 00:00:00
Summarizing Data with COUNTIF 00:00:00
Using Advanced Lookup Functions
Using VLOOKUP with TRUE 00:00:00
Using HLOOKUP with TRUE 00:00:00
Using the INDEX Function 00:00:00
Using the MATCH Function 00:00:00
Combining INDEX and MATCH 00:00:00
Comparing Two Lists with VLOOKUP 00:00:00
Comparing Two Lists with VLOOKUP and ISNA 00:00:00
Using Complex Logical and Text Functions
Creating a Nested IF Function 00:00:00
Using the IFERROR Function 00:00:00
Using the LEN Function 00:00:00
Using the TRIM Function 00:00:00
Using the SUBSTITUTE Function 00:00:00
Formula Auditing
Showing Formulas 00:00:00
Tracing Precedents and Dependents 00:00:00
Adding a Watch Window 00:00:00
Error Checking 00:00:00
What-If Analysis Tools
Using the Scenario Manager 00:00:00
Using Goal Seek 00:00:00
Analyzing with Data Tables 00:00:00
Worksheet and Workbook Protection
Understanding Protection 00:00:00
Encrypting Files with Passwords 00:00:00
Allowing Specific Worksheet Changes 00:00:00
Adding Protection to Selected Cells 00:00:00
Additional Protection Features 00:00:00
Advanced Use of PivotTables and PowerPivot
Using the PivotTable and PivotChart Wizard 00:00:00
Adding a Calculated Field 00:00:00
Adding a Calculated Item 00:00:00
Applying Conditional Formatting to a PivotTable 00:00:00
Using Filters in a PivotTable 00:00:00
Creating Filter Pages for a PivotTable 00:00:00
Enabling the PowerPivot Add-In 00:00:00
Automating with Macros
What are Macros? 00:00:00
Displaying the Developer Tab 00:00:00
Creating a Basic Formatting Macro 00:00:00
Running a Macro 00:00:00
Assigning a Macro to a Button 00:00:00
Creating Complex Macros 00:00:00
Viewing and Editing the VBA Code 00:00:00
Adding a Macro to the Quick Access Toolbar 00:00:00
Working with Form Controls
What are Form Controls? 00:00:00
Adding Spin Buttons and Check Boxes 00:00:00
Adding a Combo Box 00:00:00
Ensuring Data Integrity
What is Data Validation? 00:00:00
Restricting Data Entry to Whole Numbers 00:00:00
Restricting Data Entry to a List 00:00:00
Restricting Data Entry to Specific Text Lengths 00:00:00
Restricting Data Entry to a Date 00:00:00
Composing Input Messages 00:00:00
Composing Error Alerts 00:00:00
Finding Invalid Data 00:00:00
Editing and Deleting Data Validation Rules 00:00:00
Collaborating in Excel
Working with Comments 00:00:00
Printing Comments and Errors 00:00:00
Sharing a Workbook 00:00:00
Tracking Changes in a Workbook 00:00:00
Working with Versions 00:00:00
Sharing Files via Email 00:00:00
Importing and Exporting Data to a Text File
Importing a Text File 00:00:00
Exporting Data to a Text File 00:00:00
Conclusion
Course Recap 00:00:00

Course Reviews

N.A

ratings
  • 5 stars0
  • 4 stars0
  • 3 stars0
  • 2 stars0
  • 1 stars0

No Reviews found for this course.

TAKE THIS COURSE
  • $99.00 $10.00
  • UNLIMITED ACCESS
  • Course Certificate
STUDENTS ENROLLED

    Related Courses

    © Learnsector