Description

Introduction

This two-day program covers the more advanced features of Microsoft® 365 – Excel.

Course Objectives

• To understand the advanced functions of Microsoft® 365 – Excel, 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 analyse the combined data using PivotTables and PivotCharts
• To understand how to use macros
• To understand and utilise 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
• Working With Data Tools
  • What-If Analysis Tool
  • Forecast Sheet
  • Customising 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

DAY TWO

• 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
• 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

Before the course each delegate will be asked to complete a Pre-Course Questionnaire 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 Investment: AED 2,600/- + VAT, which includes – Training Materials, Lunch and Refreshments. Discount available for multiple bookings.