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

Jan 22 - 23, 2020 (9:00am to 5:00pm)

Reviews

"I like how the course outline gave us relevant information and flexibility that we can truly use in our line of work. "

Microsoft Excel Advanced

Brendelmeri Divina

Accounting Analyst, Aboitiz Power Corporation

"The instructor talks the talk and walks the walk as he knows the subject very well. He speaks clearly, with conviction and is able to answer our queries with sure answers. He also coursed through the materials completely, leaving no lesson or bullet point behind. In fact, he taught us bonus items and simple tricks/hacks which I'm sure will be equally useful when we apply all the things that we've learned during our two-day course in advanced excel. "

Microsoft Excel Advanced

Richelle Eve Baquerfo

Finance Analyst, Aboitiz Power Corporation

"The course content covered clearly the complex topics in which I expected to learn about. What I liked the most is the use of pivot tables, vlookup/hlookup and the named ranges, as these will be helpful for me in my field of work. Thank you! "

Microsoft Excel Advanced

Janine Yonzon

Accounting Analyst, Aboitiz Power Corporation

"All of my expectations were met. The instructor did really great! "

Microsoft Excel Advanced

Howell Basilad

Accounting Analyst, Aboitiz Power Corporation

"The Instructor knows well-hidden features of Excel and has several applications with it."

Microsoft Excel Advanced

Elsa Paniza

PCI Specialist, Integrated Micro-Electronics Inc.

"Very good at giving technical skills that can help do our job much easier."

Microsoft Excel Advanced

Ester Zeresh Reyes

Data Coordinator, Wyndham Destinations

"We are able to explore Excel already for our everyday work, tasks using the course we learned. It was taught and summarized in a way we can understand and use it."

Microsoft Excel Advanced

Maria Cecilia Condez

Reports Analyst, Acquire BPO

"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