Advance Excel and Data Analysis using Excel

Microsoft Excel has created incredible efficiencies in Business Planning, tax planning, auditing, finance, marketing and accounting industries. Excel is widely-used by many businesses to manage, track and analyze company data. Excel is also used to track and manage personal finances. Excel is the most-used Spread sheet program in the business world.

Excel provides enormous capacity to do quantitative analysis, Statistical analyses of databases with hundreds of thousands of records to complex estimation tools with user-friendly front ends. And unlike traditional statistical programs, it provides an intuitive interface that lets user see what happens to the data as he manipulates them.

This course explores the power of excel and its usage in business, Finance, Marketing, accounting industry.

Who should do?

Advance Excel Skill is required in almost all the profiles involving Excel / Spread sheet Usage. It is seamlessly used in Accounting & Taxation Profiles , Business & Marketing Planning profiles , Back office Profiles , MIS reporting Profiles . Nowadays it is expected from most of the professional like CA / MBA / ICWA and required by graduates for most of the office jobs involving Excel usage.

Course curriculum

Overview of the Basics&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
  • Nested VlookUP with Exact Match
  • Using VLookUP to consolidate Data from Multiple Sheets

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

Working with Templates&Sorting and Filtering Data

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

Working with Reports

  • Creating subtotals, Multiple-level subtotals
  • Creating Pivot tables, Formatting and customizing 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


More Functions

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


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

WhatIf Analysis

  • Goal Seek
  • Data Tables
  • Scenario Manager


  • Using Charts, Formatting Charts , Using 3D Graphs
  • Using Bar and Line Chart together , Using Secondary Axis in Graphs
  • Formatting charts
  • (Data Modified in Excel, Chart would automatically get updated)

Course duration: 40 hoursCourse Fees: Rs 12000