Microsoft Office Excel Advanced
=====
—–
* Course Id : OTHR-MXLA
* Duration : 16 hours
Overview
—–
* This course provides skills and knowledge in advanced concepts and techniques including enhanced formatting, charting and worksheet operation
* Participants are also taught the use of complex analytical and automation tools
Pre-Requisites
—–
* Good understanding of Microsoft Excel basics
Training Objectives
—–
* Learn to format sheets and charts as a Power User
* Learn new keyboard shortcuts
* Understand advanced Excel functions
* Understand how to use a range of formula techniques
* Understand how to use a range of lookup and reference functions
* How to apply conditional formatting to ranges in a worksheet
* Learn to use a range of techniques to work with worksheets
* Understand and create simple PivotTable reports
* Create and edit a PivotChart
* Create summaries in your spreadsheets using subtotals
* Create recorded macros in Excel
Course Structure
——-
* We provide more focus on hands-on in our technical courses (typically 80% hands-on/20% theory)
* Students get the capability to apply the material they learn to real-world problems
Materials Provided
—–
* PDF of slides and hands-on exercises
* Access to instance with lab environment
Software Requirements
—–
Any of the following
* Any current internet browser
* vnc client
* rdp client
Hardware Requirements
—–
* Processor: 1.2 GHz
* RAM: 512 MB
* Disk space: 1 GB
* Network Connection with low latency (<250ms) to Internet
## Daywise course outline
—–
## Day 1
—–
* Unit 1 : Grouping & Outlining and Subtotals
* Unit 2 : What-if Analysis
* Unit 3 : Data Tables
## Day 2
—–
* Unit 4 : Working with Outlines
* Unit 5 : Analysing Data with Pivot Tables
* Unit 6 : Creating HLOOKUP and VLOOKUP Functions
* Unit 7 : Excel Macros
## Detailed course outline
—–
Unit 1 : Grouping & Outlining and Subtotals
—–
* Grouping & Outlining and Subtotals
* Outlining and Grouping Data
* Using the Subtotals Tool
Unit 2 : What-if Analysis
—–
* What-if Analysis
* Using the Scenario Manager
* Creating and editing scenarios
* Creating a Scenario Report
* Using Goal Seek
Unit 3 : Data Tables
—–
* Data Tables
* The single-variable data table
* The two-variable data table
* Adding conditional formatting
Unit 4 : Working with Outlines
—–
* Working with Outlines
* Applying an outline
* Automatic outlining
* Displaying and collapsing levels
* Modifying outline settings
* Creating subtotals
Unit 5 : Analysing Data with Pivot Tables
—–
* Analysing Data with Pivot Tables
* Creating a pivot table
* Defining the source range
* Inserting. moving and deleting fields
* Showing and hiding fields
* Grouping and ungrouping fields
* Renaming fields
* Defining field properties
* Formatting a pivot table
* Refreshing a pivot table
* Drilling down
Unit 6 : Creating HLOOKUP and VLOOKUP Functions
—–
* Creating HLOOKUP and VLOOKUP Functions
* Using the VLOOKUP Function
* Using the LOOKUP Function
Unit 7 : Excel Macros
—–
* Excel Macros
* About macros and VBA
* Macro security
* Recording macros
* Running a Macro
* Absolute and relative references
* Saving and distributing macros
* Assigning a macro to a button
* Editing macro code
* Automatic macros