MicroSoft Advanced Excel

Reading Time: 2 minutes

Microsoft Advanced Excel

    Module 1: Overview of the Basics of Excel
    • Customizing common options in Excel
    • Absolute and relative cells
    • Protecting and un-protecting worksheets and cells

    Module 2: Working with Functions

    • Writing conditional expressions (using IF)
    • Using logical functions (AND, OR, NOT)
    • Using lookup and reference functions (VLOOKUP, HLOOKUP, MATCH, INDEX)
    • VlookUP with Exact Match, Approximate Match
    • Nested VlookUP with Exact Match
    • VlookUP with Tables, Dynamic Ranges
    • Nested VlookUP with Exact Match
    • Using VLookUP to consolidate Data from Multiple Sheets

    Module 3: Data Validations

    • Specifying a valid range of values for a cell
    • Specifying a list of valid values for a cell
    • Specifying custom validations based on formula for a cell

    Module 4: Working with Templates

    • Designing the structure of a template
    • Using templates for standardization of worksheets

    Module 5: Sorting and Filtering Data

    • Sorting tables
    • Using multiple-level sorting
    • Using custom sorting
    • Filtering data for selected view (AutoFilter)
    • Using advanced filter options

    Module 6: Working with Reports

    • Creating subtotals
    • Multiple-level subtotals
    • Creating Pivot tables
    • Formatting and customizing Pivot tables
    • Using advanced options of Pivot tables
    • Pivot charts
    • Consolidating data from multiple sheets and files using Pivot tables
    • Using external data sources
    • Using data consolidation feature to consolidate data
    • Show Value As ( % of Row, % of Column, Running Total, Compare with Specific Field)
    • Viewing Subtotal under Pivot
    • Creating Slicers ( Version 2010 & Above)

    Module 7: More Functions

    • Date and time functions
    • Text functions
    • Database functions
    • Power Functions (CountIf, CountIFS, SumIF, SumIfS)

    Module 8: Formatting

    • Using auto formatting option for worksheets
    • Using conditional formatting option for rows, columns and cells

    Module 9: Macros

    • Relative & Absolute Macros
    • Editing Macro’s

    Module 10: WhatIf Analysis

    • Goal Seek
    • Data Tables
    • Scenario Manager

    Module 11: Charts

    • Using Charts
    • Formatting Charts
    • Using 3D Graphs
    • Using Bar and Line Chart together
    • Using Secondary Axis in Graphs
    • Sharing Charts with PowerPoint / MS Word, Dynamically
      (Data Modified in Excel, Chart would automatically get updated)

    Module 12: New Features Of Excel

    • Sparklines, Inline Charts, data Charts
    • Overview of all the new features