c26a2c20-0a9e-448c-8942-9b4a28356200

Excel Advanced Essentials

Excel Advanced Essentials

Course Code
EXL2013
Payment Options
Upfront & Payment Plans
Delivery
Online & Correspondence
Duration
25 Hours

Excel 2013 Advanced Essentials

This advanced Microsoft Excel training course includes information on:

  • Advanced formulas and functions
  • Named ranges
  • Resolving formula errors
  • Consolidating data, removing duplicates, configuring data validation, and transposing data
  • Using outlining, grouping, and subtotal tools
  • Working with scenarios
  • Using data analysis tools (such as Solver and Goal Seek)
  • Creating and using PivotTables and PivotCharts
  • Using PowerPivot to integrate data from several different sources
  • Recording, editing, and using macros

Course Outline

Module 1: Advanced Formula Tasks

  • Understanding Relative and Absolute Cell References
  • Using Multiple Cell References
  • Using 3D References
  • Using Array Formulas
  • Review Questions

Module 2: Working with Named Ranges

  • Understanding Named Ranges
  • Defining Named Ranges
  • Editing Named Ranges
  • Deleting Named Ranges
  • Using Named Ranges in Formulas
  • Review Questions

Module 3: Using Advanced Functions

  • Using the PMT Function
  • Using the FV Function
  • Using VLOOKUP and HLOOKUP Functions
  • Using IF, AND, OR Functions
  • Review Questions

Module 4: 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

Module 5: Managing Data

  • Consolidating Information
  • Removing Duplicates
  • Configuring Data Validation
  • Transposing Data
  • Converting Text to Columns
  • Review Questions

Module 6: Outlining and Grouping Data

  • Outlining Data
  • Showing and Hiding Outline Details
  • Grouping Data
  • Creating Subtotals
  • Removing Outlining and Grouping
  • Review Questions

Module 7: Working with Scenarios

  • Creating Scenarios
  • Loading Scenarios
  • Merging Scenarios
  • Editing Scenarios
  • Creating a Scenario Summary Report
  • Deleting Scenarios
  • Review Questions

Module 8: Using Solver

  • Setting up the Worksheet
  • Running Solver
  • Generating Reports and Scenarios with Solver
  • Modifying Constraints
  • Setting Solver Options
  • Review Questions

Module 9: Analyzing Data

  • Enabling the Analysis ToolPak
  • Using Data Analysis Tools
  • Using Goal Seek
  • Using Data Tables
  • Review Questions

Module 10: 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

Module 11: Using PowerPivot

  • Enabling PowerPivot
  • Importing Access Data
  • Importing Excel Data
  • Integrating Data with Relationships
  • Creating a PivotTable with PowerPivot Data
  • Review Questions

Module 12: Using Macros

  • Recording a Macro
  • Writing a Macro using the Visual Basic Editor
  • Editing a Macro
  • Running a Macro
  • Review Questions
Are you interested in this course?
Free Info Pack