Microsoft Excel: Advanced (Level 3)

Automate spreadsheet analysis

• VLOOKUP
• VLOOKUP + Helper columns
• HLOOKUP
• IF Function
• Left, Right Function
• CONCATENATION
• ROUND
• Choose()
• REPT()
• TYPE()
• RANDBETWEEN()
• Unit conversion by CONVERT()
• PV function
• Time functions
• INDEX and MATCH
• SUMPRODUCT
• SUMIF & SUMIFS
• COUNTIF & COUNTIFS,
• OFFSET & INDIRECT Functions
• Case studies: Real life examples and models using these techniques.

Automate Decision making processes:

• IF and nested IF Functions
• AND, OR, NOT Functions
• Build complex powerful Formulas with an easy step by step method.
• Text and Date Functions with examples of combining Text functions.

Data Analysis: Use techniques to analyse your data instantly.

• Advanced Filters
• Using List Features
• Dynamic Lists
• The new Sumifs and Countifs Functions
• Pivot Tables
• Using the Pivot Table Wizard
• Changing the pivot table layout
• Formatting
• Grouping items
• Inserting calculated fields
• Pivot Table Options
• Display and hide data in fields
• Lay out reports directly on worksheet
• Pivot Charts
• Pivot Tables Slicers
• 20 Pivot Tables Recipes for powerful reports
• Pivot Tables Macros
• Creating Independent Pivot table

Advanced Pivot Tables

• Inserting Calculated Fields
• Manipulating Fields
• Changing Value Field Settings
• Grouping Data containing Dates and Numbers
• Formatting Pivot Table
• Showing and Hiding the Grand Totals
• Changing The Scope Of The Data source
• Summarizing Values by Sum, Count, Average, Max, and Product
• Show Values As % of Grand Total, % of Column Total, % of Row Total
• Creating Pivot Table Reports and Pivot Chart Reports

 

 

 

What If Analysis

• Scenarios
• Custom Views
• Reports
• Goal Seek
• Data Tables

Charts

• Charts basics
• Types of charts

Advanced charts

• Column Chart with Percentage Change
• Column Chart with Percentage Change
• Interactive Histogram with Details
• Annual Trend with Monthly Details
• Interactive Waterfall Chart
• Variance on Clustered Charts
• Actual vs. Multiple Targets Chart
• Stacked Bar with Dynamic Data Labels
• Comparative Distribution Chart
• Zoom on Charts Macro

Macros

• Purpose of Macros
• Recording macros
• Where to save macros
• Absolute and relative record
• Running macros: Custom buttons, menu items, keyboard shortcuts
• Case Studies: Examination of Macros to save time and automate your work

VBA Coding

• Event-driven programming
• The role of events in Windows
• Responding to events through event procedures
• Putting objects to work
• Properties
• Methods
• Events
• Collections
• Using With...End With
• Storing data in variables
• How and when to declare variables
• Selecting data types
• Fixed and dynamic arrays
• Constants
• Conditional branching
• If...Then...Else
• Select...Case
• Looping through code
• Do...Loop, While and Until
• For...Next and For Each...Next