Microsoft Excel (Version: 2010)

Duration: 2 days

This program is designed for executives who are already familiar with the basics of Microsoft Excel, and who would like to work with more advanced features of Microsoft Excel that help in improving their efficiency of working with worksheets, analyzing data, creating MIS reports, and automating various tasks.

Program Objectives

This Advanced Excel training program will empower the participants to be able to do the following:

• Performing complex calculations more efficiently, using various Excel functions.

• Organizing and analyzing large volumes of data.

• Creating MIS reports.

• Designing and using templates.

• Analyzing data using chart

• Consolidating and managing data from multiple workbooks.

• Recording Macro to automate work

Audience:

Executives and managers who have already been using Microsoft Excel, but now feel the need for learning more powerful features and options of Excel, to manage their worksheet-related tasks more efficiently.

Prerequisites:

Participants attending this training should be familiar with the basic operations in Microsoft Excel, such as simple calculations, formatting and printing.

Modules:

Module 1 – Overview of the Basics

Module 2 – Working with Cell Reference

Module 3 – Working wit Formulas & Functions

Module 4 – Sorting & Filtering data

Module 5 – Working with Styles & Formatting

Module 6 – Working with Vlookup & Index and Match

Module 7 – Working with Range Names

Module 8 – Data Validation

Module 9 – Working with Templates

Module 10 – Working with Reports

Module 11 – More Functions

Module 12 – What-If Analysis

Module 13 – Basic of Macros

Course Outline

Module 1: Overview of the Basics

• Explore the User Interface

• Work with the Ribbon & Backstage view

• Work with Contextual Tabs

• Use the Excel Galleries

• Customize the Excel Interface

Module 2: Working with Cell Reference

• Understanding Cell Reference

• Working with all kind of cell reference (Relative, Absolute & Mixed)

• Understanding Mixed cell reference with some formulas

Module 3: Working with Formulas & Functions

• Working with general & advance function(SUM, AVERAGE, MAX,MIN, LARGE & SMALL,SUMIF,SUMIFS,AVERAGEIF,AVERAGEIFS,COUNTIF,COUNTIFS,RANK)

• Writing conditional expressions (using IF)

• Using Nested IF

• Limitations of IF

• Using logical functions (AND, OR, NOT) inside IF

• Using Date & Time function in IF

Module 4: Sorting and Filtering Data

• Sorting lists

• Sorting data according to color

• Different option of Sort

• Using multiple-level sorting

• Using custom sorting

• Using simple Filter

• Working with top/bottom records

• Working with date filter

• Using wild card in filter

• Working with text filter

• Understanding advance filter

• Copying filter data into different location using

• Working with unique records in filter

Module 5: Working with Styles & Formatting

• Cell Styles

• Creating lists using Table

• Formatting the structure of a list

• Conditional Formatting

• Working with different option of conditional formatting

• Using formula/function in formatting

Module 6: Working with VLookup & Index and Match

• Using simple VLookup

• Using Column function in Vlookup

• Limitation of Vlookup

• Joining two Vlookup using Concatenate function

• Working with Index & Match function

• Understanding Hlookup function

Module 7: Working with Range Names

• Defying a range name

• Defying the scope of a range name

• Modifying/deleting the range name

Module 8: 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 9: Working with Templates

• Designing the structure of a template

• Using templates for standardization of worksheets

Module 10: Working with Reports

• Creating subtotals

• Multiple-level subtotals

• Creating Pivot tables

• Formatting and customizing Pivot tables

• Using Multiple options of Pivot tables

• Creating group in Pivot tables

• Pivot charts

• Using external data sources

Module 11: More Functions

• Date and time Functions

• Text Functions

• Math & Statistical Function(SUMIF,COUNTIF, COUNT, COUNTA & COUNTBLANK)

Module 12: What-If Analysis

• Using goal seek

• Using data tables

• Creating and editing scenarios

Module 13: Basic of Macros

• Recording and executing macros

• Understanding different types of references in macros

• Assigning macros to Quick Access Toolbar

• Editing macros using VBA Editor

PDUs for Credential Holders: 14 PDUs