Corporate Training
Blue Ocean offers a wide array of bespoke corporate training solutions for organizations to enable their employees to become specialists in their industry. We offer over 600+ corporate training programs catering to a wide range of industries across government, semi-government and private organizations. The courses are designed to deliver strategic insights and learning for building high potential teams that can drive actionable strategies for their organizations. Our corporate programs are flexible and can be tailored to suit customers’ specific needs, helping teams unleash their maximum potential for organizational benefits.
Excel Advanced 2016
Course Objectives
- To understand the new features of Microsoft® Excel 2016, and how it is different from previous versions
- To be able to use conditional formatting and advanced display options
- 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 be able to forecast using the Forecast Sheet tool
- To be able to create relationships between multiple datasheets and analyze the combined data using PivotTables and Pivot Charts
- To understand and utilize advanced data management tools like What-If Analysis
- To be able to secure, protect, and customize your workbook.
- To understand how to create and run macros
Course Outline
Getting Familiar With Excel
- Using AutoFill And Creating Custom Lists
- Using Flash Fill
- Using The Quick Analysis Tool
Understanding Links
- Pasting Links
- Examining And Editing External Links
Understanding Absolute, Mixed, And Relative Cell References
- 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
- Lookup Functions
- Text Functions
- Nested Functions
Conditional Formatting
- Creating Conditional Formatting Rules Using Formulas
- Creating Rules To Customize Icon Sets
Chart And Sparklines
- Using The Recommended Charts Option
- Understanding The Use Of The New Chart Types Offered In Excel 2016
- Combination Charts
- Using Sparklines To Understand Trends
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 Analyze Multiple Tables Together
- Inserting Pivot Charts
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
- Working With Data Tools
- Separating Text Into Columns
- What-If Analysis Tool
- Forecast Sheet
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
- Customized Protection For Your Worksheet To Allow Selective Editing
- Encrypting Your Workbook With A Password
- Adding Password Protection To Protect Your Workbook Structure
- Finalizing Your Workbook As ‘Read Only’
Creating And Working With Tables
- Formatting Tables Using Pre-Set Styles
Course Objectives
- To understand the new features of Microsoft® Excel 2016, and how it is different from previous versions
- To be able to use conditional formatting and advanced display options
- 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 be able to forecast using the Forecast Sheet tool
- To be able to create relationships between multiple datasheets and analyze the combined data using PivotTables and Pivot Charts
- To understand and utilize advanced data management tools like What-If Analysis
- To be able to secure, protect, and customize your workbook.
- To understand how to create and run macros
Course Outline
Getting Familiar With Excel
- Using AutoFill And Creating Custom Lists
- Using Flash Fill
- Using The Quick Analysis Tool
Understanding Links
- Pasting Links
- Examining And Editing External Links
Understanding Absolute, Mixed, And Relative Cell References
- 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
- Lookup Functions
- Text Functions
- Nested Functions
Conditional Formatting
- Creating Conditional Formatting Rules Using Formulas
- Creating Rules To Customize Icon Sets
Chart And Sparklines
- Using The Recommended Charts Option
- Understanding The Use Of The New Chart Types Offered In Excel 2016
- Combination Charts
- Using Sparklines To Understand Trends
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 Analyze Multiple Tables Together
- Inserting Pivot Charts
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
- Working With Data Tools
- Separating Text Into Columns
- What-If Analysis Tool
- Forecast Sheet
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
- Customized Protection For Your Worksheet To Allow Selective Editing
- Encrypting Your Workbook With A Password
- Adding Password Protection To Protect Your Workbook Structure
- Finalizing Your Workbook As ‘Read Only’
Creating And Working With Tables
- Formatting Tables Using Pre-Set Styles