Microsoft Excel Training Courses

Excel Basics

• An overview of the screen, navigation and basic spreadsheet concepts
• Understand Excel terminology workbooks, worksheets, rows, columns, cells
• Various selection techniques
• Understanding the various mouse cursors
• Working with Ribbon Tabs, Groups and Dialog Boxes
• Standard and Contextual ribbons
• Use the task pane
• Understand and use the File Tab
• Saving, File types, File compatibility (earlier versions)
• Useful keyboard commands
• Quick Access Toolbar & Mini Toolbar
• Get Help!

Entering Data

• Various ways to Enter, Edit and Delete information (Text, Numbers, Dates)
• Use Auto List feature
• Date and Time are storage in Excel
• Enter Dates and Times
• Date/time formatting
• Methods to Move and Copy data
• Use the Fill Handle to copy data and create simple lists
• Create series of information
• Insert, Delete and Hiding Rows & Columns
• Insert, Delete and Move Cells
• Insert, Delete, Move and Copy Sheets
• Various navigation techniques

Creating & Editing Formula & Functions

• Define Formula
• Create & Edit Formula
• BODMAS : Mathematical Order
• Copy & Move Formula
• Use Basic Functions - Sum, Average, Max, Min, Count, Counta
• Use Autosum tool
• Introduction to the Function Library
• Reference a cell in other sheets
• Understand Relative Referencing
• Apply Absolute (Fixed) Referencing
  

Formatting and Proofing

• Formatting of Cells with Number formats, Font formats, Alignment, Borders, Fill colours and Patterns
• Quick formats and themes
• Sheet tab formatting and Renaming
• Copy and Clear Formats
• Merging cells
• Spell Check, Find & Replace and AutoCorrect

Printing and Page Setup

• Page Orientation
• Add Headers and Footers
• Print a selection
• Set a print area
• Clear a print area
• Shrink to Fit
• Adjust page breaks within Page Break Preview
• Repeat columns and rows
• Print Grid-lines
• Save print settings

Multiple File & Large Workbook Tools

• Split screen tool
• Freeze panes
• Arrange workbook windows
• Zooming tools
• GoTo tool
• Name box navigation

Recap

• Absolute references
• Basic function creation methods
• Formula
• Data entry and formatting
• Date, Text, Number principles

Range Names

• Concept and Purpose
• Naming individual or range cells
• Deleting and amending named ranges
• Using named cells/ranges in formula
• Create Named Ranges and Cells
• Manage Names (Setting Scope, Deleting, Renaming)
• Navigate Named Ranges
• Use names to Calculate
• Use ranges in Functions
• Paste Names
• Filter Names

Formula Auditing Tools

• Revealing Formula
• Tracing Precedents/Dependents
• Goto Special
• 3D Calculations
• Linking sheets in the same file
• Linking different Excel files
• Using Edit, Links
• Viewing different files at once
• Saving a workspace
• Viewing different sheets at once
• Window Split
• Data consolidation

Conditional Functions & Formats and Date Calculations

• Work with Styles
• Use If Statements, Nested If
• Use And, Or, Not
• Nesting If, And, Or, Not
• Use the Sumif , Countif, Averageif
• Use the Sumifs, Countifs, Averageifs
• Use the Iserror, Iferror functions
• Apply Conditional Formatting
• Date Calculations
• Calculate working days
• Use the DAY(),MONTH(),YEAR() functions
• Create and use Time calculations

Formula Auditing

• Use the Formula Auditing Tools
• Reveal Formula (in a cell)
• Trace Precedents/Dependents
• Setting a Watch
• Goto Special

Worksheet Management & Linking

• Link sheets in the same workbook
• Link cells in different Excel files
• Manage and edit Links
• View different files at same time
• Saving a workspace
• View different sheets at same time
• Data consolidation (within same file)
• Data consolidation (across workbooks)

Data List Management

• Create an Excel data list
• Use the Excel List Tools
• Use Autofilter
• Sort the Data
• Use the Advanced Filter
• Add Subtotals to a list
• Apply Data Validation to keep data clean
• Group and Outline your data
• Use the Data Form

