Excel Advanced Course Outlines
Prerequisits |
Topics |
Duration: 1 Day (8:30 am - 3:30 pm)
Objective: To create spread sheets with complex formulas. Manipulate and analyse data. Essential: An awareness of keys on keyboard e.g.: Shift, space bar, and Enter key. Completed Introduction and Intermediate or equivalent. Learning Outcome: By the end of the day you will be able to create complex functions, manipulate data. |
Module 1 Functions Refresher basic IF SumIf, CountIf Nested If Functions IF with And/Or/Not IsBlank, IsText, IsError.
Module 2 Complex Functions Lookup, Refresher Vlookup, Hlookup Choose ,Index, Match, IFError. Module 3 Clean up Functions Using Text to Column Upper case Lower case Left/Right Trim Len Concatenate Text. Module 4 Data Analysis Goal Seek Solver Scenarios Data Tables. Module 5 Workbook Tools Validation Rules Creating List Setting Error Alerts Circle Invalid Data Track Changes Create a Track Changes history report. Module 6 Working with grouping Subtotalling Totalling by Sum and Count Outlining. Module 7 Pivot Tables Create Table Creating Pivot Tables Group data Work with Value field settings Create Calculated fields Creating a Pivot Chart. Module 8 Macros Creating Macros Viewing Macro Code Creating a Message box in VBA Code Assign a Macro to a Command button Creating Relative/Absolute Macros Store Macros Global/Local. Excel Intermediate |