Microsoft Advanced Excel 2013- Level 2 & 3

Excel 2013 – Level 2 

Overview:

Students will build upon the foundational Microsoft® Office Excel® 2013 knowledge and skills already acquired and learn to create advanced workbooks and worksheets, including advanced formulas, tables, PivotTables, PivotCharts and data filtering.

Who Should Attend:

This course is designed for students who already have foundational knowledge and skills in Excel 2013 and who wish to begin taking advantage of some of the higher-level functionality in Excel to analyze and present data.

At Course Completion:

Upon successful completion of this course, you will be able to leverage the power of data analysis and presentation in order to make informed, intelligent organizational decisions. You will: •Customize the Excel environment. •Create advanced formulas. •Analyze data by using functions and conditional formatting. •Organize and analyze datasets and tables. •Visualize data by using basic charts. •Analyze data by using PivotTables, slicers, and PivotCharts.

Excel 2013 - Part 3

Overview

In this course, students will learn some of the more advanced features of Excel, including automating common tasks, auditing workbooks to avoid errors, sharing data with other people, analyzing data, and using Excel data in other applications.

Who Should Attend:

This course is intended for a student who has experience working with Excel and would like to learn more about creating macros, working with shared documents, analyzing data, and auditing worksheets.

At Course Completion:

At course completion, students will: • Automate worksheet functions. • Audit worksheets. • Analyze data. • Work with multiple workbooks. • Import and export data.

Excel 2013 – Level 2

Lesson 1: Customizing the Excel Environment

• Configure Excel Options
• Customize the Ribbon and the Quick Access Toolbar
• Enable Excel Add-Ins

Lesson 2: Creating Advanced Formulas

• Use Range Names in Formulas
• Use Specialized Functions
• Use Array Formulas

Lesson 3: Analyzing Data with Functions and Conditional Formatting

• Analyze Data by Using Text and Logical Functions
• Apply Advanced Conditional Formatting

Lesson 4: Organizing and Analyzing Datasets and Tables

• Create and Modify Tables
• Sort Data
• Filter Data
• Use SUBTOTAL and Database Functions

Lesson 5: Visualizing Data with Basic Charts

• Create Charts
• Modify and Format Charts

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

• Create a PivotTable
• Analyze PivotTable Data
• Present Data with PivotCharts
• Filter Data by Using Slicers

Excel 2013 - Part 3

Lesson 1: Working with Multiple Worksheets and Workbooks Simultaneously

• Use 3-D References
• Use Links and External References
• Consolidate Data
•  

Lesson 2: Sharing and Protecting Workbooks

• Collaborate on a Workbook
• Protect Worksheets and Workbooks

Lesson 3: Automating Workbook Functionality

• Apply Data Validation
• Work with Forms and Controls
• Work with Macros
•  

Lesson 4: Applying Conditional Logic

• Use Lookup Functions
• Combine Functions
• Use Formulas and Functions to Apply Conditional Formatting

Lesson 5: Auditing Worksheets

• Trace Cells
• Search for Invalid Data and Formulas with Errors Watch and Evaluate Formulas

Lesson 6: Using Automated Analysis Tools

• Determine Potential Outcomes Using Data Tables
• Determine Potential Outcomes Using Scenarios
• Use the Goal Seek Feature
• Activate and Use the Solver Tool
• Analyze Data with Analysis ToolPak Tools

Lesson 7: Presenting Your Data Visually

• Use Advanced Chart Features
• Create Sparklines