Advanced Excel

Excel 2007 & 2010 Quick Overview
Difference between Excel 2003, 2007 and 2010, Use of Excel, its boundaries & features

Basic Formula
Formulae that Add/Subtract/Multiply/Divide • BODMAS/Formula Error Checking • The Sum Function

Absolute Referencing
Problems with Absolute/Relative Cell Referencing, Creating Absolute/Mixed References

LOOKUP Functions
The VLOOKUP/HLOOKUP Functions

PivotTables
Creating, Formatting Simple PivotTables • Page Field in a PivotTable • Formatting a PivotTable • Creating/Modifying a PivotChart

Logical Functions
IFs and Nested IF Functions • Using AND/OR/NOT Functions

Statistical Functions Using The SUMIF/COUNTIF Functions • Using The AVERAGE/COUNT/LARGER/SMALLER Functions

Pivot Tables – Advance
Adding new calculated Fields / Items • Changing the Summary Function • Consolidate Pivot table

LOOKUP Functions – Advance
MATCH with VLOOKUP Functions • INDEX & MATCH Functions • OFFSET/ INDIRECT functions

Logical Functions - AdvanceIf Loop and Nested IF Loop Functions • Using IF/ISERROR Functions

Chart Data Techniques
The Chart Wizard • Chart Types • Adding Title/Legends/Lables • Printing Charts • Adding Data to a Chart • Formatting/Renaming/Deleting Data Series • Changing the Order of Data Series

Date/Time Functions
Using The Today • Now & Date Functions • Using The Datedif/ Networkdays/ Eomonth Functions • Using The Weeknum Functions • Using The Edate/ Networkdays.Intl/ Weekdays.Intl Functions.

Text Functions Using
The Mid/ Search/ Left/ Right Functions • Using The Trim/ Clean/ Upper/ Lower Functions • Using The Subsitute/ Text Functions • Using The Trim/ Clean/ Proper/ Dollar Function

Validations
Input Messages / Error Alerts/ Drop-Down Lists • Conditional Formatting

Advanced Filters
Extracting Records with Advanced Filter • Using Formulas In Criteria

Advanced Sorting
Sorting by Top to Bottom / Left to Right • Creating / Deleting Custom List • Sort by using Custom List

Hyper / Data Linking
Hyper linking data, within sheet / workbook • Linking & Updating links between workbooks & application

Math & Trigonometry Functions
Using SUMPRODUCT Functions • Using FLOOR/ CEILING/ MROUND/ MOD/ QUOTIENT Functions

Summarizing Data
Creating Subtotals/Nested Subtotals • SUBTOTALS Formula

Outlining
Creating/Working with an Automatic/Manual Outline • Grouping / Ungrouping

Consolidation
Consolidating Data with Identical/Different Layout

Using Auditing Tools
Displaying/Removing Dependent & Precedent Arrows • Evaluate Formula - Step IN/ Step Out

Custom Views
Creating Custom Views • Displaying Custom Views • Deleting Custom Views

Sharing and Protecting Workbooks
Sharing Workbooks & Tracking Changes • Protecting sheets / workbooks / Files

Importing & Exporting Data
Importing Data from Database/ Text Files/ Web • Exporting Data • Changing External Data Range