Microsoft® Excel 2016 Formulas, Functions & PivotTables for the Advanced Excel User

Take this course if you want to learn useful tools, tricks and tips for utilizing functions and formulas available in Microsoft® Excel 2013. Learn which are best suited from over 300 available built-in functions. If you need to analyze large amounts of data quickly and easily, this class covers PivotTables – Excel’s best resource for doing so.
Course Content:

Lesson 1: The Interface
Quick Access Toolbar
Home Ribbon
Insert Ribbon
Page Layout Ribbon
Formulas Ribbon
Data Ribbon
Review Ribbon

Lesson 2: Fundamentals Review
Overview of Formulas and Using AutoSum
AutoFill and AutoComplete
Moving Data
Editing Tools
Conditional Formatting
Cell Merging and AutoFit
Find and Replace
Working with Worksheet Appearance

Lesson 3: Working with Formulas
Relative and Absolute Cell References
Mathematical Operators
Formulas with Multiple Cell References
Formula Auditing Buttons
Fixing Formula Errors
Displaying and Printing Formulas
Displaying and Tracing Formulas

Lesson 4: Working with Functions
Inserting and Editing a Function
Defining Names
Using and Managing Defined Names
Understanding Formula ErrorsUsing Logical Functions (IF)
Using Financial Functions (PMT)
Using Database Functions (DSUM)
Using Lookup Functions (VLOOKUP)
Financial Functions
Date & Time Functions
Math & Trig Functions
Statistical Functions
Lookup & Reference Functions
Database Functions
Text Functions
Logical Functions

Lesson 5: Working with Web and External Data
Inserting a Hyperlink
Creating a Web Page from a Workbook
Importing Data from an Access Database or Text File
Importing Data from the Web or Other Sources
Working With Existing Data Connections

Lesson 6: Working with PivotTables
Creating a PivotTable
Specifying PivotTable Data
Changing a PivotTable’s Calculation
Filtering and Sorting a PivotTable
Working with PivotTable Layout
Grouping PivotTable Items
Updating a PivotTable
Formatting a PivotTable
Creating a PivotChart

Lesson 7: What-If Tools for Analyzing Data
Working with Data Tables
Creating Scenarios
Creating a Scenario Report
Using Goal Seek
Using Solver