Course Contents

Excel Basics with Formula writing

Introducing Excel

 What’s New in Excel 2007?

 Ribbon, Quick Access Toolbar, Live Preview, Galleries, Mini Toolbar, Enhance Screen Tips and Office Button

 Opening a worksheet

 Inserting & Deleting a worksheet

 Inserting & Deleting cells

 Naming & Renaming sheets

 Worksheet Data

 Understanding Excel’s Files

 Converting Files from Office 2003 to Office 2007 and vice versa

Managing Worksheets

 Inserting, Deleting, Copying and Moving data between sheets

 Modifying various Page Setup Options

 Formatting cells, rows, columns, numbers & Conditional Formatting

 Copying and Paste Special Options

Using Formulas & Functions

 Using Text functions like Left, Right, Mid, Concatenate, Upper, Lower, Proper, Len, Rept, Find, Search, Substitute, Replace and Trim

 Using Text to Column Feature

 Using Date & Time functions like Today, Now, Day, Month, Year, Workday and Networkdays

 Using Logical Functions like If, And, Or, Not and performing multiple conditions with Nested If.

 Using Mathematical Functions such as ABS, Rand, Randbetween, Round, Roundup, Rounddown, Sum, Sumif and Sumifs

 Using Statistical Functions such as Count, Counta, Countblank, Countif, Countifs, Sumif, Sumifs, Average, Averageifs, Max, Min, Small and Large

Writing Complex Formulas with Advance Functions

Using Lookup functionality

 Using Lookup and Reference Functions such as Lookup, Vlookup, Hlookup, Match and Index.Searching values on the basis of Exact and Appropriate relationship. Performing multiple Lookup value, Performing lookup in multiple fields, and many more.

Using Financial Functions

 Functions such as PV, FV, Rate, NPV, PPMT and IPMT

Working with Range and Naming Conventions

 Applying a Name to a cell or range of cells

 Modifying, Deleting and Modifying the names

Array Formulas

 Introducing Array Formulas

 Using Array Formulas in different situations

Using Advanced Excel Features

Playing with Data

 Using Inbuilt Lists and creating custom lists

 Using Sort to Arrange data alphabetically or through customized order

 Using Sort to Arrange data either vertically or horizontally

 Extracting data by using Custom Filters

 Adding Multiple conditions to extract data using Advance Filters

 Performing calculations on one field depending upon the value of the second field of the database using Subtotals

 Importing the data from different application to Excel

 Removing duplicate records from the database

 Linking the sheets and workbook through Hyperlinks

 Representing the Data graphically using through various Chart Types such as Column Chart, Bar chart, Line Chart, Pie chart and Scatter chart.

 Using Keyboard Shortcuts

Advanced Data Handling & Analyzing Techniques

Advance Data Handling

 Designing the Forms for the user using Data Validations

 Preparing dropdown lists to fill up a form

 Protecting and Sharing the Worksheets & Workbooks

 Consolidating the Data from different sheets using Data Consolidation

Analyzing Data by Data Analytical Tools

 Performing What If Analysis by using Data tables and Scenarios Manager

 Using Pivot Tables & Pivot Charts- Creating and Modifying a Pivot Table Layout, Creating Groups, Inserting a Calculated Field and Representing data through Pivot Charts

 Using Goal Seek and Solver

Macros & VBA Programming Intro

Introduction to Macros

 What is a Macro?

 Displaying the Developer Tab

 About Macro Security

 Saving Workbooks that contain macros

 Understanding different types of macros

Recording, Editing & Deleting of Macros

 Understanding how to record VBA Macros

 Examples of recording macros

 Examining, Testing and Editing macros

 Absolute versus Relative referencing while recording macros

 Assigning macro to a keyboard shortcut or a button

 Macros In Depth

 Inset Form controls (Button, combo box, spin button, option button etc.)

 Insert ActiveX controls (Labels, Pictures, Toggle buttons, scroll bars etc.)

Introduction to VBA Programming

 Understanding VBA

 Know how of VBA coding

 Basics of writing VBA Code

 Debugging errors

______