Skip to Main Content

Microsoft Excel 2013 Advanced Essentials  (MO13EX201)

Time: 9AM - 4PM Eastern Time (ET)
Duration: 1 Day

Compatible with 2013

Description:

In this course you will learn how to: create advanced formulas and functions; create named ranges; resolving formula errors; consolidate data; removing duplicates; configure data validation; transpose data; use outlining, grouping, and subtotal tools; working with scenarios; use data analysis tools (such as Solver and Goal Seek); create and use PivotTables and PivotCharts; use PowerPivot to integrate data from several different sources; record, edit, and use macros.

Prerequisites:

MO13EX101—Microsoft Excel 2013 Core Essentials or equivalent knowledge or experience is mandatory.

This course is currently available in another city, on demand or for private classes only. Call us at 888.484.2933 to learn more about our flexible scheduling options or complete our Information Request Form and we will contact you to arrange a class date that is suitable to your availability.

Course Outline

  • Module 1: Advanced Formula Tasks
    • Understanding Relative and Absolute Cell References
    • Using Multiple Cell References
    • Using 3D References
    • Using Array Formulas
  • Module 2: Working with Named Ranges
    • Understanding Named Ranges
    • Defining Named Ranges
    • Editing Named Ranges
    • Deleting Named Ranges
    • Using Named Ranges in Formulas
  • Module 3: Using Advanced Functions
    • Using the PMT Function
    • Using the FV Function
    • Using VLOOKUP and HLOOKUP Function
    • Using IF, AND, OR Functions
  • 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
  • Module 5: Managing Data
    • Consolidating Information
    • Removing Duplicates
    • Configuring Data Validation
    • Transposing Data
    • Converting Text to Columns
  • Module 6: Outlining and Grouping Data
    • Outlining Data
    • Showing and Hiding Outline Details
    • Grouping Data
    • Creating Subtotals
    • Removing Outlining and Grouping
  • Module 7: Working with Scenarios
    • Creating Scenarios
    • Loading Scenarios
    • Merging Scenarios
    • Editing Scenarios
    • Creating a Scenario Summary Report
    • Deleting Scenarios
  • Module 8: Using Solver
    • Setting up the Worksheet
    • Running Solver
    • Modifying Constraints
    • Setting Solver Options
  • Module 9: Analyzing Data
    • Enabling the Analysis ToolPak
    • Using Data Analysis Tools
    • Using Goal Seek
    • Using Data Tables
  • 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
  • Using PowerPivot
    • Enabling PowerPivot
    • Importing Access Data
    • Importing Excel Data
    • Integrating Data with Relationships
    • Creating a PivotTable with PowerPivot Data
  • Module 12: Using Macros
    • Recording a Macro
    • Writing a Macro using the Visual Basic Editor
    • Editing a Macro
    • Running a Macro

Private training is available for this course.

Call us at 1-888-484-2933 to learn more about our flexible scheduling options or complete our Information Request Form.

© 2019 Eliquo, Inc. All rights reserved.

Back to Top