Description

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.

Who Should Attend?

This course is ideal for those who would like to be able to work comfortably with Microsoft® 365 – Excel.  Users who have a basic to good understanding and knowledge of Microsoft® Excel, and those who are keen to further their working knowledge of Microsoft® 365 – Excel, would benefit from this course.

Course Objectives

• To understand the intermediate functions of Microsoft® 365 – Excel, and how it is different from the previous versions
• To be able to use different viewing options
• To be able to use conditional formatting and advanced display options
• To prepare and format Microsoft® Excel sheets and work with multi worksheets
• To be able to use Logical, Statistical, Lookup and Reference functions
• To understand nested functions
• To understand how to navigate efficiently to the relevant tools within the Microsoft® Excel Chart Basics
• To proficiently use Advanced Charting
• To be able to insert PivotTables and PivotCharts into your Excel sheets
• To be able to secure, protect, and customise your workbook

What You Will Gain

• Quick and easy ways of creating and formatting multiple Excel sheets
• An understanding of how to create charts and graphs which are available in Microsoft® 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 protect your worksheets and data
• Knowledge of how to create various reports from your data using tools like PivotTables and PivotCharts

Program Content

DAY ONE

• Getting Familiar With Excel
  • Customising The Ribbon And The Quick Access Toolbar
  • Understanding The Difference Between Permanent And Contextual Tabs
  • Using AutoFill And Creating Custom Lists
  • Using FlashFill
  • Using The Quick Analysis Tool
• Working With Multiple Excel Worksheets
  • Creating A Copy Of A Sheet
  • Grouping Worksheets To Work On Them Simultaneously
  • Opening And Arranging New Windows
  • Switching Between Workbooks
• Using The Different View Options Available And Understanding Their Benefits
  • Setting The Print Area For Your Sheet
  • Inserting Headers And Footers
  • Repeating Rows And Columns On Printed Sheets For Easy Viewing
• Using The Paste Special Options
  • Using Various Operators While Pasting Data
  • Transposing Data
  • Pasting Links
  • Examining And Editing External Links
• Conditional Formatting
  • Creating Conditional Formatting Rules Using Formulas
  • Creating Rules To Customise Icon Sets
• Working With Data Tools
  • Creating And Removing Subtotals
  • Separating Text Into Columns
• Using Formulas And Functions
  • Working With The Order Of Operations
  • Understanding Absolute, Mixed, And Relative Cell References
  • Inserting A Function
  • Understanding The Syntax Of A Function
  • Statistical Functions – COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, AVERAGEIF, SUMIF, LARGE, SMALL
  • Logical Functions – AND, OR, IF, IFERROR
  • Nested Functions – AND, OR, IF
  • Lookup Functions – XLOOKUP, VLOOKUP, HLOOKUP, INDEX, MATCH
  • Text Functions – PROPER, UPPER, LOWER, CONCATENATE
  • Date Functions – TODAY, WORKDAY.INTL

DAY TWO

• Creating And Working With Tables
  • Formatting Tables Using Preset Styles
  • Customising The Sort Function
  • Adding And Rearranging The Level To Sort By
  • Sorting According To A Custom List
  • Applying Single / Multiple Filters To Data
  • Creating A List Of Unique Values
  • Removing Filters
• Chart And Sparklines
  • Using The Recommended Charts Option
  • Viewing Various Preset Chart Options Available Within Microsoft® Excel
  • Resizing A Chart
  • Formatting The Text, Colour, Etc., Within A Chart
  • Pie Chart Angles
  • Formatting And Positioning The Chart Axis, Chart Title, And Chart Legend
  • Changing The Chart Design After A Chart Has Been Created
  • Adding And Deleting A Data Series Within A Chart
  • Copying And Moving Charts Within Worksheets
  • Combination Charts
  • Using Sparklines To Understand Trends
• Inserting PivotTables And Charts Into Your Excel Sheets
  • Creating A PivotTable
  • Using The Recommended PivotTables Option
  • 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
  • Inserting PivotCharts
• 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’

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.