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 usedworksheets. 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.

Session Plan

Using Mathematical Functions
The Basic SUM, COUNT, MIN, And MAX
AVERAGE, MODE, MEAN, And MEDIAN
SUMIF For Selective Adding Up
COUNTIF For Selective Counting
AVERAGEIF For The Mean Of Selected Cells
Multiple Criteria Within SUMIF, COUNTIF, And AVERAGEIF

IF Functionality
IF Syntax And Uses
Nesting The IF Statement
Use Of The AND Operator Within An IF
Use Of The OR Operator Within An IF
The NOT Operator Within AND And OR Statements
Display Cell Formulas In Another Cell

Performing Data Lookups
VLOOKUP: Syntax And Usage
VLOOKUP In Live Action
HLOOKUP: Variation On A Theme
Managing The Lookup Table

Further Mathematical Functions
Working With Time In Excel
Calculations Using Time
Useful Time And Date Functions
Rounding Decimal Places
MOD And INT Functions And Uses
Generate And Use A Random Number
Loan And Investment Calculations

Functions For Manipulating Text
LEFT And RIGHT: Text Manipulation
LEN And TRIM: String Extractions
FIND And MID: Text Functions Working Together
CONCATENATE: Building Strings From Multiple Cells Changing Case Functions
REPLACE And SUBSTITUTE: Two More String Manipulation Functions
Use Of CHAR Function For More Obscure Characters Formatting Numeric And Date Values Using TEXT
Keeping The Values Created By String Manipulation

Useful Data Functions
Using The MATCH Function
How The INDEX Function Works
Handling Out Of Range Index Requests
The CHOOSE Lookup Function
MATCH And INDEX Functions Working Together

PivotTables
What Is A PivotTable?
The New Recommended PivotTable Route
Creating Your Own PivotTables
Changing The Formatting And Formulas In PivotTable Summaries
Creating Multiple PivotTables On The Same Dataset
Moving And Deleting PivotTables
Making Use Of The Report Filter Options
Sorting The PivotTable Columns
Refreshing A PivotTable

PivotCharts
Creating A PivotChart
Changing The Fields Used In A PivotChart
Formatting The PivotChart
Changing The PivotChart Type
Filtering A PivotChart
Hiding The PivotChart Buttons
Moving And Deleting PivotCharts

Goal Seek And Solver What-If Analysis
Using Goal Seek
Activating The Solver Add-In
Using Solver To Complete A What-If
Adding Constraints To Solver

Macros
What Is A Macro
Creating And Running Your First Macro
Saving Workbooks With Macros
Macro Security Settings For Workbooks With Macros
The Personal Macro Workbook
Deleting Macros
Use Of Relative Or Absolute Referencing
Trigger A Macro With A Keyboard Shortcut
Formatting With A Macro
Switch Scenarios And Views With Macros
Use Of Worksheet Buttons To Trigger Macros
Customizing Form Buttons And Other Shape Triggers
Assigning Macros To Ribbon Icons
Create Your Own Ribbon
View And Edit Macro Code

Registration Fees

One participant Rs.6,000/- Plus Service Tax. which includes lunch, tea, course material etc.
For three or more participant from the same organization Rs.5,500/- Plus Service Tax per participant.

Registration Procedure

1. Please send confirmation with your Full name ,mobile number,email-id through mail.
2. The seats will be reserved after receiving your mail and due mail confirmation will be sent to your mail ID.
3. Payment by cheque/DD in advance in favour of “Synapsetech eServices Pvt Ltd”. Cheque/Online Transaction details to be sent through email to our Program Cordinator Ms Mohini Tyagi
Seats are confirmed only after receipt of payment.
Please Send the Cheques to our office address D-321 Sector 10 Noida UP 201301
4. Early registration will be appreciated