Course Title: Microsoft Excel, Level 2 (Version : 2007)
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.
Consolidating and managing data from multiple workbooks
Audience
The professionals 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.
Day-wise Break-up
Day Module Topic
Day 1 Module 1 Overview of the Basics
Module 2 Working with Functions
Module 3 Data Validation
Module 4 Working with Templates
Module 5 Sorting and Filtering Data
Module 6 Working with Reports
Day 2 Module 7 More Functions
Module 8 What-If Analysis
Module 9 Working with Styles
Module 10 Data Forms
Module 11 Workbook Sharing and Auditing
Module 12 Working with External Data
Module 13 Using Macros
Course Outline
Module 1: Overview of the Basics
Explore the User Interface
Work with the Ribbon
Work with Contextual Tabs
Use the Excel Galleries
Customize the Excel Interface
Absolute and relative cells
Protecting and un-protecting worksheets and cells
Work with Comments
Access External Resources Using Hyperlinks
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)
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 and filtering lists
Sorting data according to color
Using multiple-level sorting
Using custom sorting
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
Module 7: More Functions
Date and time functions
Text functions
Database functions
Module 8: What-If Analysis
Using goal seek
Using data tables
Creating and editing scenarios
Module 9: Working with Styles
Cell Styles
Creating lists using Table
Formatting the structure of a list
Conditional Formatting 10: Data Forms
Using forms to simplify data manipulation
Specifying criteria for finding records
Module 11: Workbook Sharing and Auditing
Tracking changes
Merging workbooks
Tracing precedents and dependents
Tracing errors
Module 12: Working with External Data
Querying external databases
Saving, editing and reusing external data queries
Importing text files
Refreshing data
Recording and executing macros
Understanding different types of references in macros
Assigning macros to toolbars or menu items
Editing macros using VBA Editor
Writing function using VBA Editor
