Course Title: Microsoft Excel, Level 2 (2003, XP, 2003/2007)
Duration : 30 hrs
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
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.
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
Module 7
More Functions
Module 8
Formatting
COURSES OUTLINE
Module 1: Overview of the Basics
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)
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
Module 7: More Functions
Date and time functions Text functions Database functions
Module 8: Formatting
Using auto formatting option for worksheets Using conditional formatting option for rows, columns and cells
This course will help to write macros in Excel using programming language Visual Basic Application (VBA). Recorded macro can edited to customize it to fulfill our requirement and one can directly go for coding to accomplish the required tasks. One can write his own functions and procedures, design forms and working with add-ins.
Program
Objectives
After completion of this program, the participants will be able to the following
Write macros to automate routine work Create functions and procedures Design and use forms for data entry Create add-ins to deploy at client machines Create simple reports
Audience
Program is scheduled for Advanced Microsoft Excel users. VBA is a programming language, requires aptitude for programming.
What is Function and Subroutine Writing Functions using VBA Editor Calling function in other workbook
Module
3: Recording Macros
Recording and running macro Storing macro in different books Creating buttons and assigning macro to it Creating My Menu
Module
4: The Visual Basic Editor
Editing macros using VBA Editor Understanding the project explorer Exploring windows properties Modifying code in code window Debugging code
Module
5: Programming Fundamentals
Variables, data types and constants Arrays Object Variables Conditional structures (If …. Then, Select Case) Logical Operators Looping constructs (For loops, Do loops, while… Wend) Built-in functions Creating Subroutines
Module
6: Excel Object Model
Working with objects – properties, events and methods Application objects Workbook objects, Worksheet objects and Range objects Charts objects Pivot table objects
Module
7: User Forms
Creating forms Form events Creating custom menus and toolbars Creating Add-ins
Course Duration
Fees (Indian RS.)
Fees (US $)
50 sessions of 1 hr. each
10,000
285
Management has full right tochange courses, contents, tuition fees without prior notice.
Click here to Register for
COMPUFIELDoffers a unique style of learning -
One computer : One student : One Instructor
The timing can be fixed based on our mutual convenience,
between 9.00 a.m. to 7.00 p.m.
Maximum Instructor to Student Ratio 1:4
Course duration in working days = Total sessions divided by number of sessions (hours) per day.
Sessions : Each session will be of 1 hour
No extra charges/refund for more/less time taken to complete the course.
Fees is for one course and it includes instructions, practicals,practice, project, preparation for exam (if any)
Lodging, boarding and travelling not included in the fees.
Overseas / local Students take 5 to 10 hours per day to complete their courses earlier.
Performing complex calculations more efficiently, using various Excel functions.
