Advance Excel Course

Course Content are as follows:

Spreadsheet basics

• Creating,
• editing,
• saving
• printing spreadsheets

Working with functions & formulae

• Graphically representing data : Charts & Graphs
• Analyzing data : Data Menu, Subtotal, Filtering Data
• Formatting worksheets , Securing & Protecting spreadsheets

Formulas & Macros Formulas:

• Use the Function Wizard, Common functions (AVERAGE, MIN, MAX, COUNT,COUNTA, ROUND, INT)
• Nested functions , Name cells /ranges /constants
• Relative, Absolute, Mixed cell references : >,<,= operators
• Logical functions using IF, AND, OR, NOT
• The LOOKUP function , Date and time functions , Annotating formulas

DATA Analysis:

• Sub Total Reports, Auto Filter
• Password Protecting Worksheets
• Linking Multiple Sheets
• Linking Between Word/Excel/Ppt
• Functions:- LOOKUP, VLOOKUP, HLOOKUP, COUNTIF, SUMIF
• What-if-analysis, GOAL SEEK
• Absolute Cell References
• Name Manager

Naming cells and ranges

• Creating and defining names
•  Making a name list
•  Advanced technique of using names in formulas
•  Using Name Manager
•  Navigating spreadsheet with names

Excel Pivot Tables

• Create an Excel Template
• Data Forms in Excel 2007/2010
• Drop Down Lists in Excel
• Add your own Error Messages
• Excel and Web Integration
• Hyperlinks in Excel
• Object Linking and Embedding

Database

• The database components
• Using Excel Form feature
• Inputting data
• Deleting data
• Finding records
• Using menu commands to find records

Advanced data sorting and subtotal

• Multi-level sorting
• Restoring data to original order after performing sorting
• Sort by icons
• Sort by colours
• Multi-level subtotal

Managing documents with workbooks

Consolidation with several worksheets

• Consolidating and combining several spreadsheets using the operation addition, subtraction
• Synchronizing the consolidated table with the source data

Data table

• One-Input table
• Two-Input table

Lookup table

• Lookup()
• Vlookup()
• Hlookup()
• Application of exact match and approximate match
• Creating an order form using vlookup function

Document protection

• Files protection
• Protecting cells/documents
• Unprotecting documents

File linking

• Paste link

Filter and advanced filter

• Defining single and multiple criteria
• Combining search criteria
• Deleting criteria
• Extracting records

Pivot table

• Steps to create a pivot table
• Creating pivot table from Excel
• Consolidating data from multiple ranges into a pivot table

Conditional format

• Highlighting data using cell colours, font colours
• Highlighting data using icons

Data validation

• Define the data input type
• Define the warning message
• Define the error message
• Circle invalid data

Using Scenario Manager

• Defining your own scenario
• Preview the result of scenario
• Editing a scenario

What-If Analysis

•  How to apply What-If Analysis

Inserting a hyperlink to a workbook

•  Creating a hyperlink
•  Editing a hyperlink
•  Creating a menu system using hyperlink

Creating a pull down box to facilitate the data entry process
Creating and using Macros