Description
Who Should Attend?
This course is ideal for those who are comfortable with Microsoft® Excel and would like to explore the advanced features offered in Microsoft® Excel 2016. Users who have a good working knowledge of Microsoft® Excel, and those who are keen to further their working knowledge of Microsoft® Excel, would benefit greatly from this course.
About The Programme
This two-day programme, covers the more advanced levels of Microsoft® Excel 2016. Delegates who feel they already have a very good working knowledge of Microsoft® Excel, or are familiar with some Day One topics, should view Day One as a ‘refresher’.
Course Objectives
- To understand the advanced functions of Microsoft® Excel 2016, 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 2016
- To understand how to create Power Maps
- 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 and utilise advanced data management tools like What-If Analysis
- To be able to secure, protect, and customise your workbook
- To understand how to create and run macros
What You Will Gain
- An advanced working knowledge of Microsoft® Excel 2016
- 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® Excel 2016
- 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 protect your worksheets and data
- 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
Programme 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
> Adding And Rearranging The Level To Sort By
> Sorting According To A Custom List
> Using Advanced Filter
> Creating A List Of Unique Values - Creating And Working With Tables
> Using Tables To Build Relationships And Update Reports
> Connecting Data From Multiple Sources
> Transforming The Data To Meet Your Specifications
> Combining Data By Creating Relationships
DAY TWO
- Chart Basics
> Understanding The Use Of The New Chart Types Offered In Excel 2016
> Combination Charts
> Using PowerMaps
> Using Sparklines To Understand Trends - 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
> Build Relationships To Analyse Multiple Tables Together
> Inserting PivotCharts - Advanced Data Management
> Defining Data Entry Rules To Prevent Incorrect Information Being Entered
> Setting Date And List Validation Rules
> Circling Invalid Data - Protecting Your Workbook
> Customised Protection For Your Worksheet To Allow Selective Editing
> Encrypting Your Workbook With A Password
> Adding A Password Protection To Protect Your Workbook Structure
> Finalising Your Workbook As ‘Read Only’ - 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 - Creating Forms
> Understanding The Various Form Controls And ActiveX Controls In Excel
> Inserting And Configuring The Properties Of The Form Controls
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.