Microsoft Excel is the most commonly used analytics, reporting, and strategy software. Becoming an expert data analyst requires you to have mastery of advanced Excel skills. This course is aimed at taking your excel skills to the next level. You will be able to apply complex functions within Excel to manage, manipulate, analyze and visualize data.
Course Objective of Advanced Microsoft Excel
By the end of this training the participants will be able to:
Master advanced formulas and functions
Consolidate data, link and export data
Create sophisticated outputs including tables, charts, and graph
Use What-If Analysis tools such as Goal Seek, Solver, and Scenarios
Increase interactivity by automating spreadsheets with macros and VBA
Turn raw data into ‘must-make decisions using PivotTables and PowerPivot.
Course Methodology of Advanced Microsoft Excel
Participants will increase competencies through a variety of instructional methods including lectures by our seasoned trainers who have vast experience as expert professionals in the respective fields of practice.
Organizational Impact of Advanced Microsoft Excel
The organization will increase:
More efficient Excel support for the staff
Greater relevance and dependability of Microsoft Excel information
More tricky thinking and focus on performance
More efficient use of Microsoft Excel for the company
Personal Impact of Advanced Microsoft Excel
Increased Value to Employers
Handle Big Data
Do Smart Work Than Hard work
Shortcut Tricks And Tips
Mastery of Excel Will Make You More Efficient
Learning Excel Prepares Workers for Financial Positions
A Variety of Non-Traditional Workers Benefit from Excel Classes
Who Should Attend
This course is aimed at professionals who have basic to intermediate Excel skills and want to advance those skills.
Finance & Accounting Advisors
Human Resource
Digital Marketing
Project Manager
Inventory & Product Management
Course Outline
Day 1
Data Validation & Referencing in Excel
Whole numbers and decimals
Date and time validation
Text length
Excel data validation list (drop-down)
Custom data validation rules
Absolute referencing
Relative referencing
Structured reference
Day 2
Working with Structured tables & Formulas and Functions
Why you should use tables
Elements of an Excel table
Managing data in an Excel table
Using structured references with Excel tables
VLOOKUP
HLOOKUP
INDEX and MATCH
SUMPRODUCT
SUMIF & SUMIFS
COUNTIF & COUNTIFS,
OFFSET & INDIRECT
Concatenating Functions
IF and nested IF Functions
AND, OR, NOT Functions
Date Formulas and Functions
Day 3
What If Analysis
Scenarios
Custom Views
Reports
Goal Seek
Data Tables
Day 4
Pivot Tables and Pivot Charts & Charts and Visualizations
