Microsoft Excel

  • This Course is designed to help those new to Excel gain the necessary skills to use Excel either in the workplace or personally. This course requires no previous knowledge of Excel. Included in this course are Working with Cells, Ranges, Columns and Rows, as well as Formulas, Functions and Managing Worksheets and Workbooks.

    Excel Overview
    Entering and Editing Data
    Resizing Rows and Columns
    Hiding and Showing Rows and Columns
    Move and Copy Data
    Navigation in Excel
    Using Find and Replace
    Working with Freeze Panes
    Using the Autofill tool

    Building Blocks of Formulas
    Working with Operators and Cell References
    Using the Auto calculate Feature
    Using basic functions (SUM, AVERAGE, MAX, MIN,COUNT)
    Formulas and Non-Contiguous Ranges
    Using Absolute References
    Using 3D Totals

    Formatting
    Basic Formatting Tools
    Working with Cells and Borders
    Number and Text Formatting
    Copy and Paste Formats
    Working with Cell Styles
    Merging and Unmerging Cells
    Rotating and Vertical Text
    Format as Table

    Printing & Managing Workbooks
    Working with Print Options
    Printing Large Worksheets
    Using and Removing Page Breaks
    Headers and Footers
    Insert and Delete Worksheets
    Move or Copy Worksheets
    Grouping Worksheets
    Worksheet Tabs
    Basic Worksheet Protection

  • In this Course the student will focus on more advanced formulas and Functions, use formatting based of conditions and simply Excel use through Sort, Filter and Table tools. Charts add a graphic element to analysis and will prove very useful to those who need to represent data in with visual impact.

    Worksheet Functions
    Logical Functions (IF, AND, OR & Nested)
    Lookup Functions (Vlookup & Hlookup)
    Text Functions (Right, Left, Proper, Concatenate)
    Today & Now Functions

    Conditional Formatting
    Format Cells Based on Value
    Icons Sets, Databars and Color Scales
    Formula Is Formatting
    Manage Rules
    Clear Rules

    Sort & Filter Data
    Autosort Data
    Muliple Sort
    Sort by Color or Icon
    Sort by Expression
    Filter Data by Value
    Filter by Text
    Copy and Paste Filter Data
    Filter by Color or Icon
    Custom Filters

    Create and Use Charts
    Understanding Chart Elements
    Adding a Chart Title
    Adding Axes Titles
    Repositioning the Legend
    Showing Data Labels
    Showing Gridlines
    Formatting Charts
    Chart Themes
    Trendlines and Errorbars
    Adding a Data Table
    Exploding Pie Slices
    Changing Individual Bar Colours
    Formatting Text

    Worksheet Tables
    Understanding Tables
    Creating a Table From Scratch
    Working With Table Styles
    Inserting Table Columns
    Removing Table Columns
    Converting a Table to a Range
    Creating a Table From Data
    Inserting or Deleting Table Records
    Removing Duplicates
    Sorting Tables
    Filtering Tables
    Renaming a Table
    Splitting a Table
    Deleting a Table

  • In this Course the Student will gain necessary skills in some of Excel Powerful Analysis Tools, including Pivot Table, Data Consolidate, Data Tables, Scenarios and much more. This course a must for those need to manage large data or complex data structures.

    Protecting Data
    Providing Total Access to Cells
    Protecting a Worksheet
    Disabling Worksheet Protection
    Providing Restricted Access to Cells
    Password Protecting a Workbook
    Protect and Share a Workbook

    Importing Data
    From Text File/CSV
    The Web
    A Database
    Data Import Issues Workshop
    Working With Connected Data
    Unlinking Connections

    Grouping and Outlining
    Understanding Grouping and Outlining
    Creating an Automatic Outline
    Working With an Outline
    Creating a Manual Group
    Grouping by Columns
    Creating Subtotals
    Creating Nested Subtotals
    Copying Subtotals
    Using Subtotals With AutoFilter

    Data Consolidation
    Understanding Data Consolidation
    Consolidating With Identical Layouts
    Creating a Linked Consolidation
    Consolidating From Different Layouts
    Consolidating Data Using the SUM,AVERAGE, MAX & MIN

    Data Tables
    Understanding Data Tables and WhatIf Models
    Using a Simple WhatIf Model
    Creating a One Variable Table
    Using OneVariable Data Tables
    Creating a Two Variable Data Table

    Scenarios
    Understanding Scenarios
    Creating a Default Scenario
    Creating Scenarios
    Using Names in Scenarios
    Displaying Scenarios
    Creating a Scenario Summary Report
    Merging Scenarios

    PivotTables
    Creating PivotTables
    Pivotable Fields
    Filtering a PivotTable
    Clearing a Report Filter
    Formatting a PivotTable
    Understanding Slicers
    Inserting a Timeline Filter
    Using Compound Fields
    Counting in a PivotTable
    Formatting PivotTable Values
    Working With PivotTable Subtotals & Grand Totals
    Creating Calculated Items
    Providing Custom Names
    PivotTable Options
    Sorting in a PivotTable
    PivotCharts
    Inserting a PivotChart

    Data Validation
    Understanding Data Validation
    Creating a Number Range Validation
    Testing a Validation
    Creating an Input Message
    Creating an Error Message
    Creating a Drop Down List
    Using Formulas as Validation Criteria

    Analysis Tools
    Goal Seek
    Solver
    Analysis Toolpak
    Intro to PowerView

  • In this 3 day training course, the student will learn the Fundamentals of Visual Basic Programming, starting from recording Macros, to Creating and Coding Procedures. Working with Modules, Decision Making and Loops. This course also includes creating and using User Forms to further enhance the user experience in Excel.

    Understanding Excel VBA
    Programming in Microsoft Excel
    VBA Terminology
    Displaying the Developer Tab
    The VBA Editor Screen
    Opening and Closing the Editor
    Naming Conventions

    Create and Use Macros
    Macro Recorder
    Absolute and Relative Movement
    Run Macro Dialog Box
    Keyboard Shortcuts

    Recorder Workshop
    Recording Macros
    Creating Buttons
    Quick Access Toolbar
    Create a Custom Ribbon Tabs and Groups

    VBA Editor
    Overview of the Editor
    Access Edit and Debug Tools
    Setting Breakpoints in Code
    Stepping Through Code Procedures
    Using IntelliSense
    Commenting Statements
    Indenting Code
    Bookmarking in Procedures
    Create a Basic Procedure
    Create and NameĀ  Module
    Run the Procedure from the VBE

    User Defined Functions
    Create a User Defined Function
    Functions Variables
    Run the Function
    Modify Properties in Object Browser
    Creating a Function Library

    Variables
    Overview of Variables
    Scoping Variables
    Passing Variables by Reference
    Passing Variables by Value
    Understanding Data Types for Variables
    Declaring Data Types (Option Explicit)
    Using Arrays
    Static Variables

    Using Excel Objects
    The Application Object
    The Workbook Objects
    Program Testing With the Editor
    Using Workbook Objects
    The Worksheets Object
    Using the Worksheets Object
    The Range Object
    Using Range Objects
    Using Objects in a Procedure

    MsgBox and Inputbox Functions
    Create a Basic MsgBox
    Using Msgboxes for Input & Output
    Msgbox Parameters
    Create an Inputbox
    InputBox Parameters

    Decisions
    Single Line IF
    Multiline IF
    IF for Multiple Conditions
    Nesting IF Statements
    Select Case Statements

    Create and Use Loops
    For To (Fixed Numeric Iterations)
    For Each (Fixed Cell Based Iterations)
    Do Until (Continuous Loop)
    Do While (Continuous Loop)
    Exiting Loops
    Nesting Loops

    Using Forms
    Adding Controls to a Form
    Programming Form Controls
    Initializing The Form
    Loading and Unloading the Form
    Transfer Data to the Worksheet
    Call the Form in a Procedure

    Error Handling
    Understanding Error Types
    The on Error Statement
    Simple Error Trapping
    Using the Resume Statement
    Using Decision Structures in Error Handlers
    Working With Err Object
    Error Handling in Forms
    Coding Error Handling in Forms
    Defining Custom Errors