Our 1 day Microsoft Excel Advanced course is suitable for those with a sound working knowledge of Excel who wish to progress to the most complicated functions and features.
Prerequisites
A working knowledge of Excel or our Introduction to MS Excel course.
Benefits
§ At the end of this course you will have a complete understanding of the higher functions of Excel.
§ You will be able to perform the most advanced calculations and functions, manipulate data using pivot tables and ‘advanced’ analysis, record macros and integrate with other office applications.
§ You will then be qualified to progress to our Excel VBA course.
Course Content
Advanced Functions
§ IF Function & Nested IF Statements
§ And & Or Functions
§ Lookup Functions (Horizontal And Vertical Lookup
§ Subtotals
§ Conditional Formatting
3D Ranges
§ Create & Maintain (Within Worksheets In a Single File & Between External Files)
§ Consolidate Data (via 3D Ranges & Using The Consolidate Tool)
Pivot Tables
§ Create Pivot Table
§ Work With Row/ Column Fields
§ Data Fields
§ Customise Table Display
Organising Workbooks
§ Create Templates
§ Protect Ranges & Worksheets
Data
§ Enter & Select Records Using The Input Form
§ Sort Data Into Lists
§ Filter Data - Autofilter, Advanced Filter
§ Validation Rules
Goal Seeking
§ Solver
§ Goal Seeking With Charts
§ What-If
Scenarios
§ Create Scenarios
§ Create Summary Report
§ Merge Scenarios
Protect Scenarios
Macros
§ Create Macros – Record & Modify the VBA Code Generated
§ Relative Referencing
§ Assign To Toolbars/ Keyboard
§ Make Available To All New Documents/ Current Document Only
Outlines
§ Group Row/ Column Data To Create Outline Levels Based On Worksheet Formulae
§ Display Summary Rows And Summary Styles
Custom Views
§ Create, Modify, Delete
§ Include Hidden Rows, Columns, Filter Settings
|
Excel Adv (1 Day)
