Course Description
If you are looking forward to advance your skills with Microsoft Excel 2016 and want to learn more advanced skills or want to learn the topics covered in this course in the 2016 interface, this Excel 2016 Advanced will be the perfect choice for you!
Through the course you will gain the skills necessary to use pivot tables, audit and analyze worksheet data, utilize data tools, collaborate with others, and create and manage macros. Additionally, you 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 your worksheets and workbooks. You will also learn about Excel’s many collaboration features, as well as how to import and export data to and from your workbooks.
What Will I Learn?
- 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
Requirements
- Excel Introduction and Intermediate courses or equivalent experience.
Who is the target audience?
- Students who want to expand their Excel knowledge.
-
Introduction
00:01:00
-
Summarize Data with SUMIF
00:04:00 -
Summarize Data with AVERAGIF
00:03:00 -
Summarize Data with COUNTIF
00:02:00
-
Using VLOOKUP with TRUE to find an Approximate Match
00:04:00 -
Using HLOOKUP TRUE to find an Approximate Match
00:01:00 -
Using the Index Function
00:03:00 -
Using the Match Function
00:02:00 -
Creating a Combined Index and Match Formula
00:04:00 -
Comparing Two Lists with VLOOKUP
00:02:00 -
Comparing Two Lists with VLOOKUP and ISNA
00:04:00
-
Creating a Nested IF Function
00:03:00 -
Using the IFERROR Function
00:02:00 -
Using the LEN Function
00:02:00 -
Using the TRIM Function
00:01:00 -
Using the Substitute Function
00:02:00
-
Showing Formulas
00:01:00 -
Tracing Cell Precedents and Dependents
00:03:00 -
Adding a watch Window
00:02:00 -
Error Checking
00:02:00
-
Using the Scenario Manager
00:03:00 -
Using Goal Seek
00:02:00 -
Analyzing with Data Tables
00:02:00
-
Protection Overview
00:02:00 -
Excel File Password Encryption
00:03:00 -
Allowing Specific Worksheet Changes
00:01:00 -
Adding Protection to only Certain Cells in a Worksheet
00:03:00 -
Additional Protection Features
00:02:00
-
Using the Pivot Tables Charts Wizard
00:02:00 -
Adding a Calculated Field
00:02:00 -
Adding a Caculated Item
00:02:00 -
Apply Conditional Formatting to a Pivot Table
00:03:00 -
Using Filters in the Pivot Table Fields Pane
00:02:00 -
Creating Filter Pages for a Pivot Table
00:01:00 -
Enabling a Power Pivot Add In
00:04:00
-
What are Macros
00:03:00 -
Displaying the Developer Tab and Enabling Macros in Excel
00:03:00 -
Creating a Basic Formatting Macro
00:03:00 -
Running a Macro
00:02:00 -
Assigning a Macro to a Button
00:02:00 -
Creating a More Complex Macro
00:02:00 -
Viewing and Editing the VBA Code for an Existing Macro
00:03:00 -
Adding a Macro to the Quick Access Toolbar
00:02:00
-
What are Form Controls
00:02:00 -
Adding Spin Buttons and Check Boxes to a Spreadsheet
00:02:00 -
Adding a Combo Box to a Spreadsheet
00:02:00
-
What is Data Validation
00:01:00 -
Restricting Data Entries to Whole Numbers
00:02:00 -
Data Validation Restricting Data Entry to a List
00:01:00 -
Data Validation Restricting Data Entry to a Date
00:01:00 -
Data Validation Restricting Data Entry to Different Text Lengths
00:01:00 -
Composing Input Messages
00:02:00 -
Composing Error Alters
00:02:00 -
Finding Invalid Data
00:02:00 -
Editing and Deleting Data Validation Rules
00:01:00
-
Working with Comments
00:03:00 -
Printing Comments and Errors
00:02:00 -
Sharing a Workbook
00:03:00 -
Tracking Changes in a Workbook
00:03:00 -
Working with Versions
00:02:00 -
Sharing Files via Email
00:02:00
-
Importing a Text File
00:03:00 -
Exporting a Text File
00:01:00
-
Course Recap
00:01:00
Course Reviews
No Reviews found for this course.