Welcome to TED Learning!

  • 12-07, Binjai 8 Premium SOHO, No. 2 Lorong Binjai, 50450 Kuala Lumpur
  • Phone: +603-2742 1828
  • training@ted.com.my

Microsoft Excel 2013/2016 (Advanced Level)

By tedadmin | February 09, 2018
Awesome Image

Advanced Formula and Functions

Awesome Image

Advanced Way of Computing Data

This Advanced level of Microsoft Excel 2013 course covers the advance features of Excel. It is suitable for Excel users who wish to enhance their knowledge on more advanced formulas and functions.

At the end of this course you will have better understanding of the more advanced features of Excel. You will be able to link and consolidate information from different Excel files, manipulate data lists using filtering, subtotals and pivot tables. You will also be able to use logical and lookup functions.

What Will I Learn From This Course?

Interlinking Multiple Worksheet

Which of ever undertake laborious
physical exercised, excepts too obtain
some advantage from it?

List and Managing Data

Great explorer of the truth, the
master-builder of human happiness
No one rejects.

Advanced Data Computing

chooses to enjoy a pleasure that
has no annoing consequences, one a
pain that produces.

Course Outline

This program will run for 2 days which which covers Advanced level of Microsoft Excel. In 2 days, trainer will guide and teach students according to these outline to ensure the learning outcome will achieve.

  • Module A1
  • Module A2
  • Module A3
  • Module A4
  • Module A5
  • Module A6
  • Module A7

Module A1: Linking and Consolidating Data

  • Linking Workbooks
    • Creating A Link Formula By Paste Link
  • Consolidating Data
  • Creating A 3-D Formula

Module A2: Managing Tables and Data

  • Tables Overview
    • New Tables Item in Excel 2013
    • Table Terms
    • Tips for Setting Up a Table
    • Creating Tables
  • Sorting Data
    • Sorting Data: Sort Button
    • Sorting Data: Sort Dialog Box
    • To sort according to a custom list
    • To sort according a formatting criterion
    • To sort by more than one column
    • To control how to sort lists of days or months, case-sensitive data, and orientation
  • Using Forms to Enter Table Data
    • Adding the Form Button to the Quick Access Toolbar
    • Accessing the Form Dialog Box
    • Using Form Options
    • Establishing Criteria
  • Filtering Data
    • Using Table Filters
    • Using Custom AutoFilter
    • Turning Off the AutoFilter
  • Advanced Filtering
    • Using Advanced Filter
    • Running an Advanced Filter
    • Turning Off Advanced Filter
    • Copying an Advanced Filter to Another Location
  • Subtotals
    • Adding Subtotals
    • Adjusting Views with Subtotals
    • Removing Subtotals
    • Re-sorting the Data
  • Data Validation
    • How to create a Validation Drop Down List
  • Dealing with Text Files
    • Text Format Files
    • Text To Column

Module A3: Pivot Tables and Pivot Charts

  • Working with PivotTables
  • Notes on Working with PivotTables
    • Creating a PivotTable
    • Creating a PivotTable Report
    • Customizing a PivotTable Report
    • The PivotTable Field List Illustrated
    • PivotTable Tools
  • What is a Pivot Chart?
    • Comparing a PivotTable report and a PivotChart report
    • Differences between PivotChart reports and standard charts
    • Create a PivotChart report from an existing PivotTable report

Module A4: Functions

  • What is a Function?
  • Logical Functions
    • IF Function
    • Nested IF function
  • LookUp Functions
    • VLOOKUP
    • HLOOKUP

Module A5: What if Analysis

  • Creating Data Tables
    • Creating A One Input Data table
    • Creating A Two-Input Data table
  • Goal Seek
  • Solver
    • Using Solver
    • To save adjustable cell values in Solver as a scenario
    • Loading a Solver problem model
  • Scenario Manager
    • How to Create a Report from a Scenario

Module A6: Macros

  • Automating Excel with Macros
    • Creating & Recording a New Macro
    • Running a Macro
    • Suspending & Resuming a Macro
    • Deleting a Macro
    • Saving A Workbook containing Macros

Module A7: Auditing a Worksheet

  • Tracing Formulas and Errors
    • Trace cells that provide data to a formula (precedents)
    • Trace formulas that reference a particular cell (dependents)
  • Error Checking
    • Correct common formula errors one at a time
    • Mark common formula errors on the worksheet and correct them there
    • Correct an error value
  • Working with Comments
    • Adding Comments
    • Editing Comments
    • Displaying/Hiding Comments
    • Moving Comments
    • Deleting Comments
    • Printing Comments
  • Watch Window
    • Add cells to the Watch Window
    • Remove cells from the Watch Window
  • Protecting Your Files and Worksheets
    • Protection Options
    • Locking & Unlocking Cells
    • Workbook Level Protection
    • Worksheet Level Protection
    • File Level Protection

Feel Free To Inquire About our Range of Training Course

Previous client that we have conducted training with

Lorem Ipsum is simply dummy text of the printing and typesetting industry.
Text Title
Lead Manager
Lorem Ipsum is simply dummy text of the printing and typesetting industry.
Text Title
Lead Manager

Over 32 years of experience we’ll ensure you get the best guidance.

Request Quote