Advanced Excel Course in Germany - Spoclearn

1
Advanced Microsoft Excel Training - Brochure
2
Why take up Advanced Microsoft Excel Training?
Microsoft Excel has become an indispensable tool
in an individual's life, starting from school,
college, and then to professional workspace. More
than 80 of jobs in the industry require Excel
skills, and for some jobs, it is a mandatory
skill set as we are moving towards a data-centric
business landscape. Professionals with Advanced
Microsoft Excel knowledge earn 12 more than
others with basic or intermediate-level skills.
Some of the top-level jobs that require Advanced
Microsoft Excel skills include Accountants,
Administration Staff, Cost Estimators, Data
Analysts, Business Analysts, Project Managers,
Financial Analysts, Retail Managers, Sales
Marketing professionals, and more. The average
salary of a Microsoft Excel certified
professional is in the range of USD 47,000 to USD
89,000 per annum in the US.
3
Who can take up
ADVANCED MICROSOFT EXCEL TRAINING? Job roles that
can find Advanced Microsoft Excel training
beneficial are
Data Analysts Business Analysts Operations
Staff Clerical Team Members Marketing Staff
Administration Staff
Sales team Members Marketing Team Members Project
Managers Data Engineers Data Scientists Accounting
/Finance Staff

• Anybody who wants to gain a thorough
  understanding of Advanced Microsoft Excel
  concepts
• Professionals who are looking to clear their
  Microsoft Excel MO- 201 certification exam

4
Why Take up ADVANCED MICROSOFT EXCEL TRAINING
FROM SPOCLEARN?
2-Day (6 hours/day) interactive instructor-led
Advanced Microsoft Excel training
100 Money-Back Guarantee

• Expert Microsoft Certified Trainers across the
  globe
• Exercise Spreadsheets prepared by SMEs
• Hands-on practical-based learning with formulas
  and functions
• After training coaching for real-world application

5
MICROSOFT EXCEL MO-201
EXAM FORMAT
Exam Type Multiple Choice
No. of Questions 40-60 questions
Type Closed Book Exam Languages English
01
02
03
04
Exam Duration 90 minutes (One and a half hours)
Passing Score 700 out of 1000 required to
pass (equivalent to 70)
6
ADVANCED MICROSOFT EXCEL
Training Agenda
7
1 INTRODUCTION DATA FORMATTING

• Number Formatting
• Worksheets/Rows/Columns/Workbook
• Wrap Text/Merge/Go to/Find Replace
• Cell-reference

8
2 TABLE STYLE

• Conditional Formatting
• Highlight rows and columns
• Apply data bars and Icons
• How to apply more than one conditional
  formatting?
• Remove conditional formatting
• Custom conditional formatting

9
3 FORMULAS FUNCTION

• Logical Function
• Mathematical Formula
• Text Function
• Date Function

10
3 LOOKUP FUNCTION

• What is the difference between VLOOKUP HLOOKUP?
• Difference uses of True and False option
• Lookup itself
• Advanced VLOOKUP Tricks
• Advanced VLOOKUPs
• Looking up on left
• VLOOKUP with Match function
• Match Index function
• VLOOKUP with multiple condition

11
4 ADVANCED FORMULAS

• OFFSET Formula
• What is it and Syntax
• Offset with examples
• Uses of Offset Formula

12
5 FILTER WITH EDITING

• Sorting with custom list
• Filter
• Advance Filter
• Freeze Panes

13
6 DATA VALIDATION

• Uses of Data Validation
• Using drop-down box to validate
• Dependent drop-down List
• Using Formulas
• Showing Error Messages

14
7 MASTER REPORT

• Pivot Table
• How to convert data in pivot table?
• Use of Pivot table tools.
• Pivot table option and Layout
• Change source data
• Automatic Refresh Data

15
8 REPORT WITH GRAPHICS

• Charts
• Creating 2D 3D Charts
• Formatting charts
• Using Column Line chart together
• Primary Secondary Axis Bullet chart what is
  it and how to make
• In cell charts using REPT to make charts
• Min-Max chart showing range along with average
• Waterfall charts
• Timeline charts
• Thermo-meter charts
• Gauge Chart

16
9 GOAL SEEK SCENARIO

• Creating a scenario
• Defining data tables
• Finding a target value with Goal
• Seek Defining constraints and running Solver
• Creating a configurable model

17
10 GET TRANSFORM DATA

• Import Data
• How to Import Live Data
• How to Import Data from Text, CSV, MS-Access or
  Web

18
11 ADVANCED PROTECTION

• Workbook/Worksheets Protection
• Protection on Opening
• Protection on Read-only
• Workbook Structure
• Locking Unlocking of Cells
• Protecting Constant Cells
• Protecting Formula containing cells
• Protection only specific Cells

19
12 MACROS VBA PROGRAMMING

• Introduction to Macros
• What is a Macro?
• Displaying the Developer Tab
• About Macro Security
• Saving Workbooks that contain macros
• Understanding different types of macros
• Recording, Editing, Deleting of Macro
• Understanding how to record VBA Macros
• Examining, Testing and Editing macros
• Absolute versus Relative referencing while
  recording macros
• Assigning macro to a keyboard shortcut or a button

20
LEARNING ROADMAP FOR YOU
Understand the learning objectives that fits your
learning need
Contact our coach and consultants and understand
in depth
Block your seat
2
4
6
1
3
5
Relate to how it can help you
Talk to the trainer for more clarity
Continuously learn and apply the learning
21
Connect with us now!
You can also email us at info_at_spoclearn.com
support_at_Spoclearn.com
Talk to our consultant USA 1 (832) 924
0564 IND 91 83417-05065 UK 44 807 164 0572