*Prior to attending any of the Microsoft® Courses, a Questionnaire and Assessment will need to be completed to ensure the course matches your specific needs.

Introduction

This three-day program covers the more advanced features of Microsoft® 365.

Course Objectives

• To understand the advanced functions of Microsoft® 365, and how it is different from the previous versions
• To be able to use Date and Time, Logical, Statistical, and Lookup and Reference functions
• To be able to create more complex formulas
• To proficiently use Advanced Charting and the new charts introduced in Excel
• To understand how to create Power Maps and Map Charts
• To be able to forecast using the Forecast Sheet tool
• To be able to consolidate data
• To be able to gather and shape data for analysis using Get and Transform
• To be able to create relationships between multiple datasheets and analyze the combined data using PivotTables and PivotCharts
• To understand how to use macros
• To understand and utilize advanced data management tools like What-If Analysis

What You Will Gain

• An advanced working knowledge of Microsoft® 365 – Excel
• An in-depth understanding of how to work with complex and unfamiliar Functions
• An understanding of how to create charts and graphs which are available in Microsoft® 365 – Excel
• An awareness of the various analysis tools available in Excel and how to use them
• A list of keyboard shortcuts for frequently used tools, and how to use them to increase speed
• Knowledge of how to record frequently used functions using Macros
• Knowledge of how to create various reports from your data using tools like PivotTables and PivotCharts

Program Content

DAY ONE

• Defining Cell And Range Names
  • Understanding Absolute, Mixed And Relative Cell References
  • Naming Cell Ranges
  • Creating Multiple Names From A Selection
  • Inserting Names In A Formula
  • Managing Names To Edit And Delete
• Using Formulas And Functions
  • Working With The Order Of Operations
  • Inserting A Function
  • Understanding The Syntax Of A Function
  • Date And Time Functions
  • Statistical Functions
  • Logical Functions
  • Nested Functions
  • Lookup Functions
  • Text Functions

DAY TWO

• Working With Data Tools
  • What-If Analysis Tool
  • Forecast Sheet
  • Customizing The Sort Function
  • Using Advanced Filter
  • Using Wildcards In Filters
  • Creating A List Of Unique Values
• Creating And Working With Tables
  • Using Tables To Build Relationships And Update Reports
  • Connecting Data From Multiple Sources
  • Combining Data By Creating Relationships
• Importing Data Using Get And Transform
• Transforming The Data To Meet Your Specifications
• Charts
  • Understanding The Use Of The New Chart Types Offered In Excel
  • Combination Charts
  • Using PowerMaps And Map Charts
  • Using Sparklines To Understand Trends
• Advanced Data Management
  • Defining Data Entry Rules To Prevent Incorrect Information Being Entered
  • Setting Date And List Validation Rules
  • Circling Invalid Data

DAY THREE

• Consolidating Data
  • Combining Data From Various Cells Into A Single Cell
  • Consolidating By Position, By Category, By Using 3D Formulas And By Using The PivotTable Wizard
• Inserting PivotTables And Charts Into Your Excel Sheets
  • Creating A PivotTable
  • Using The Recommended PivotTables Option
  • Applying Conditional Formatting In PivotTables
  • Adding Fields To A PivotTable
  • Changing The Data Source
  • Refreshing The Data In A PivotTable
  • Grouping Data
  • Collapsing And Expanding Fields
  • Using Slicers And Timelines To Filter PivotTable Data
  • Creating Multiple Worksheets From Report Filter Pages
  • Using The Drill Down Feature
  • Building Relationships To Analyse Multiple Tables Together
  • Inserting PivotCharts
• Macros
  • Recording Macros To Speed Up Frequently Performed Actions
  • Editing And Deleting Macros
  • Copying VBA Code
  • Assigning Shortcut Keys, Buttons Or Controls To Recorded Macros

Please contact us for specific details on the courses we are running. Our Business Development Team will be happy to provide you with further details.

Before the course, each delegate will be asked to complete a Pre-Course Briefing Form to determine their individual objectives for attending the course. These objectives will be used by the Tutor to give on-target training that is focused on the individual delegates.

At the end of the course, each delegate will be asked to complete a Personal Development Plan that can be used as part of future appraisals, and that will also be an important tool for management reference.

Total ONLINE Investment: AED 2,850 + VAT