Excel 2016: Formulas, Functions & Analysis ( 5 Days)
- This event has passed.
This course builds upon the foundational knowledge presented in the Microsoft® Office Excel® 2016: Part 1 course and will help start you down the road to creating advanced workbooks and worksheets that can help deepen your understanding of organizational intelligence. The ability to analyze massive amounts of data, extract actionable information from it, and present that information to decision makers is at the foundation of a successful organization that is able to compete at a high level.
This course covers Microsoft Office Specialist exam objectives to help students prepare for the Excel 2016 Exam and the Excel 2016 Expert Exam. This course has extensive practical application.
Pre-requisites
To ensure success, students should have completed the Part 1 course or have the equivalent knowledge and experience.
Objectives
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:
- Work with functions
- Work with lists
- Analyze data
- Visualize data with charts
- Use PivotTables and PivotCharts
- Work with multiple worksheets and workbooks
- Use Lookup functions and formula auditing
- Share and protect workbooks
- Automate workbook functionality
- Create Sparklines and map data
- Forecast data
- Using Array Formulas
- Working with Graphical Objects
- Importing and Exporting Data
- Working with Forms and Controls
- Preparing data and creating PivotTables
- Analyze Data Using PivotTables
- Work with PivotCharts
- Get started with Power Pivot
- Visualize Power Pivot data
- Work with advanced functionality in Power Pivot
Course contents
Outline Manual A
Lesson 1: Working with Functions
Topic A: Work with Ranges
Topic B: Use Specialized Functions
Topic C: Work with Logical Functions
Topic D: Work with Date & Time Functions
Topic E: Work with Text Functions
Lesson 2: Working with Lists
Topic A: Sort Data
Topic B: Filter Data
Topic C: Query Data with Database Functions
Topic D: Outline and Subtotal Data
Lesson 3: Analyzing Data
Topic A: Create and Modify Tables
Topic B: Apply Intermediate Conditional Formatting
Topic C: Apply Advanced Conditional Formatting
Lesson 4: Visualizing Data with Charts
Topic A: Create Charts
Topic B: Modify and Format Charts
Topic C: Use Advanced Chart Features
Lesson 5: Using Pivot Tables and Pivot Charts
Topic A: Create a PivotTable
Topic B: Analyze PivotTable Data
Topic C: Present Data with PivotCharts
Topic D: Filter Data by Using Timelines and Slicers
Lesson 6: Working with Multiple Worksheets and Workbooks
Topic A: Use Links and External References
Topic B: Use 3-D References
Topic C: Consolidate Data
Lesson 7: Using Lookup Functions and Formula Auditing
Topic A: Use Lookup Functions
Topic B: Trace Cells
Topic C: Watch and Evaluate Formulas
Lesson 8: Sharing and Protecting Workbooks
Topic A: Collaborate on a Workbook
Topic B: Protect Worksheets and Workbooks
Lesson 9: Automating Workbook Functionality
Topic A: Apply Data Validation
Topic B: Search for Invalid Data and Formulas with Errors
Topic C: Work with Macros
Lesson 10: Creating Sparklines and Mapping Data
Topic A: Create Sparklines
Topic B: Map Data
Lesson 11: Forecasting Data
Topic A: Determine Potential Outcomes Using Data Tables
Topic B: Determine Potential Outcomes Using Scenarios
Topic C: Use the Goal Seek Feature
Topic D: Forecasting Data Trends
Lesson 12: Using Array Formulas
Topic A: Use Array Formulas
Lesson 13: Working with Graphical Objects
Topic A: Insert Graphical Objects
Topic B: Modify Graphical Objects
Topic C: Work with SmartArt
Lesson 14: Importing and Exporting Data
Topic A: Import and Export Data
Lesson 15: Working with Forms and Controls
Topic A: Work with Forms and Controls
Outline Manual B
Lesson 1: Preparing Data and Creating PivotTables
Topic A: Prepare Data
Topic B: Create a PivotTable from a Local Data Source
Topic C: Create a PivotTable from Multiple Local Data Sources
Topic D: Create a Pivot Table from an External Data Source
Lesson 2: Analyzing Data Using PivotTables
Topic A: Summarize PivotTable Data
Topic B: Organize PivotTable Data
Topic C: Filter PivotTable Data
Topic D: Format a PivotTable
Topic E: Refresh and Change Pivot Table Data
Lesson 3: Working with PivotChart
Topic A: Create a PivotChart
Topic B: Manipulate PivotChart Data
Topic C: Format a Pivot Chart
Lesson 4: Getting Started with Power Pivot
Topic A: Enable and Navigate Power Pivot
Topic B: Manage Data Relationships
Lesson 5: Visualizing Power Pivot Data
Topic A: Create a Power Pivot Report
Topic B: Create Calculations in Power Pivot
Lesson 6: Working with Advanced Functionality in Power Pivot
Topic A: Create a Key Performance Indicator
Topic B: Work with Dates and Time in Power Pivot