Microsoft Excel Advanced

Microsoft Excel Training Philippines

Download PDF

COURSE CODE

132

DURATION

2 days

Overview

Courses Benefits

Microsoft Excel has many time saving tools that will help you become even more productive. This advanced Excel course covers a wide range of topics that will help you take full advantage of Excel's powerful features.

You Will Learn How To

  • Work with named ranges
  • Use advanced functions
  • Resolve formula errors
  • Consolidate and manipulate data
  • Analyze data using the Analysis Toolpak
  • Work with PivotTables
  • Use Macros

Who Should Attend

Anyone who wants to use Microsoft Excel to create spreadsheets and automate data processing. Basic Excel knowledge at the level of Course 131 is expected.

Course Investment

Course Outline

Advanced Formula Tasks

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

Working with Named Ranges

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

Using Advanced Functions

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

Resolving Formula Errors

  • Tracing Formula Precedents and Dependents
  • Showing Formulas
  • Evaluating Formulas
  • Setting Error Checking Options
  • Using Error Option Buttons
  • Running an Error Check

Managing Data

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

Outlining and Grouping Data

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

Working with Scenarios

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

Using Solver

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

Analyzing Data

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

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 Macros

  • Recording a Macro
  • Editing a Macro
  • Running a Macro
    (Note: Macro Programming is not covered)

Schedule

March 27 - 28, 2018 (9:00am to 5:00pm)

Reviews

"The course content and the flow of the lecture topics were easy to follow and track throughout the lecture."

Microsoft Excel Advanced

Alexandra Gatchallan

United Laboratories Inc. (Unilab)

"Content is really useful for my work ."

Microsoft Excel Advanced

Katrina Ann Alcantara

United Laboratories Inc. (Unilab)

"The course met my expectations. I gained additional skills and knowledge. The activities are easy to follow and understand. Content is really useful for my work ."

Microsoft Excel Advanced

Ella Marie Grace Lingao

Land Bank of the Philippines

Request More Info