Advance MS Office Excel Essentials | Weekend Course

Advanced MS Office Excel Essentials

 Excel is the world’s leading spreadsheet tool that lets you store and analyze data more intelligently. Ensure you become a proficient user of this powerful tool and choose from programs on introducing worksheet design and formulas through to working with macros, multiple spreadsheets, pivot tables, manipulating data and data analysis features.

Course Overview

This advanced Microsoft Excel 2013 Advanced Essentials workshop focuses rigorously on developing your expertise on the following functions of MS Excel:

• Advanced formulas and functions
• Named ranges
• Resolving formula errors
• Consolidating data, removing duplicates, configuring data validation, and transposing data
• Using outlining, grouping, and subtotal tools
• Working with scenarios
• Using data analysis tools (such as Solver and Goal Seek)
• Creating and using Pivot Tables and Pivot Charts
• Using Power Pivot to integrate data from several different sources
• Recording, editing, and using macros

Course Outline

Module 1: Advanced Formula Tasks

• Understanding Relative and Absolute Cell References
• Using Multiple Cell References
• Using 3D References
• Using Array Formulas
• Review Questions

Module 2: Working with Named Ranges

• Understanding Named Ranges
• Defining Named Ranges
• Editing Named Ranges
• Deleting Named Ranges
• Using Named Ranges in Formulas
• Review Questions

Module 3: Using Advanced Functions

• Using the PMT Function
• Using the FV Function
• Using VLOOKUP and HLOOKUP Functions
• Using IF, AND, OR Functions
• Review Questions

Module 4: Resolving Formula Errors

• Tracing Formula Precedents and Dependents
• Showing Formulas
• Evaluating Formulas
• Setting Error Checking Options
• Using Error Option Buttons
• Running an Error Check
• Review Questions

Module 5: Managing Data

• Consolidating Information
• Removing Duplicates
• Configuring Data Validation
• Transposing Data
• Converting Text to Columns
• Review Questions

Module 6: Outlining and Grouping Data

• Outlining Data
• Showing and Hiding Outline Details
• Grouping Data
• Creating Subtotals
• Removing Outlining and Grouping
• Review Questions

Module 7: Working with Scenarios

• Creating Scenarios
• Loading Scenarios
• Merging Scenarios
• Editing Scenarios
• Creating a Scenario Summary Report
• Deleting Scenarios
• Review Questions

Module 8: Using Solver

• Setting up the Worksheet
• Running Solver
• Generating Reports and Scenarios with Solver
• Modifying Constraints
• Setting Solver Options
• Review Questions

Module 9: Analyzing Data

• Enabling the Analysis Tool Pak
• Using Data Analysis Tools
• Using Goal Seek
• Using Data Tables
• Review Questions

Module 10: Advanced Pivot Table Features

• Creating a Basic Pivot Table
• Creating a Basic Pivot Chart
• Using the Pivot Table Fields Pane
• Adding Calculated Fields
• Sorting Pivoted Data
• Filtering Pivoted Data
• Review Questions

Module 11: Using Power Pivot

• Enabling Power Pivot
• Importing Access Data
• Importing Excel Data
• Integrating Data with Relationships
• Creating a Pivot Table with Power Pivot Data
• Review Questions

Module 12: Using Macros

• Recording a Macro
• Writing a Macro using the Visual Basic Editor
• Editing a Macro
• Running a Macro
• Review Questions

Program Format

• No. of Classes: 04 (Friday only)
• Duration of Classes: 03 hours (with 15 minutes break)
• There will be pre-assignment and post assignments.
• After each class, there will be assignments for skills practice.