Advance MS Excel 15 May 2015

This program is designed for executives who want to build on Microsoft Excel skills. This course will cover all aspects of creating spreadsheets, performing calculations, formatting and using page setup for printing.Getting Excel to make decisions using the ‘If’ function and conditional formatting, You’ll learn how to create templates for commonly used worksheets. This course will also enable you to create, build and customise graphs.

Ms-B

15 May 2015India Habitat Center

Course Overview

This program is designed for executives who want to build on Microsoft Excel skills. This course will cover all aspects of creating spreadsheets, performing calculations, formatting and using page setup for printing.Getting Excel to make decisions using the ‘If’ function and conditional formatting, You’ll learn how to create templates for commonly used worksheets. This course will also enable you to create, build and customise graphs.

In this workshop you will also explore 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.

Course Objectives

Excel Basics

Moving About the Workbook
Simple Data Entry and Selecting Ranges
Saving Workbook Formats
Copying & Filling
Using Fill for Quick Copying and Copying Cell(s) to One/Many Cells & Worksheets
Editing Data

Overwriting and Editing Cell Contents, Editing Formulae or Functions or Using Undo/Redo/Search & Replace

Adjusting a Worksheet
Basics like Inserting/Deleting Cells/Inserting/Deleting/Resizing Rows/Columns
Copying/Moving/Renaming Worksheet(s)
Inserting/Deleting/Grouping Worksheets
Hiding & Unhiding Worksheets

Formulae & Working with Functions

Formulae that Add/Subtract/Multiply/Divide
BODMAS/Formula Error Checking
The Sum Function
Summing Non-Contiguous Ranges •Count/ Average/Maximum/Minimum Functions
Paste Special to Copy Formulae/Formats/Validations • Paste Special to Add/Multiple & Transpose

Formatting in Excel

• Formatting Numbers/Dates/Currency
• Formatting for Effect Bolding/ Italics/ Underling
• Cell/Background Colours
• Cell Alignment

Working and Viewing Worksheets

Hiding/Unhiding Columns/Rows • Freezing Panes/Splitting Windows
Absolute Referencing
Problems with Absolute/Relative Cell Referencing, Creating Absolute/Mixed References
Define Names & Lables
Creating/Deleting Labels/ Names • Creating/Using Names in Formulas/ Across Workbooks

Working with advance Functions

Writing conditional expressions (using IF)
Using nested IFs
Using logical functions (AND, OR & NOT)
Using lookup and reference functions (VLOOKUP & HLOOKUP)
Limitation of Vlookup and how to avoid it by using INDEX &INDEX

Data Validation

Creating Input Messages and Error Alerts
Specifying a valid range of values for a cell
Specifying a list of valid values for a cell
Creating list based on other sheet data(new features of Excel 2007/2010)
Specifying custom validations based on formula for a cell
Conditional Formating

Sorting and Filtering Data

Sorting tables
Using multiple-level sorting
Using custom sorting
Filtering data for selected view (AutoFilter)
Using advanced filter options
Extracting/removing duplicate record from database

Working with professional Reports

Creating subtotals
Multiple-level subtotals
Creating Pivot tables
Using Slicer in Pivot table(new features)
Using advance options in Pivot table(with new features of Excel 2007/2010)
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

Working with PowerPivot(new features in Excel 2007/2010)

Introducing PowerPivot feature in Excel
Importing backend data
Using validation while importing data
Importing Excel data
Working with 64bit OS
Working with huge no of Rows

More Functions

Date and time functions(TODAY, NOW, YEAR, MONTH, DAY & DATEDIF)
Text functions(TEXT, LEFT, RIGHT, MID & CONCATENATE etc)
Statistical function(COUNT, COUNTA, COUNTBLANK & COUNTIF)

Working with Styles & Formatting

Cell Styles
Creating lists using Table
Formatting the structure of a list
Conditional Formatting
Using formula/function in Conditional Formatting
Using large no of icons and bars in Conditional Formatting(new features in Excel 2007/2010)

What-If Analysis using Financial Function (PMT,PPMT & IPMT)

Using goal seek
Using data tables
Creating and editing scenarios

Working with Charts and Templates

Inserting Chart
Playing with different options and different type of chart
Using Sparklines, i.e. inserting chart in single cell(new features of Excel 2010)
Creating Templates in Excel

Who should attend

This Microsoft Excel training course has been developed for executives /Managers working in organisations wanting to use more complex functions within MS Excel.

Timing of the Course

On Day 1 registration is between 9.15-9.25 am and the training begins at 9.30 am finishing at 5.30 pm.

Meet The Faculty

Ms Madhu Bahl

Some people are borne to teach as it’s their DNA – a desire to teach what they know with a larger audience and Madhu Bahl exemplifies the same. She has a tenacious commitment and passion to train/teach.Madhu is an accomplished and seasoned professional who after completing her Engineering degree in Electronics and Telecommunications has been in the Teaching profession as a Corporate Trainer as well as Visiting faculty to students in various MBA institutes. Successfully developed skills in understanding, designing, implementing and training on various systems with good understanding of the customer needs.

She brings 22 years of progressive teaching experience and has expertise in the IT and related subjects.

She has taught and trained in IT related courses to a wide range of corporate audience. As a Corporate trainer she has trained at NTPC, SAIL, Aricent, Vanderlande Ayurvet Shoppers Stop to name a few.

As a Trainer and Training Consultant she has been associated with Corporates and Training Companies like NIIT ,Strategic Hr and Training and Various Polytechnics.

She is a visiting faculty at management campuses like IIPM, ICFP, NBA, WLC, IWSB, ASKIME, MAII.

She was adjudged Best lecturer by Multiple Reputed Management institutes on for the year 2009/10.

Registration Fees

One participant Rs.6,000/- Plus Service Tax. which includes lunch, tea, course material etc for One Day.

For Three or more participant from the same organization Rs. 5,500 + Service Tax.

(It is Mandatory for every participant to bring Laptop with MS Excel Version 2007/2010 in their laptop for Training workshop.)

Workshop Date/Venue

15th May 2015 At India Habitat Centre,Lodhi Road,New Delhi 110003.

(It is Mandatory for every participant to bring Laptop with MS Excel Version 2007/2010 in their laptop for Training workshop.)

Registration Procedure

1. Please send confirmation with your Full name ,mobile number,email-id through mail before 24th July 2015.

2. The seats will be reserved after receiving your mail and due mail confirmation will be sent to your mail ID.

3. It is Mandatory for every participant to bring Laptop with MS Excel Version 2007/2010 in their laptop for Training workshop.

4. Payment by cheque/DD in advance in favour of “Strategic HR and Training”. Cheque/Online Transaction details to be sent through email to our Program Coordinator Ms Roquia Siddiqui

Seats are confirmed only after receipt of payment.

Please Send the Cheques to our office address D-321 Sector 10 Noida UP 201301
5. Early registration will be appreciated
(It is Mandatory for every participant to bring Laptop with MS Excel Version 2007/2010 in their laptop for Training workshop.)