Microsoft Excel Training – Level 2

Testimonials

1. Advanced Calculations

Creating & copying formulas efficiently +

1. Shortcut to copy formulas
2. Shortcut to apply AutoSum
3. Calculating with Quick Analysis

Formulas with Absolute & Mix cell Reference Addresses +

1. Creating complex formulas using absolute cell references
2. Creating formulas using mixed cell references

2. CUSTOMIZED ADVANCED FORMATTING

Applying Conditional Formatting +

1. Setting conditional formatting
2. Using the rules manager windows
3. Applying highlight cells or top/bottom rules
4. Applying data bars, colour scales, or icon sets
5. Clearing rules

Data Validation +

1. Types of data validation
2. Setting data validation criteria
3. Entering the input message
4. Entering the error alert style & message
5. Using custom data validation
6. Clearing data validation

3. ADVANCED FUNCTIONS

Inserting Date & Time functions +

Conditional Logical Function +

1. Understanding the IF functions’ arguments
2. Using the IF functions
3. Working with nested IF functions
4. Using AND & OR functions

Using Lookup Functions +

1. Understanding the lookup functions’ arguments
2. Using VLookup & HLookup functions
3. Limiting the lookup value for exact match in unsorted data

Working with Financial Functions +

1. Understanding the financial functions’ arguments
2. Using PMT or PPMT functions
3. Using IPMT, PV or FV functions

4. WORKING WITH MULTIPLE WORKSHEETS & WORKBOOKS

Managing Data in Multiple Worksheets +

1. Entering data into multiple worksheets
2. Adding, copying, or moving worksheets
3. Grouping & ungrouping worksheets
4. Selecting & printing adjacent & non-adjacent worksheets

Consolidating & Summarizing by Linking Workbooks +

1. Linking information from different worksheets & workbook
2. Summarizing worksheets
3. Entering 3-D formulas across worksheets
4. Creating 3-D formulas linking workbooks
5. Updating links

5. WORKING WITH COMMENTS

Using Comments +

1. Inserting comments
2. Showing or hiding comments
3. Navigating comment commands
4. Editing, formatting or deleting comments
5. Shortcut to insert comments

6. PROTECTING WORKBOOKS

Protecting Workbook Files +

1. Protecting your workbook from unauthorized users access
2. Protecting or unprotecting the workbook structure
3. Setting or removing a password to open or modify a workbook

Protecting Cells & Formulas in Worksheets +

1. Locking or unlocking cells in worksheets
2. Protecting or unprotecting a worksheet
3. Protecting & hiding formulas in protected worksheets
4. Setting a password to edit or select specific cells & ranges

7. WORKING WITH DATABASES

Creating a Database +

1. Understanding basic database concepts
2. Creating a list & adding records manually
3. Using find & replace

Data Form +

1. Entering new records
2. Searching for records using data form
3. Updating or deleting records using data form

Sorting Data +

1. Sorting by single-level data
2. Sorting by multi-level data
3. Creating custom sort list

AutoFilter +

1. Enabling or disabling AutoFilter
2. Applying or removing the criteria
3. Filtering using more than one criterion
4. Customizing criteria
5. Applying multiple filters

Advanced Filter +

1. Setting the list range & defining the criteria range
2. Using comparison operators
3. Filtering using one condition or multiple conditions
4. Filtering the list in place or copying to another location

Subtotal +

1. Using subtotal to create outlines
2. Showing or hiding details using outline symbols
3. Using functions with subtotal
4. Inserting page brakes between groups
5. Removing subtotal

Grouping & Outlining Data +

1. Organizing information to group & outline
2. Applying auto outline
3. Grouping manually
4. Clearing outlines