Microsoft Excel Advanced

Microsoft Excel Training Philippines

Download PDF
Microsoft Excel Advanced

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 What-If Analysis
  • 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
  • Using TODAY, NOW, DATE, and TIME Functions
  • Using the TRIM Function
  • Using LEN and SUBSTITUTE Functions
  • Using LEFT, RIGHT, and MID Functions
  • Using the CONCATENATE Function
  • Using SUMIF, COUNTIF, and AVERAGEIF 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 Goal Seek

  • Introducing Goal Seek
  • Using Goal Seek

Using Data Tables

  • Introducing Data Tables
  • 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)

Sharing and Protecting Workbooks

  • Co-authoring
  • Sharing a Workbook
  • Protecting a Worksheet
  • Protecting Cells
  • Protecting a Workbook

Using the Camera Tool

  • Introducing the Camera Tool
  • Adding the Camera Tool
  • Using the Camera Tool

Schedule

Aug 23 - 24, 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