Advanced Excel

Course Description:

You have a need to streamline repetitive tasks and display spreadsheet data in more visually effective ways. In this course, you will use Microsoft Office Excel to streamline and enhance your spreadsheets with templates, charts, graphics, data analysis and formulas.

Demonstration will be based on MS Office version.

 

Prerequisites:

Participant should familiar with basic excel that contain few functions like sum, max, min etc, formatting like borders, fonts, merge, etc, saving, printing techniques.

Participants need to bring their own laptop with MS Office installed!

 

Delivery Method:

Instructor led, group-paced, classroom-delivery learning model with structured hands-on activities.

 

The participants are requested to bring their own laptop for better understanding and practical experience.  The participants may also bring with them live data pertaining to their organisation, so that these can be studied during the workshop, as to how it can be worked out with better solutions.

 

Contents:

Chapter 1: Introduction to Microsoft Excel 2007

·         About Excel

·         Components of the Excel Window

·         Interacting with Excel

·         Changing Default Settings

 

Chapter 2: Cell References and Range Names

·         Why Use Different Types of References?

·         Types of Cell Reference:

·         Named Ranges

·         Exercise

 

Chapter 3: Working with Formulas and Functions

·         Using Formulas in a Worksheet

·         Array Formulae

·         Using functions

·         IF function

·         Nested IF

·         IF with AND

·         IF with OR

·         IF with NOT

·         Lookup Functions

·         V-lookup

·         H-lookup

·         Making V-Lookup Dynamic

·         Index

·         Index-Match

·         Exercise

 

Chapter 4: Data Validation

·         Setting Data Validation Rules

·         Methods of Data Validation

·         Exercise

 

Chapter 5: Protection

·         Protecting a Worksheet by using Passwords

·         Protecting a Workbook

·         Protecting Part of a Worksheet

·         Password Protecting a File

·         Case Study

 

Chapter 6: Sorting a Database

·         Simple Sort

·         Multilevel Sort

·         Customized Sorting

 

Chapter 7: Filtering a Database

·         Auto Filter

·         Number, Text or Date Filters

·         Filtering a List using Advanced Filter

·         Filtering Unique Records

·         Exercise

 

Chapter 8: Subtotals

·         Display Subtotal at Single Level

·         Displaying Nested Subtotals

 

Chapter 9: Pivot Tables

·         Examining Pivot

·         Tables

·         Format a PivotTable report

·         Calculate the Percentage of the field

·         Top/ Bottom Report

·         Group Items in a PivotTable

·         Create a Graph using Pivot Data

 

Chapter 10: Conditional formatting

·         Conditional Formatting using Cell Values (Column Based Conditional Formatting)

·         Conditional Formatting using Formula (Record Based Conditional Formatting)

·         Database Case Study

 

Chapter 11: What-if-Analysis Tools

·         Goal Seek

·         Projecting Figures Using a Data Table

·         What-If Scenarios

·         Merge Scenarios from Another Worksheet

·         Protecting Scenarios

 

Chapter 12: Working with multiple worksheets, workbooks and applications

·         Creating Links between Different Worksheets

·         Creating links between different software

·         Workgroup collaboration

·         Merging workbooks

·         Tracking changes

·         Creating Hyper Link

 

Chapter 13:-Working with Charts

·         Creating Charts using Chart Tools

·         Including Titles and Values in Charts using Chart Tools

·         Formatting charts

·         Charts for my Data

·         Chart Templates

·         Exercise

 

Chapter 14: Macros

·         Trust Center settings

·         Creating a macro

·         Recording a Macro

·         Running a Macro Using Menu Command

·         Writing a macro

·         Creating a sub procedure

·         Creating a function

·         Assigning a Macro to a Button

·         Final Assignment

·         Shortcuts in Excel 2007

 

Medium of Instruction: English, Hindi & Marathi.

 

Course Information:

 

Investment (Donation): Rs. 2,400/- + Service Tax 14% per person.

 

Incentive: Rs. 2,200/- + Service Tax 14% per person for a group of Three or more Members from the same Organization.

 

Fees include participation, course material (Hard copies or Soft copies), Breakfast, Lunch and tea / coffee.

 

Cheques, DD, NEFT or net banking to be drawn in favour of ‘Indian Institute of Training & Development’ (IITD), Pune.

 

Registration Details:

√ Reserve your seats today giving details like: Name, Designation & Contact Numbers of the participants on E mail: iitdpune@gmail.com; rajiv@iitdpune.com or Call Rohan Mhatre on Mo: 83084 59802/ Rajiv Kolhe on Mo: 9822089898. Register NOW!

 

√ Advance payment of fees by DD/Cheque/Cash to be drawn in favour of ‘Indian Institute of Training & Development’. Please note our Service Tax No. BDNPK3328J SD001. PAN No. BDNPK3328J.

 

√ Participation fee is non-refundable/ non-adjustable against any other programme of IITD, but change in nomination(s) is accepted.

 

√ We request you to email us NEFT transaction details at EM: iitdpune@gmail.com

 

Certification:

Certificate of Participation would be provided to all the participants.