Microsoft Excel Training – Level 2


CUSTOMIZED ADVANCED FORMATTING

Applying Conditional Formatting

  • Setting conditional formatting

  • Using the rules manager windows

  • Applying highlight cells or top/bottom rules

  • Applying data bars, colour scales, or icon sets

  • Clearing rules

Data Validation

  • Types of data validation

  • Setting data validation criteria

  • Entering the input message

  • Entering the error alert style & message

  • Using custom data validation

  • Clearing data validation

Advanced Calculations

Absolute Cell Reference & Improving Calculation Performance

  • Creating & copying formulas efficiently

  • Switching between relative & absolute or mix cell references

  • Creating complex formulas using absolute cell references

Naming Cells or Ranges

  • Using the name manager

  • Creating & defining names

  • Modifying or deleting named ranges

  • Using a named range

  • Creating names from selection

Subtotal Functions

  • Using the different types of subtotal

  • Creating a subtotal function

  • Using subtotal to dynamically summarize data

Conditional Logical Functions

  • Understanding the IF functions’ arguments

  • Using the IF functions

  • Working with nested IF functions

  • Using AND & OR functions

Using Lookup Functions

  • Understanding the lookup functions’ arguments

  • Using VLookup & HLookup functions

  • Limiting the lookup value for exact match in unsorted data

Working with Financial Functions

  • Understanding the financial functions’ arguments

  • Using PMT or PPMT functions

  • Using IPMT, PV or FV functions

WORKING WITH MULTIPLE WORKSHEETS & WORKBOOKS

Managing Data in Multiple Worksheets

  • Entering data into multiple worksheets

  • Adding, copying, or moving worksheets

  • Grouping & ungrouping worksheets

  • Selecting & printing adjacent & non-adjacent worksheets

Consolidating & Summarizing by Linking Workbooks

  • Linking information from different worksheets & workbook

  • Summarizing worksheets

  • Entering 3-D formulas across worksheets

  • Creating 3-D formulas linking workbooks

  • Updating links

WORKING WITH COMMENTS

Using Comments

  • Inserting comments

  • Showing or hiding comments

  • Navigating comment commands

  • Editing, formatting or deleting comments

  • Shortcut to insert comments

PROTECTING WORKBOOKS

Protecting Workbook Files

  • Protecting your workbook from unauthorized users access

  • Protecting or unprotecting the workbook structure

  • Setting or removing a password to open or modify a workbook

Protecting Cells & Formulas in Worksheets

  • Locking or unlocking cells in worksheets

  • Protecting or unprotecting a worksheet

  • Protecting & hiding formulas in protected worksheets

  • Setting a password to edit or select specific cells & ranges

WORKING WITH DATABASES

Creating a Database

  • Understanding basic database concepts

  • Creating a list & adding records manually

  • Using find & replace

Data Form

  • Entering new records

  • Searching for records using data form

  • updating or deleting records using data form

Sorting Data

  • Sorting by single-level data

  • Sorting by multi-level data

  • Creating custom sort list

AutoFilter

  • Enabling or disabling AutoFilter

  • Applying or removing the criteria

  • Filtering using more than one criterion

  • Customizing criteria

  • Applying multiple filters

Advanced Filter

  • Setting the list range & defining the criteria range

  • Using comparison operators

  • Filtering using one condition or multiple conditions

  • Filtering the list in place or copying to another location

Subtotal

  • Using subtotal to create outlines

  • Showing or hiding details using outline symbols

  • Using functions with subtotal

  • Inserting page brakes between groups

  • Removing subtotal

Grouping & Outlining Data

  • Organizing information to group & outline

  • Applying auto outline

  • Grouping manually

  • Clearing outlines