Microsoft Excel Training – Level 2



Excel

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



2. Advanced Calculations


  • Absolute Cell Reference & Improving Calculation Performance +


    1. Creating & copying formulas efficiently
    2. Switching between relative & absolute or mix cell references
    3. Creating complex formulas using absolute cell references


  • Naming Cells or Ranges +


    1. Using the name manager
    2. Creating & defining names
    3. Modifying or deleting named ranges
    4. Using a named range
    5. Creating names from selection


  • Subtotal Functions +


    1. Using the different types of subtotal
    2. Creating a subtotal function
    3. Using subtotal to dynamically summarize data


  • 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


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



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



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


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


Register Now