Microsoft Office Excel 2016 Advanced

Before starting this course, students are recommended to take the following course or have equivalent knowledge: Microsoft® Office Excel® 2016: Basic & Intermediate Level. 

You will automate some common Excel tasks, apply advanced analysis techniques to more complex data sets, troubleshoot errors, protecting data, and you will also organize table data. 

Upon successful completion of this course, students will be able to: 

•               Enhance productivity and efficiency by streamlining the workflow. 

•               Organize worksheet and table data using various techniques. 

•               Summarizing data using functions. 

•               Analyze data using PivotTables, Slicers, and PivotCharts. 

•               Audit worksheets. 

•               Analyze data. 

•               Protecting data. 

•               Import and export data. 

•               Recording macro. 

This course is meant for those desiring to gain advanced skill sets necessary for calculating data using functions and formulas, sorting & filtering data, using PivotTables, Slicers & PivotCharts for analyzing data, audit and analyze worksheet data, import and export data, protecting data, and record a macro.

Lesson 1: Automating Worksheet Functionality 

Topic 1A: Apply Conditional Formatting 

Topic 1B: Add Data Validation Criteria 

Lesson 2: Working with Lists 

Topic 2A: Format data as Tables 

Topic 2B: Create Sparklines 

Topic 2C: Sorting Data 

Topic 2D: Filtering data 

Lesson 3: Data List Outline 

Topic 3A: Create Outline Group 

Topic 3B: Create SubTotal 

Lesson 4: Using Functions to Summarizing Data 

Topic 4A: Use SumIF, AverageIF, CountIF Function 

Topic 4B: Use SumIFs, AverageIFs, CountIFs Function 

Lesson 5: Analyzing Data with PivotTables, Slicers, and PivotCharts 

Topic 5A: Create a PivotTables 

Topic 5B: Analyze PivotTable Data 

Topic 5C: Filter Data by Using Timelines and Slicers 

Topic 5D: Present Data with PivotCharts 

Lesson 6: Auditing Worksheets 

Topic 6A: Trace Formulas 

Topic 6B: Watch and Evaluate Formulas 

Topic 6C: Troubleshoot Formula Errors 

Lesson 7: Analyzing and Presenting Data  

Topic 7A: Create Scenario 

Topic 7B: Analyze Data using Goal Seek Feature 

Topic 7C: Analyze Data using Solver Feature 

Lesson 8: Enhancing Workbook  

Topic 8A: Protecting Data 

Topic 8B: Create and Use Template 

Lesson 9: Importing and Exporting Data 

Topic 9A: Import Data 

Topic 9B: Export Data  

Lesson 10: Macro 

 Topic 10A: Recording a Macro 

Topic 10B: Editing a Macro