Who Should Attend?
This course is ideal for those who would like to be able to work comfortably with Microsoft® Excel. Users who have a good understanding and knowledge of Microsoft® Excel, and those who are keen to further their working knowledge of Microsoft® Excel, would greatly benefit from this course.
About The Programme
This two-day programme covers the more intermediate levels of Microsoft® Excel on Day One, and looks at advanced topics on Day Two. 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
What You Will Gain
This course is ideal for those who would like to be able to work comfortably with Microsoft® Excel. Users who have a good understanding and knowledge of Microsoft® Excel, and those who are keen to further their working knowledge of Microsoft® Excel, would greatly benefit from this course.
About The Programme
This two-day programme covers the more intermediate levels of Microsoft® Excel on Day One, and looks at advanced topics on Day Two. 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 intermediate functions of Microsoft® Excel 2013, 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 multiple worksheets
- To be able to use Logical and Statistical functions
- To understand advanced Lookup and Reference 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 consolidate data
- To be able to insert PivotTables and PivotCharts into your Excel sheets
- To understand how to use Macros
- To understand and utilise advanced Data Management
- To be able to secure, protect, and customise your workbook
What You Will Gain
- An advanced working knowledge of Microsoft® Excel
- 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 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
- 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 - Importing And Exporting Text
- 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
> Statistical Functions – COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, AVERAGEIF, SUMIF, LARGE, SMALL
> Logical Functions – AND, OR, IF, IFERROR
> Nested Functions – AND, OR, IF
> Lookup Functions – VLOOKUP, HLOOKUP, INDEX, MATCH
> Text Functions – PROPER, UPPER, LOWER, CONCATENATE - 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
DAY TWO
- Charts 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 - 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
> Using Advanced Filter
> Creating A List Of Unique Values
> Removing Filters - 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
> 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 Visual Basic For Applications (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.
| Date: | 21st - 22nd Oct 2018 |
| Duration: | 2 days |
| Location: | Dubai |
| Brochure Price: | AED 2,600/- + VAT |
| Online Price: | AED 2,470/- + VAT |
| Date: | 05th - 06th Dec 2018 |
| Duration: | 2 days |
| Location: | Abu Dhabi |
| Brochure Price: | AED 2,600/- + VAT |
| Online Price: | AED 2,470/- + VAT |
