Loading Events
This event has passed.

Introduction:
Organisations today have a need to streamline repetitive tasks and display spreadsheet data in more visually effective ways. Data analysis & representation is indispensable to facilitate decision making and strategy development. It’s very challenging to turn a huge amount of data into useful information without the help of Excel.

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 the MS Office version.

Prerequisites:
Participants should be familiar with basic excel that contains 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, 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.

This program is for anyone who is 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, use various Excel functions.
Organizing and analyzing large volumes of data.
Creating MIS reports.
Designing and using templates.
Consolidating and managing data from multiple workbooks.

Delivery Method:
Instructor led, group-paced, learning model with structured hands-on activities.

Contents:
Module 1: Working with Functions
1) Writing conditional expressions (using IF)
Using if with single condition
Using if with multiple conditions

2) Using logical functions (AND, OR)
Using And Function
Using Or Function

3) Using lookup and reference functions (VLOOKUP, HLOOKUP, MATCH, INDEX)
Using Vlookup Function
Using Hlookup Function
Using Match Function
Using Index Function
Using Index, Match together

Module 2: Security & Sharing Features

4) Protecting and unprotecting worksheets and cells
Protecting Sheet with password
Unprotecting Sheet

5) Hiding formulas
Hiding Formulas

6) Saving files with passwords
Adding password to a file
Removing Password

7) Tracking changes
How to activate track changes
How to track changes in the workbook

8) Merging workbooks

Module 3: Dynamic Formatting

9) Applying auto formatting option to worksheets
Applying auto format

10) Applying conditional formatting to cells
Applying conditional formatting to cells
Applying conditional formatting to rows
Applying conditional formatting to worksheets

Module 4: Sorting and Filtering Data

11) Sorting Data
Sorting tables
Using multiple-level sorting
Using custom sorting

12) Filtering data for selected view (AutoFilter)
Filtering Numbers
Filtering Text
Filtering Date

13) Using advanced filter options
Filtering Unique Values
Filtering on multiple criteria

Module 5: Specialized Functions

14) Using conditional Aggregate Functions like
Dsum,
Dmax,
Dmin etc….

Module 6: Working with Reports & Charts

15) Subtotal on data
Creating subtotals
Multiple-level subtotals

16) Pivot Table
Creating Pivot tables
Formatting and customizing Pivot tables
Using advanced options of Pivot tables
Grouping data
Sorting Data in pivot
Filtering data in pivot
Adding calculated field
Removing Subtotal
Removing Grand Total
Types of Pivot Report
Pivot charts

17) Consolidating data from multiple sheets and files using Pivot tables

18) Using external data sources

19) Using data consolidation feature to consolidate data

Module 7: Presenting Data Using Charts

20) Chart Tools
Create a Chart
Modify Charts
Format Charts
Create a Chart Template – Combination chart

Module 8: Using Macros

21) Record Macro
Recording and executing macros
Understanding different types of references in macros
Assigning macros to toolbars or menu items

22) Editing Using VBA
Editing macros using VBA Editor
Writing function using VBA Editor

Module 9: More Functions

23) Date and time functions
Date Function
Dated if Function
Edate Function
EOMonth Function
Time Function
Finding Time Difference

24) Text functions
Proper Function
Upper
Lower
Len
Concatenate

25) Financial Functions
Pmt
Fv

Module 10: What-If Analysis

26) Using goal seek

27) Using data tables

28) Creating and editing scenarios

Benefits of this Advanced Excel Workshop:
Perform complex calculations more efficiently, using various Excel functions.
Create Dashboards and MIS reports.
Consolidate and manage data from multiple workbooks.
Tips & Tricks

IITD Sr. Expert Faculty:
Highly experienced faculty from Indian Institute of Training & Development will conduct this training. The training sessions will provide participants an opportunity to discuss specific problems with the expert faculty.

Medium: English/ Hindi/ Marathi

Fees and Registration:
Rs. 3,000 + GST 18% per person.
Rs. 2,800 + GST 18% per person for a group of Three to Four participants.
Rs. 2,600 + GST 18% per person for a group of Five or more participants.
Kindly provide details for registration to Email: iitdpune@gmail.com & rajiv@iitdworld.com; Name of the Program, name of the participants, company name, GST no., contact details.

Certification:
Digital Certificate of Participation will be provided to all the participants.