Excel Charts / Graphs

• Create a Chart with keyboard
• Use the Chart Wizard
• Edit and Format a chart
• Change the Chart source Data
• Apply Trendlines
• Use Sparklines
• Change Charts from objects to sheets
• Add labels and axes to chart
• Show specific Data Points
• Save custom chart types
• Change the default chart type
• Using the Chart Wizard
• Editing and Formatting charts
• Saving custom chart types
• Setting a default chart type

Lookup & Information Functions

• Build the Vertical Lookup (Vlookup) function
• Build the Horizontal Lookup (Hlookup) function
• Build the Match and Index functions
• Build the IsText, IsValue, IsDate , IsNull , IsErr, IsNa functions
• Build and use Database Functions

Pivot Tables

• Create A Pivot Table
• Change the Pivot Table layout
• Manipulate Pivot Table fields
• Format the Pivot Table and apply Styles
• Use Banding (Formatting)
• Group & Grouping items
• Filter data in the Pivot Table
• Insert calculated fields
• Change calculation options
• Change Pivot Table Options
• Display and hide data in fields
• Lay out reports on worksheet
• Create a Pivot Chart from report
• Manipulate Pivot Chart fields
• Use Slicers

What If Analysis

• Create and manipulate Scenarios
• Create and change Custom Views
• Build scenario Reports
• Use the Goal Seek tool
• Use Solver & Advanced Solver Features
• Build Data Tables

Protecting & Sharing Files

• Share a file
• Track changes
• Accept or reject changes
• View Change history
• Apply Data validation rules
• Insert, edit and delete comments

Introduction to Macros

• Review the purpose of Macros
• Record a macro
• Save a macro
• Edit a Macro
• Absolute and relative records
• Running macros:
• Apply macro to Custom buttons on the Ribbon or Quick Access Toolbar, keyboard shortcuts

Customising Excel

• Customise the Quick Access Toolbar and Ribbons
• Set Workbook options
• Change workbook defaults

Templates

• View Template Types
• Use Normal Template
• Open Sample Templates
• Create Custom Templates
• Use Custom and Online Templates
• Open And Edit Templates
• Setting Template Properties

Objects In Excel

• Insert, Format And Delete Objects
• Insert A Drawing Object
• Use the design ribbon
• Use Smart-Art
• Apply Formatting to Smart-Art
• Use Quick-styles
• Explore 2D And 3D Formatting
• Insert and use Word-Art
• Insert and Format Shapes
• More on Themes and styles
• Manually Format an object
• Customise or create A Theme

The Concept, Record and Run Macros & the VB Environment

• The Project Window
• Code Window
• Properties Window
• Navigating between Excel and the VB Editor

Editing Code + Methods of Executing

• Using the Visual Basic Editor
• Structure of a sub routine
• Key Words
• Navigate a Module
• Edit a module
• Use of Function Keys
• Run subroutines together
• Menu, Visual Basic Toolbar, Keyboard Shortcut, Custom Toolbar Button, Custom
• Menu Item, Forms Toolbar executing

Visual Basic Language Rules

• Theory of Object
• Properties and Methods
• Active Objects
• Object Model Hierarchy
• Inheritance

Control Structures

• IF, Nested IF
• AND OR NOT
• SELECT CASE
• Inbuilt Functions
• Distribution, Watch Pane
• Step through Functions using the immediate Window

Looping Structures

• Do Loops
• For Next Loops
• For Each Next Loops

Interactive Macros

• Using a Msgbox
• Use a Conditional Message Box
• Use the Inputbox

Create Interactive Forms

• Build Form Controls
• Build User forms
• Use of the Properties Window
• Combo box
• Radio Buttons
• Group box
• Check box
• Built in Dialog Boxes
• Create Custom Menus
• Create Custom Toolbars

Events

• What are Event Procedures?
• On Open event
• Before Close event
• On Activate event
• On Deactivate event
• On Key event
• On Time event

Security

• Protect/lock code for view/edit
• Digital Signatures