IITD Presents 1 Full Day Interactive workshop on  Advanced Microsoft Excel

IITD Presents 1 Full Day Interactive workshop on Advanced Microsoft Excel

 

About The Event

Introduction

Organizations today have a need to streamline repetitive tasks and display spreadsheet data in more visually effective ways. Data analysis & representation is indispensible to facilitate decision making and strategy development.  It’s very challenging to turn a huge amount of data to 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 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, for better understanding and practical experience.  The participants may also bring with them live data pertaining to their organization, 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. 

Prerequisites

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

Module 

Topic

­Module 1

Functions

Module 2

Working with  Excel Functions

Module 3

Security & Sharing Features in Excel

Module 4

Dynamic Formatting

Module 5

Sorting and Filtering Data

Module 6

Specialized Functions

Module 7

Working with Reports

Module 8

Presenting Data using charts

Module 9

Using Macros

Module 10

More Functions

Module 1: Working with Functions

 

1)    Writing conditional expressions (using IF)

 

a)    Using if with single condition

 

b)    Using if with multiple conditions

 

2)    Using logical functions (AND, OR)

 

a)    Using And Function

 

b)    Using Or Function

 

3)    Using lookup and reference functions (VLOOKUP, HLOOKUP, MATCH, INDEX)

 

a)    Using Vlookup Function

 

b)    Using Hlookup Function

 

c)    Using Match Function

 

d)    Using Index Function

 

e)    Using Index, Match together

 

Module 2: Security & Sharing Features

 

4)    Protecting and un-protecting worksheets and cells

 

a)    Protecting Sheet with password

 

b)    Unprotecting Sheet

 

5)    Hiding formulas

 

a)    Hiding Formulas

 

6)    Saving files with passwords

 

a)    Adding password to a file

 

b)    Removing Password

 

7)    Tracking changes

 

a)    How to activate track changes

 

b)    How to track changes in the workbook

 

8)    Merging workbooks

 

Module 3: Dynamic Formatting

 

9)    Applying auto formatting option to worksheets

 

a)    Applying auto format

 

10) Applying conditional formatting to cells

 

a)    Applying conditional formatting to cells

 

b)    Applying conditional formatting to rows

 

c)    Applying conditional formatting to worksheets

 

Module 4: Sorting and Filtering Data

 

11) Sorting Data

 

a)    Sorting tables

 

b)    Using multiple-level sorting

 

c)    Using custom sorting

 

12) Filtering data for selected view (AutoFilter)

 

a)    Filtering Numbers

 

b)    Filtering Text

 

c)    Filtering Date

 

13) Using advanced filter options

 

a)    Filtering Unique Values

 

b)    Filtering on multiple criteria

 

Module 5: Specialized Functions

 

14) Using conditional Aggregate Functions like

 

a)    Dsum,

 

b)    Dmax,

 

c)    Dmin etc….

 

Module 6: Working with Reports & Charts

 

15) Subtotal on data

 

a)    Creating subtotals

 

b)    Multiple-level subtotals

 

16) Pivot Table

 

a)    Creating Pivot tables

 

b)    Formatting and customizing Pivot tables

 

c)    Using advanced options of Pivot tables

 

d)    Grouping data

 

e)    Sorting Data in pivot

 

f)    Filtering data in pivot

 

g)    Adding calculated field

 

h)    Removing Subtotal

 

i)     Removing Grand Total

 

j)     Types of Pivot Report

 

k)    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

 

a)    Create a Chart

 

b)    Modify Charts

 

c)    Format Charts

 

d)    Create a Chart Template – Combination chart

 

Module 8: Using Macros

 

21) Record Macro

 

a)    Recording and executing macros

 

b)    Understanding different types of references in macros

 

c)    Assigning macros to toolbars or menu items

 

22) Editing Using VBA

 

a)    Editing macros using VBA Editor

 

b)    Writing function using VBA Editor

 

Module 9: More Functions

 

23) Date and time functions

 

a)    Date Function

 

b)    Dated if Function

 

c)    Edate Function

 

d)    EOMonth Function

 

e)    Time Function

 

f)    Finding Time Difference

 

24) Text functions

 

a)    Proper Function

 

b)    Upper

 

c)    Lower

 

d)    Len

 

e)    Concatenate

 

25) Financial Functions

 

a)    Pmt

 

b)    Fv

 

Module 10: What-If Analysis

 

26) Using goal seek

 

27) Using data tables

 

28) Creating and editing scenarios

 

Delivery Method

 

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

 

IITD Expert Faculty

Masters in Computer Management & Masters in Personnel Management, Microsoft Certified Office Specialist Master.

She is an IT and Soft Skills Trainer with 8+years of experience in Corporate trainings. She has trained over 10000 students and has been associated with more than 200 corporate organisations.

She has got a lot of appreciation for her technical knowledge and excellent delivery by various clients such as Infosys, ICAI, Indian Railways Institute of Civil Engineering Pune, INS Hansa Naval Aviation Base Vasco Goa, Zensar, Cybage, Emerson Climate Technologies, Emphasis.etc.

Her area of expertise is Advance Excel, Excel VBA, Microsoft Project Management, DBMS, SQL, MS Access Advance Word, Advance Power point, Auditing via Excel, Tally ERP, MS Outlook.

Medium: English, Hindi & Marathi 

 

Invite friends

Contact Us

Page Views : 40

Venue Map