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