CERTIFICATE IN ADVANCED EXCEL ( RS-COM-03 )

1.  Overview of the Basics of Excel

ü Customizing common options in Excel

ü Absolute and relative cells

ü Protecting and un-protecting worksheets and cells

ü Conditional Formatting

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

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

4.  Working with Templates

ü Designing the structure of a template

ü Using templates for standardization of worksheets

5.  Sorting and Filtering Data

ü Sorting tables

ü Using multiple-level sorting

ü Using custom sorting

ü Filtering data for selected view (AutoFilter)

ü Using advanced filter options

6.  Working with Reports

ü Creating subtotals

ü Multiple-level subtotals

7.  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)

8.  More Functions

ü Date and time functions

ü Text functions

ü Database functions

ü Financial Function

ü Power Functions (CountIf, CountIFS, SumIF, SumIfS)

9.  Formatting

ü Using auto formatting option for worksheets

ü Using conditional formatting option for rows, columns and cells

10. WhatIf Analysis

ü Consolidate

ü Goal Seek

ü Data Tables

ü Scenario Manager

ü Text To Column

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)

12. New Features Of Excel

ü Sparklines, Inline Charts, data Charts

ü Overview of all the new feature

ü Protection

ü Protect Sheet

ü Protect Work Book