Advanced Excel 2010/2013

Duration 2 Day
Unit Standard ID No Unit Standard available

Course Objective

Upon successful completion of this course, users will be able to calculate with Advanced Formulas, Extract data using Sorting, Filtering and Summarizing features. Analyze Data, use the Track and Auditing Features and preform complicated What-If Analysis using the various available tools.

Prerequisite

Users should be working extensively in Excel, and are able to create Involved Functions and Formulas, Manage Tables and Create Simple Pivot Tables.

Topics covered

  • Advanced Formula Techniques
    • Create Name Ranges, Use Relative, Absolute and Mixed Addressing
    • Understand Errors in Formulas and Identify various Operators
    • Using Three Dimensional Ranges and Names
    • Understand and Grasp the Concept of Nesting Formulas
    • Understand the Concept of an ARRAY Formula
  • Functions
    • Date and Time Functions – TODAY, NOW, DATEDIFF
    • Mathematical functions – SUMIF
    • Statistical Functions – COUNTIF
    • Text Functions – CONCATENATE, UPPER, PROPER, LOWER
    • Financial Functions – PMT, FV, PV, IPMT, PPMT, NPV, RATE
    • Logical Functions – IF, IF & AND, IF & OR, IF & NOT, Nested IF’S
    • Lookup Functions – VLOOKUP, HLOOKUP, INDEX, MATCH
  • Auditing Functions
    • Trace Errors
    • Track Precedents and Dependent Cells
  • Managing What-If Analysis
    • Goal Seeking
    • Scenarios
    • Data Tables
  • Sorting and Filtering Date in Full Detail
  • Creating Complex Pivot Tables and Pivot Charts