Course content

Course contents: Advanced

  • Advanced Formula Tasks • Understanding Relative and Absolute Cell References • Using Multiple Cell References • Using 3D References • Using Array Formulas • Review Questions
  • Working with Named Ranges • Understanding Named Ranges • Defining Named Ranges • Editing Named Ranges • Deleting Named Ranges • Using Named Ranges in Formulas • Review Questions
  • Using Advanced Functions • Using the PMT Function • Using the FV Function • Using VLOOKUP and HLOOKUP Functions • Using IF, AND, OR Functions • Review Questions
  • Resolving Formula Errors • Tracing Formula Precedents and Dependents • Showing Formulas • Evaluating Formulas • Setting Error Checking Options • Using Error Option Buttons • Running an Error Check • Review Questions
  • Managing Data • Consolidating Information • Removing Duplicates • Configuring Data Validation • Transposing Data • Converting Text to Columns • Review Questions
  • Outlining and Grouping Data • Outlining Data • Showing and Hiding Outline Details • Grouping Data • Creating Subtotals • Removing Outlining and Grouping • Review Questions
  • Working with Scenarios • Creating Scenarios • Loading Scenarios • Merging Scenarios • Editing Scenarios • Creating a Scenario Summary Report • Deleting Scenarios • Review Questions
  • Using Solver • Setting up the Worksheet • Running Solver • Generating Reports and Scenarios with Solver • Modifying Constraints • Setting Solver Options • Review Questions
  • Analyzing Data • Enabling the Analysis ToolPak • Using Data Analysis Tools • Using Goal Seek • Using Data Tables • Review Questions
  • Advanced PivotTable Features • Creating a Basic PivotTable • Creating a Basic PivotChart • Using the PivotTable Fields Pane • Adding Calculated Fields • Sorting Pivoted Data • Filtering Pivoted Data • Review Questions
  • Using PowerPivot • Enabling PowerPivot • Importing Access Data • Importing Excel Data • Integrating Data with Relationships • Creating a PivotTable with PowerPivot Data • Review Questions
  • Using Macros • Recording a Macro • Writing a Macro using the Visual Basic Editor • Editing a Macro • Running a Macro • Review Questions