Advanced Functions and Formulas

• Formulas with Multiple Operators
• Inserting and Editing a Function
• AutoCalculate and Manual Calculation
• Defining Names
• Using and Managing Defined Names
• Displaying and Tracing Formulas
• Understanding Formula Errors
• Using Financial Functions (PMT)
• Using Logical Functions (IF, AND, OR, SUMIF, COUNTIF & etc)
• Using Lookup & Reference Functions (VLOOKUP, HLOOKUP, MATCH & etc)
• Using Text Functions (PROPER, CONCATENATE, UPPER, LEFT & etc)
• Using Date & Time Functions

Working with Data Ranges

• Creating a Custom AutoFill List
• Applying Conditional Formatting
• Creating & Managing Conditional Formatting Rules
• Sorting by a Custom List
• Filtering Data
• Creating a Custom AutoFilter
• Using an Advanced Filter
• Using Data Validation

Organizing and Analyzing Data

• Creating Scenarios
• Creating a Scenario Report
• Working with Data Tables
• Using Goal Seek
• Using Solver
• Using Text to Columns
• Grouping and Outlining Data
• Using Subtotals
• Consolidating Data by Position or Category
• Consolidating Data Using Formulas
• Sharing a Workbook
• Track changes
• Compare and Merge Workbook

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
• Creating a Standalone PivotChart
• Use Slicers
• Sharing Slicers Between PivotTables
• Creating Relationships between Tables

Working with the Web and External Data

• Inserting a Hyperlink
• Importing Data from an Access Database or Text File
• Importing Data from the Web and Other Sources
• Working with Existing Data Connections

Working with Macros

• Recording a Macro
• Playing and Deleting a Macro
• Adding a Macro to the Quick Access Toolbar