Excel Essentials

Starting Out

• About Workbooks
• Exploring your Workbook
• Getting Help with Excel

Getting to grips with the basics

• The Excel Interface
• Using Backstage View
• Understanding the Ribbon
• Using & customising the Quick Access toolbar
• The Home Tab
• The Insert Tab
• The Page Layout Tab
• The Formulas Tab
• The Data Tab
• The Review Tab
• Using shortcuts

Organising Your Workbooks effectively

• Create, save & open a new Workbook
• Entering numbers & text in a Workbook
• Adding, deleting & naming sheets

Entering Data into Spreadsheets

• Inserting Cells, Columns & Rows
• Deleting Cells, Columns & Rows
• AutoFill & using SmartTags

Presenting Your Data

• Font formatting
• Borders & Shading
• Number formatting (currency etc)
• Alignment options
• Clearing formats
• Applying cell styles
• Formatting shortcut

Defining & Creating Calculations

• Writing a basic formula
• Understanding the order of BODMA
• The Sum function & AutoSum
• Creating statistical functions (Average, Max and Min)
• An introduction to the Function Wizard

Advanced File Tasks

• Saving and File Management
• Converting and Encrypting Files
• File Properties and Digital Signatures
• File Accessibility and Compatibility

Working with Functions and Formulas

• Formulas with Absolute References
• Autosum Functions (SUM & AVERAGE)
• IF and Nested Functions
• Working with Named Ranges
• Array Functions (SUMIF & AVERAGEIF)
• Formula Auditing
• Text Functions

Managing Tables in Microsoft Excel

• Creating Excel Tables
• Working with Records and Filters
• Advanced Filters

Data Tools in Excel

• Text to Columns
• Flash Fill
• Remove Duplicates
• Data Validation
• Consolidate

How to do more with Charts in Excel

• Trendlines
• Error Bars
• Working with Axis Options
• Advanced Chart Formatting

Creating Sparklines

• Editing Sparklines

Grouping & Outlining and Subtotals

• Outlining and Grouping Data
• Using the Subtotals Tool

What-If Analysis

• Exploring Scenarios
• Goal Seek and Data Tables
• Using Solver

PivotTables

• Getting Started with PivotTables
• Displaying Data in a PivotTable
• Formatting a PivotTable
• Using the Classic PivotTable Layout
• Slicers

Charting Pivoted Data

• Getting Started with Pivot Charts
• Using the PivotChart Tools Tabs
• Formatting a PivotChart

Advanced Excel Tasks

• Excel and Hyperlinks
• Using Custom AutoFill Lists
• Sharing Workbooks

Creating HLOOKUP and VLOOKUP Functions

• Using the VLOOKUP Function
• Using the LOOKUP Function

Introduction to Macros in Excel

• Recording and Playing Macros
• Copying and Deleting Macros
• Visual Basic and Macros

Advanced Conditional Formatting

• Editing Standard Formatting Rules
• Using Formulas in Conditional Formatting

Using Form Controls and Templates

• Using Form Controls
• Creating Templates

Advanced Lookup Functions

• Index Function
• Using the Match Function
• Using Index and Match Together
• Using Index and Match with Array Formula
• Using the Indirect Function

Advanced Functions in Excel

• Using Error Functions
• Using Logical Functions
• Using Array Formulas
• Using Rounding Functions

Advanced Pivot Table Tools

• Importing Pivot Table Data
• Calculated Fields in Pivot Tables
• Showing Pivot Data as a Percentage
• Creating Custom Pivot Tables Styles

Introduction to User Defined Functions Using VBAs

• Creating a User Defined Function
• Creating an Interactive Procedure
• Create and Share a Function Add-in

Setting Excel Macro Options

• Record and Run Simple Macros
• Assign Macros to the Quick Access Toolba

Introduction to User Defined Functions Using VBAs

• Creating a User Defined Function
• Creating an Interactive Procedure
• Create and Share a Function Add-in

Recording and Running Macros in ExcelVBAs

• Setting Excel Macro Options
• Record and Run Simple Macros
• Assign Macros to the Quick Access Toolbar

Introduction to the Excel VBA Editor

• Starting the Visual Basic Editor
• Using the Project Explorer
• Using the Properties Window
• Creating, Importing and Exporting Modules
• Explore Recorded Macros

Creating Procedures and Functions

• Creating Sub and Function Procedures
• Declare and Use Variables
• Displaying Message to the User
• Getting Input from the User

Working with the Excel Object Model

• Understanding Objects, Properties and Methods
• Referencing Cells and Ranges
• Gathering Cell and Range Information
• Using the Offset Method
• Using the Resize Method
• Using With Blocks
• Referencing Workbooks and Worksheets

Controlling Program Flow

• Using the IF Statement
• Using the Select Case Statement
• Using For Next Loops
• Using Do Loops
• Using For Next Loops

Advanced Procedures

• Calling Procedures
• Passing Values to Procedures

Advanced Variables

• Understanding Variable Scope
• Understanding Object Variables
• Creating Array Variables
• Creating Dynamic Array Variables

Interacting with Office Applications

• Setting References to other Applications
• Setting References using Late Binding
• Create a PowerPoint Presentation
• Create a Letter in Word

Debugging and Error Traps

• Review of Error Types
• Debugging tools
• Setting Error Traps

Creating Custom User Forms

• Creating a Custom Form
• Adding Form Controls
• Creating Event Procedures

Workbook and Worksheet Events

• Create Workbook Events
• Create WorkSheet Events