MICROSOFT EXCEL ADVANCED COURSE (MEXA3)
Take the road to becoming an Excel expert.

WHO SHOULD ATTEND?

Microsoft Excel training course
The level 3 Advanced course is designed to really take students to the next level and develop the necessary skills to be considered an Excel expert.

Students needs to be relatively experienced in Excel to attend this course and fully utilise the lessons learned. If the student is not confident level 1 & 2 are advisable.

In this course we will look at Pivot Tables, Lookup functions, What If analysis, Goal Seek, Spreadsheet security, Macros and many more.

This course will empower you to transform complicated tasks into very simple and save huge amounts of time.

  • Duration 8:30 - 16:30
  • Receive a Certificate of attendance
  • Training by a dynamic and experienced facilitator
  • Training manual with notepad & pen

DOWNLOAD REGISTRATION FORM HERE

registration_form.pdf
File Size: 119 kb
File Type: pdf
Download File
Training manuals are copyright protected under Wiki Commons License.

COURSE OUTLINE

SECTION 1 ADVANCED WORKSHEET FUNCTIONS

NAMES
  • Defining Names
  • Creating Names Automatically
  • Managing Names 

USING NAMES 
  • Go To
  • Names In Formulae 
  • Applying Names 

CONDITIONAL & LOGICAL FUNCTIONS
  • If Statements
  • Logical Test
  • Value If True / False
  • Nested If
  • Statistical If Statements

AND, OR, NOT
  • And
  • Or 
  • Not

LOOKUP FUNCTIONS
  •   Lookup
  • Vector Lookup
  • Hlookup
  • Vlookup
  • Nested Lookups

OTHER USEFUL FUNCTIONS
  •   ISERROR
  • IFERROR
  • COUNTIF
  • AVERAGEIF
  • AVERAGEIFS
  • SUMIFS
  • COUNTIFS
 
DATA CONSOLIDATION
SECTION 2 VIEWS, SCENARIOS, GOAL SEEK AND SOLVER

GOAL SEEKING AND SOLVING
  • Goal Seek
  • Solver 
  • Solver Parameters
  • Constraints 
  • Solver And Scenario Manager
  • Opening Scenario Manager
  • Saving Solver Solutions 
  • Reports

VIEWS & SCENARIOS
  •   Custom Views
  • Typical Custom View Model
  • Defining A View
  • Showing A View
  • Scenario Manager
  • Load The Scenario Manager
  • Showing A Scenario
  • Editing A Scenario
  • Deleting A Scenario


SECTION 3 USING EXCEL TO MANAGE LISTS

EXCEL LISTS,LIST TERMINOLOGY
  •   Row And Column Content
  • Column Labels 
  • List Size And Location
  • Miscellaneous 

SORTING DATA
  •   Custom Sorting Options 
  • Creating A Custom Sort Order

ADDING SUBTOTALS TO A LIST
  •   Examining Subtotals

FILTERING A LIST
  •   Custom Criteria 
  • Wildcards 
  • Turning Off Autofilter
  • Advanced Filtering 
  • Set Criteria 
  • Entering Search Criteria 
  • Wildcards With Text Criteria
  • Multiple Criteria 
  • Checking The Criteria Range 
  • Calculated Criteria
  • Calculated Criteria Using Functions
  • Copying Filtered Data
  • Unique Records

LIST STATISTICS
  •   Database Functions

PIVOTTABLES
  • To Create A PivotChart Report From An Existing PivotTable Report 
  • Create A Static Chart From The Data In A PivotTable Report 
  • Delete A PivotTable Or PivotChart Report
  • Create Layout For PivotTables 
  • Modifying A PivotTable 

MANAGING PIVOTTABLES
  • Using Another PivotTable Report
  • Changing An Existing Report's Source Data
  • Automatically Refresh Data When A Workbook Is Opened
  • Automatically Refresh Data At Regular Time Intervals 
  • Require A Password To Refresh An External Data Range 
  • Grouping PivotTable Items
  • Formatting A PivotTable 
 
SECTION 4 CHARTS

INTRODUCTION TO CHARTING
  • Terminology 
  • Embedded Charts
  • Separate Chart Pages 
  • Three Methods To Create Charts
  • Moving And Resizing Embedded Charts 
  • Data Layout 
  • Shortcut Menu (Right Click) 
  • Chart Types 
  • Default Chart Type 

FORMATTING CHARTS
  • Design Ribbon 
  • Switch Rows And Columns
  • Changing The Chart Layout 
  • Chart Styles 
  • Moving Chart Location
  • Layout Ribbon 
  • Formatting Chart Elements
  • Resetting Custom Formats
  • Adding, Removing And Formatting Labels 
  • Axes
  • Gridlines 
  • Unattached Text
  • Format Dialog
  • Add A Series Manually 
  • The Series Function
  • Charting With Blocks Of Data
SECTION 5 TEMPLATES
  • INTRODUCTION TO TEMPLATES 
  • Standard Templates 
  • Custom Templates 
  • AutoTemplates
  • Opening And Editing Templates
  • Template Properties
SECTION 6 AUDITING

AUDITING FEATURES
  • Tracers
  • Comments
SECTION 7 OTHER FORMATTING

FORMATTING CELLS
  • Themes
  • Customising A Theme
  • Styles 
  • Conditional Formatting 
SECTION 8 OTHER EXCEL FEATURES

INSERTING, FORMATTING AND DELETING OBJECTS
  • Inserting A Drawing Object 
  • SmartArt
  • WordArt
  • Formatting Shapes 
  • QuickStyles
  • Manual Formatting 

REVIEWING
  • Protecting
  • Use A Shared Workbook To Collaborate
  • Share A Workbook 

PROOFING TOOLS
  • Spelling And Grammar 
  • Thesaurus
  • Translation 
  • Show Or Hide ScreenTips

2 Day course 
R2 950 All inclusive 
(20% Discount: 2+ Delegates)

Register here
Picture
Twitter
Picture
Picture
Photos used under Creative Commons from http://www.freedigitalphotos.net: Andy Newson,  Arvind Balaraman, Ambro, digitalart, photostock, 

Biz Training Institute © 2012

Accepted in the ezSearch South African business directory. Accepted in the ezSearch South African business directory.