Objectives:
This course introduces some of the more advanced features of Excel, to enable the delegate to make the most effective use of the software, including the use of linked Workbooks. On completion of the course, the delegate will be familiar with most of the main features of Excel, including several features for Data Analysis.

Designed for -

This course is aimed at delegates who are familiar with the essentials of Microsoft Excel covered in the Level 1 and Level 2 courses and wish to extend their knowledge to make the most of the facilities available in the software.

Description:

The course covers some of the more advanced Functions, a range of Data Analysis techniques including Advanced Filter, Database Functions and Pivot Tables, and Workbook management facilities such as multiple linked workbooks, Auditing, Shared Workbooks, Protection and Templates.

Topics:

• Managing Range Names
• Date and Time Formulas
• Custom Formats
• IF and Related Functions
• VLOOKUP and Related Functions
• Advanced Filter / Subtotals
• Database Functions
• Multiple Workbooks / Consolidation
• Data Validation
• Auditing
• Pivot Tables
• Shared Workbooks / Tracking
• Protecting Worksheets
• Using Templates

Course Content:

Day 1

Module 1:  Managing Worksheet Data

• Applying Formulas
• Using Statistical Function
• Applying Adv. Conditional Formatting (Using Formulae)
• Applying Conditional Formatting on Charts
• Using Range Names
• Using Quick Analysis

Module 2: Charts

• Using charts for graphical presentation
• Use of various Types of Graphs
• Customizing Charts
• Creating Dynamic Chart
• Using Sparkline

Module 3: Functions

• Using Logical Function (IF / AND / OR )
• Using Date Function (DATEDIF, DATE, EDATE, WEEKDAY, TEXT)
• Using Conditional Functions (SUMIF / SUMIFS / COUNTF / COUNTFS / AVERAGEIF )
• Creating Dashboard

Module 4: Analysing Data

• Concept of Goal Seek
• Concept of Scenario Table
• Using Data Table

Module 5:  Managing List

• Extracting Data using Filter
• Using Advance Filter â?? With Formulas
• Summarizing Data using Subtotals
• SUBTOTAL as a FUNCTION
• Using DATABASE Function
• Using TABLES

Day 2:

Module 6:  Summarizing Report

• Using Pivot Data Table
• Using Recommended Pivot
• Using Pivot Charts
• Customizing Pivot Report â?? Using Calculated Field
• Using Slicers
• Pivot Table using Multiple Consolidated Ranges
• Using VLOOKUP / Nested VLOOKUP / VLOOKUP WITH IF LOGIC
• Using HLOOKUP
• Using INDEX / MATCH Function
• Using INDIRECT FUNCTION
• Using OFFSET Function
• Introduction to Power Pivot Addins

Module 7: Working with Multiple Workbooks

• Create a Workspace
• Consolidate Data
• Managing Linked Cells in Different Workbooks

Module 8: External Data

• Import Data from External Data Source
• Export Data to External Data Source
• Pivot Table using External Source

Module 9:  Automating Report

• Introduction to Macros
• Understanding Objects / Properties / Method
• Excel as Application Object - Hierarchy
• Introducing to Recording Macros â?? Absolute and Relative
• Introducing to VB Editor
• Customizing Recorded macro.