Microsoft Office courseware and training manuals

We do have old courseware for Word, PowerPoint, Outlook, Visio and Publisher (feel free to ask about licensing this), but we only have courseware for the current version of Microsoft Office in Excel and Access:

You can see details of how to license our Microsoft Office courseware (including volume requirements) here.

Access courseware

Here is a list of all of our Access manual chapters (you can download the ones marked):

Chapter Sections Subsections
Access Basics (12 pages) Introduction to Access Access Objects
Entering and Leaving Access Entering Access ; The Getting Started Page
Doing Things in Access Using the Access Ribbon ; Using Keyboard Short-Cuts ; Using Short-Cut Menus
Undoing and Redoing Commands Undoing Commands ; Redoing Commands
Opening and Closing Databases Opening an Existing Database ; Closing a Database ; A Note about Security
Creating a New Database Starting a Blank Database ; Using a Template
Database Files Access 2010 Files ; Working with Older Access Databases
Working with Databases The Navigation Pane ; Viewing Database Objects ; Tabs and Windows
Using Help Searching for Help ; Using Screentips
Office 2010 Interface (8 pages) Interacting With Office 2010 The File Menu ; The Ribbon ; The Mini Toolbar ; The Quick Access Toolbar
Using the Ribbon Tabs, Groups and Commands ; Using Quick Keys ; Seeing More Options
Customising the Ribbon
Getting Help on the Ribbon
Designing a Database (4 pages) The Stages of Database Design Stage 1 – Deciding What to Include ; Stage 2 – Dividing Data into Tables ; Stage 3 – Choosing a Primary Key ; Stage 4 – Drawing your Relationships ; The End Result
Many to Many Relationships
Table Design (18 pages) Creating New Tables Creating Tables in Datasheet View ; Creating Tables in Design View ; Creating Tables using a Template
Field Data Types Text Fields ; Memo Fields ; Number Fields ; Dates and Times ; Currency ; AutoNumber Fields ; Yes/No Fields ; OLE Object Fields ; Hyperlink Fields ; Attachment Fields ; Calculated Fields
Field Properties Properties for Formatting ; Properties for Validation ; Other Useful Properties
Setting a Primary Key Multiple Primary Keys
Lookup Fields Creating a Simple Lookup Field ; Lookup Fields Based on Tables ; Multiple Value Lookup Fields ; Lookup Fields Based on Queries
Datasheets (8 pages)

Download this chapter 		Moving Around a Table
Working with Records Creating New Records ; Selecting Records ; Abandoning a Record ; Deleting Records ; Copying Records
Sorting Records Removing Sorting
Filtering Records Applying Simple Filters ; Applying Complex Filters ; Removing Filters
Formatting Datasheets Column Widths and Row Heights ; Moving Columns ; Hiding and Unhiding Columns ; Formatting Text ; Formatting Numbers ; Rich Text Formatting
Subdatasheets
Relationships (6 pages) Pre-Conditions for Creating Relationships
Creating Relationships
Editing and Deleting Relationships Selecting Relationships ; Editing a Relationship ; Deleting a Relationship
Referential Integrity Cascade Deletion ; Cascade Updating
Printing Relationships
Importing and Exporting Data (4 pages) Importing Data The Data Source ; Importing Data ; Linked Tables
Exporting Data Reusing Saved Import/Export Steps
Basic Queries (12 pages) Creating a Query
Running a Query Structured Query Language (SQL)
Customising the Query Window
Editing a Query Adding Tables to a Query ; Deleting Tables from a Query ; Adding Single Fields to a Query ; Adding Multiple Fields to a Query ; Deleting Fields from a Query ; The Asterisk (*) – Including All Fields ; Changing the Order of Fields ; Sorting by Fields ; Omitting Fields ; The Best Way to Write Queries ; Saving Queries
Setting Criteria
Criteria Examples Exact Matches ; Pattern Matches ; Comparisons ; Combining Comparisons ; A Couple of Extra Ideas for Searches
Parameter Queries (4 pages) Creating Basic Parameter Queries
Using More Complex Criteria Using Multiple Criteria ; Using Comparison Symbols ; Using Wildcard Characters
Limitations of Parameter Queries
Group and Total Queries (4 pages) Groups and Totals Creating a Group and Total Query ; Changing Grouping Levels
Using Criteria in Group and Total Queries Setting Criteria Using the Where Clause
Totals in Datasheets
Expression Queries (6 pages) Expression Queries Creating an Expression Query
Using Expression Builder Zooming In
Functions Typing in Functions ; Using Functions in the Expression Builder ; The IIF Function ; The NZ Function
Text Expressions Concatenating Text ; Text Functions
Date and Time Expressions Simple Date Expressions ; Date and Time Functions
Joins (6 pages) Introduction to Joins
Creating and Deleting Joins Creating a Join ; Deleting Joins
Types of Join Inner Joins ; Outer Joins ; Using Outer Joins to Find Unmatched Records
Action Queries (6 pages) Introduction
Make Table Queries
Append Queries
Update Queries
Delete Queries The FROM and WHERE Options
Crosstab Queries (4 pages) How Crosstab Queries Work
Creating a Crosstab Query Displaying Row “Totals”
Summarising By Month, Quarter or Year Displaying the Month Name ; Changing the Column Order
Basic Forms (6 pages) What is a Form?
Creating New Forms
Views of Forms
Using Forms Moving Between Records ; Selecting Records in a Form ; Sorting, Filtering and Finding
Split Forms
Finding and filtering (6 pages) How Filters Work Cumulative Filters
Applying Filters Common Filters ; Filtering by Selection ; Filtering by Form ; Advanced Filters
Removing Filters Clearing Filters ; Toggling Filters
Finding Records
Formatting Forms and Reports (16 pages) Introduction to Formatting Which View Should You Use?
Selecting Controls Selecting a Single Control ; Selecting Every Control on a Form ; Selecting Multiple Controls ; Using the Ruler to Select Controls ; Selecting a Section ; Selecting a Form
Control Layouts Types of Control Layout ; Resizing Controls in a Layout ; Moving Controls in a Layout ; Removing Controls from a Layout ; Adding Controls to a Layout
Manipulating Controls Moving Controls ; Resizing Controls ; Deleting Controls ; Sizing Controls Automatically ; Distributing Controls ; Aligning Controls
Properties
Formatting Controls Basic Font Formatting ; Formatting Lines and Borders ; Gridlines ; Formatting Numbers and Dates ; Themes ; Form Logos
Conditional Formatting Deleting Conditional Formats
Setting Form Defaults Saving Default Control Formats ; Saving Default Form Templates
Basic Reports (12 pages) What are Reports?
Views of Reports
Creating Reports Creating Quick Reports ; Using the Report Wizard
Previewing a Report Moving Between Pages ; Zooming ; Viewing Different Numbers of Pages ; Page Size and Orientation ; Margins
Printing Reports
Report Sections Changing Section Heights ; Changing the Report Width ; Why Can’t I Make My Report Narrower?
Sorting and Grouping Adding Totals to a Group ; Avoiding Splitting a Group across Pages ; Removing a Group
Adding Controls (8 pages) Introduction Locking a Control to the Mouse
Lines and Rectangles
Labels and Text Boxes Adding Labels ; Adding Bound Text Boxes ; Adding Unbound Text Boxes
Command Buttons
Check Boxes and Toggle Buttons
Option Groups Creating an Option Group
Combo/List Boxes
Multiple Forms (8 pages) What are Multiple Forms?
Combo Boxes Based on Other Tables Setting Combo Box Format Properties ; Setting Combo Box Data Properties ; Using Lookup Fields
Subforms Editing Subforms ; Editing a Subform in a Separate Window ; Creating a Subform using the Wizard ; Adding a Subform to a Main Form ; Adding Totals to a Subform
Showing Parent Fields on Child Forms
Two Ways to Program in Access (4 pages) Macros vs. VBA
Reasons to Learn Macros
Reasons to Learn VBA Converting Macros to Visual Basic
Basic Macros (14 pages) What are Macros?
Creating Simple Macros
Working with Macro Actions Adding Actions to a Macro ; Deleting Actions ; Moving Actions ; Collapsing and Expanding Actions ; Grouping Actions
Running Macros Running Macros from the Navigation Pane ; Attaching a Macro to a Form Button ; Running a Macro Step-by-Step ; Running One Macro from Another Macro
Adding Program Flow Elements to Macros Comments ; Creating Submacros ; Using Submacros ; Error Handling ; If Statements
Embedded Macros Creating an Embedded Macro
Data Macros Creating a Data Macro ; The Data Events ; Example of a Data Macro
Using the VB Editor (8 pages) Using the Access Window to Write Code
The VBA Code Editor Switching to/from the VB Editor ; Project Explorer ; The Properties Window ; Docking Windows
Modules Inserting a Module ; Renaming Modules ; Deleting Modules
Procedures Creating a Procedure ; Moving between Procedures ; Why Some Words Appear in Blue
Finding and Replacing Text Finding Text ; Replacing Text
Getting Help Using Help ; Getting Help using Search Engines ; AutoCompletion of Commands
Good Coding Practice (4 pages) Comments Commenting Out Multiple Lines
Layout of Code Splitting Lines of Code using Continuation Characters ; Indenting Lines
Modular Coding Advantages of Modular Coding
Using Different Modules
Checking and Running Macros (4 pages) Three Stages to Run a Macro Examples of the Different Stages of Error
Checking your Macros before Running Them On-Line Syntax Checking ; Turning Off Syntax Checking ; Compiling your Code
Running and Stopping Procedures Running the Currently Selected Procedure ; Stopping a Procedure which is Running
Talking to your users (6 pages) Basic Message Boxes Syntax of the MsgBox Command ; Named Arguments ; Building Up Messages ; Multiple Line Messages
Customising your Message Box Button Combinations Available ; Symbols Available
Asking Questions with Message Boxes The Possible Buttons ; The Different Button Values Possible
Asking for Information Syntax of the InputBox Function ; Example of an InputBox Function
Displaying Messages in the Status Bar
Using VBA - Front Menus (6 pages) Our Menu System
Creating a Splash Form Step One: Ensuring that Forms don’t Fill the Screen ; Step Two: Designing your Form ; Step Three: Ensuring your Form Opens Non-Maximized ; Step Four: Getting your Splash Form to Disappear ; Step Five: Getting your Splash Form to Autostart ; Other Startup Options
Creating a Menu Form
Using VBA - Validating Forms (4 pages) Overview of Form Validation Form Validation Methods Summarised
Setting Field Validation Rules
Checking Each Control before you Leave it
Checking a Record before you Save It
Using VBA - Criteria Forms (4 pages) What is a Criteria Form?
Creating the Criteria Form Creating the Form ; Writing the Code for the Show Button
Creating the Underlying Query
Creating the Report Redisplaying the Criteria Form when you Close the Report
Using VBA - Navigation Buttons (4 pages) Wizards versus Creating Your Own
Creating your Navigation Buttons Step One: Adding Form Buttons ; Step Two: Creating the Navigation Buttons Code ; Step Three: Attaching your Function to your Button

Access Visual Basic courseware

Here is a list of all of our Access Visual Basic manual chapters (you can download the ones marked):

Chapter Sections Subsections
Two Ways to Program in Access (2 pages) Old-Fashioned Macros Saving Macros as Visual Basic
Three Reasons to Avoid Macros
Using the VB Editor (8 pages) Using the Access Window to Write Code
The VBA Code Editor Switching to/from the VB Editor ; Project Explorer ; The Properties Window ; Docking Windows
Modules Inserting a Module ; Renaming Modules ; Deleting Modules
Procedures Creating a Procedure ; Moving between Procedures ; Why Some Words Appear in Blue
Finding and Replacing Text Finding Text ; Replacing Text
Getting Help Using Help ; Getting Help using Search Engines ; AutoCompletion of Commands
Good Coding Practice (4 pages) Comments Commenting Out Multiple Lines
Layout of Code Splitting Lines of Code using Continuation Characters ; Indenting Lines
Modular Coding Advantages of Modular Coding
Using Different Modules
Checking and Running Macros (4 pages)

Download this chapter 		Three Stages to Run a Macro Examples of the Different Stages of Error
Checking your Macros before Running Them On-Line Syntax Checking ; Turning Off Syntax Checking ; Compiling your Code
Running and Stopping Procedures Running the Currently Selected Procedure ; Stopping a Procedure which is Running
Talking to your users (6 pages) Basic Message Boxes Syntax of the MsgBox Command ; Named Arguments ; Building Up Messages ; Multiple Line Messages
Customising your Message Box Button Combinations Available ; Symbols Available
Asking Questions with Message Boxes The Possible Buttons ; The Different Button Values Possible
Asking for Information Syntax of the InputBox Function ; Example of an InputBox Function
Displaying Messages in the Status Bar
Variables (6 pages) The Various Data Types Available What Happens if you Use the Wrong Type
Using Variables Using the DIM Statement to Declare Variables ; What Happens when you Misspell Variables
Forcing Variable Declarations Forcing Variable Declaration for a Single Module ; Forcing Variable Declaration for All Modules
Scope of Variables
Constants
Referring to Variables or Constants in Queries
Arrays (4 pages) Simple Arrays Starting Arrays at 1, not 0
Changing the Dimension of Arrays
Multi-dimensional Arrays Looping over Multi-Dimensional Arrays
Two Practical Uses of Arrays Using SPLIT to Split a String into Words ; Using GetRows to Return an Array of Records and Fields
Writing functions (4 pages) What is a Function?
Creating Functions An Example of a Function ; Testing Functions in the Immediate Window
Using a Function in a Query Step One: Creating the Function ; Step Two: Test the Function ; Step Three: Incorporate the Function within your Query
Embedded procedures (6 pages) What are Embedded Procedures?
Attaching Code to Events Step One: Selecting the Form, Report or Control to Attach Code to ; Step Two: Choosing the Event ; Step Three: Writing the Code ; Alternative Method: Using the Code Window to Assign Code
Using Me Using Me to Avoid Hard-Coding a Form or Report Name ; Using Me to Get Autocompletion for Control Names
Major Events Useful Form Events ; Useful Control Events
Renaming Controls and Procedures
The VBA Rabbit Hole (8 pages) Collections and Objects
Referring to Form, Report and Control Objects Referring to Forms or Reports ; Referring to Controls
Properties and Methods Properties ; Changing Properties in Visual Basic ; Methods ; Using Autocompletion to Distinguish between Methods and Properties
The DoCmd Object
The Access Object Models The Full Access Object Model ; The Object Browser
Object Variables Declaring Variables – the SET Command ; Converting Control Types
Loops and Logic (6 pages) Simple Conditions Using IF Simple Conditions ; Testing Alternatives (ELSE)
Multiple Conditions Multiple Conditions Using Nested IF Statements ; Multiple Conditions Using the SELECT statement
Normal Looping Looping Until a Condition is True (DO UNTIL … LOOP) ; Looping While a Condition is True (DO WHILE … LOOP) ; Looping a Set Number of Times (FOR … NEXT) ; Exiting Prematurely from Loops
Looping Over Collections Some Examples of Looping Over Collections
Debugging (10 pages) Our Example
Debugging The Debugging Toolbar ; Starting the Debugger ; Stepping Over or Through ; Moving the Next Execution Line
Breakpoints Setting Breakpoints ; Removing Breakpoints ; Permanent Breakpoints (STOP) ; Conditional Breakpoints (Debug.Assert)
Using the Immediate Window Examples using the Immediate Window ; Executing Statements Using the Immediate Window ; Clearing the Immediate Window ; Using DEBUG.PRINT
Looking at Variables Hovering over Variables in Break Mode ; The Locals Window ; Watching Variables
The Call Stack Displaying the Call Stack ; Using the Call Stack
Handling errors (4 pages) The ERR Object
Trapping Errors and Resuming Execution
Putting It All Together – an Example
Raising Errors
QueryDefs and TableDefs (4 pages) Overview of QueryDefs
Creating and Deleting QueryDefs Creating Queries in Code ; Deleting Queries
An Example – Criteria Form
TableDefs – An Overview Listing out Tables ; The Bad News about TableDefs
Access objects (4 pages) What is an AccessObject?
Accessing an Object Choosing the Containing Object ; Collections Within Code Objects ; Collections within Access Database Data Objects
Putting it Together – Accessing Objects Example: Listing Form Names ; Example: Finding Out if a Form is Open ; Example: Listing Queries ; Example: Listing Database Contents
Working across applications (4 pages) Referencing Other Applications Possible Object Libraries Available
Example: Listing Data in Excel Step One: Defining the Problem ; Step Two: Referencing the Application ; Step Three: Instantiating the Application ; Step Four: The Final Code
Example: Working with Files
DAO and ADO (4 pages) Why you Need to Know This! Comparison of ADO and DAO ; Summary of Differences
Choosing DAO or ADO History of Usage of ADO/DAO by Access ; Considerations when Choosing DAO or ADO
Creating References What Happens if I Reference Both Libraries?
Disambiguation Duplicate References: Possible Solutions
Using Recordsets (DAO) (8 pages) Creating Recordsets The Types of Recordset ; Creating a Recordset
Working with Recordsets Moving through Recordsets ; Example of Moving through Recordsets ; Referring to Fields
Making Changes to Records Editing Records ; Adding New Records ; Deleting Records
Finding Records Creating the Right Type of Recordset for Finding ; Finding Until No More Matches Exists
Sorting and Filtering Sorting Recordsets ; Filtering Recordsets
Using Recordsets (ADO) (10 pages) Using the Right Object Library A Quick Reminder
Types of “Cursor”
Creating a Recordset Creating a Recordset Based on an SQL Command
Working with Recordsets Moving through Recordsets ; Example of Moving through Recordsets ; Referring to Fields
Editing Records Choosing a Lock Type ; Editing Records ; Adding New Records ; Deleting Records
Finding Records Repeated Finds
Importing Data to Excel Step One: Referencing ADO from Excel ; Step Two: Creating a Connection String ; Step Three: The Connection Code
Case Study - Navigation Buttons (4 pages) Wizards versus Creating Your Own
Creating your Navigation Buttons Step One: Adding Form Buttons ; Step Two: Creating the Navigation Buttons Code ; Step Three: Attaching your Function to your Button
Case Study - Database Security (2 pages) Creating a Table of Users
Authentication Code
Case Study - Front Menu (6 pages) Our Menu System
Creating a Splash Form Step One: Ensuring that Forms don’t Fill the Screen ; Step Two: Designing your Form ; Step Three: Ensuring your Form Opens Non-Maximized ; Step Four: Getting your Splash Form to Disappear ; Step Five: Getting your Splash Form to Autostart ; Other Startup Options
Creating a Menu Form
Case Study - Criteria Forms (4 pages) What is a Criteria Form?
Creating the Underlying Query
Creating the Criteria Form Creating the Form ; Writing the Code for the Show Button
Creating the Report Redisplaying the Criteria Form when you Close the Report
Case Study - Validating Forms (4 pages) Overview of Form Validation Form Validation Methods Summarised
Setting Field Validation Rules
Checking Each Control before you Leave it
Checking a Record before you Save It
The Access VBA Rabbit Hole (8 pages) Collections and Objects
Referring to Form, Report and Control Objects Referring to Forms or Reports ; Referring to Controls
Properties and Methods Properties ; Changing Properties in Visual Basic ; Methods ; Using Autocompletion to Distinguish between Methods and Properties
The DoCmd Object
The Access Object Models The Full Access Object Model ; The Object Browser
Object Variables Declaring Variables – the SET Command ; Converting Control Types

Excel courseware

Here is a list of all of our Excel manual chapters (you can download the ones marked):

Chapter Sections Subsections
Formulae and Functions (10 pages) Basic Formulae Operators ; Brackets in Formulae
Creating Formulae
Editing Formulae
Copying Formulae The Easiest Way to Copy a Formula ; Other Ways to Copy a Formula ; How Relative Cell Referencing Works
Functions Basic Functions ; Structure of a Function ; Typing a Function ; Using AutoSum to Create Quick Totals, Averages, Etc. ; The Quickest Way to Sum ; The Function Wizard
Status Bar Calculations
The Office Interface (5 pages) Interacting with Office The File Tab ; The Ribbon ; The Mini Toolbar ; The Quick Access Toolbar
Using the Ribbon Tabs, Groups and Commands ; Using Quick Keys ; Seeing More Options
Excel Basics (4 pages) What is Excel Used for?
Opening and closing Excel Opening/Starting Excel ; Closing/Ending Excel ; The Screen Layout
Structure of a Workbook Workbooks and Worksheets ; Cells, Rows and Columns ; Cell References
Entering and Editing Data (13 pages) Entering Data Typing in Data ; Types of Data ; Disguising Numbers ; Entering Dates ; Using AutoComplete to Enter Data ; Turning AutoComplete off ; Typing the Same Data into Multiple Cells
Editing and Removing Data Editing Data in Cells ; Clearing Cell Contents ; Clearing Formats from Cells
Inserting and Deleting Cells, Rows and Columns Inserting Rows and Columns ; Deleting Rows and Columns ; Inserting Cells ; Deleting Cells
Checking Spelling
AutoCorrect Turning AutoCorrect Off ; Changing the AutoCorrect Settings
Copying and Moving Cells (7 pages) Methods of Copying and Moving Cut, Copy, and Paste ; Drag and Drop ; Replacing and Inserting Cells with Drag and Drop ; Special Options for Pasting ; The Paste Tool ; Paste Special ; Paste Special using the Right-Click Menu ; The Format Painter
AutoFill Copying Using AutoFill ; AutoFill Paste Options ; The Clipboard ; Using the Clipboard ; Copying and Pasting Using the Clipboard ; Clearing the Clipboard
Filling Cells (4 pages) Creating Series Creating a Series from its First Two Values ; Filling a Series with the Right Mouse Button
Custom Lists
Flash Fill Flash Fill when Copying Down ; Flash Fill Tool using the Ribbon ; Other Examples of Flash Fill
Moving and Selecting in Excel (6 pages) Moving Around in Excel Using the Mouse to Move Around ; Keyboard Shortcuts for Moving Around ; Zooming the View
Selecting Cells Selecting Single Cells ; Selecting a Range of Cells ; Selecting a Large Range of Cells ; Selecting Multiple Ranges ; Selecting Entire Rows and Columns ; Selecting an Entire Worksheet ; De-Selecting Cells ; Using the Keyboard to Select Cells
Working with Files (10 pages) Introduction New File Formats ; Rules for Naming Files ; Saving a New Excel Workbook ; The Workbook’s File Path ; Re-saving an Existing Workbook
Closing Workbooks Closing a Workbook Using Excel ; Closing a Workbook Using the Task Bar
Creating New Workbooks
Opening an Existing Workbook Opening a Recently Used Workbook ; Opening a File not Used Recently ; Searching for Files ; Creating New Folders when Saving
Excel File Types Choosing a File Type ; Which File Type to Use
Files with Macros Macro-Enabled File Types ; Macro Security Settings
Styles (5 pages) Overview of Styles Benefits of Styles (1) – Consistent Formatting ; Benefits of Styles (2) – Protection
Applying Styles Adding Styles to the Quick Access Toolbar
Creating Styles Creating Partial Styles
Changing Styles
Merging Styles
Number and Date Formats (12 pages) What are Number Formats? Examples of Number Formats ; Examples of Date Formats
Formatting Dates How Dates and Times are Stored ; Quick Ways to Enter Dates and Times ; Quick Formatting Dates and Times ; Selecting Different Date/Time Formats ; Custom Date Formats ; Custom Date Formatting Codes ; Custom Time Formatting Codes ; Displaying Elapsed Times without Overflow
Standard Number Formats Standard Number Formats using the Ribbon ; Standard Number Formats using Format Cells
Custom Number Formats Creating Custom Number Formats ; General Rules for Custom Number Formats ; Example of a Typical Custom Number Format ; Re-using Custom Number Formats
Tricks with Number Formats Tricks with Text ; Hiding Parts of a Number Format ; Colouring Parts of a Number Format ; Getting Positive and Negative Numbers to Line Up ; Getting Decimal Points to Line Up ; Fractions ; Scaling Large Numbers ; Filling Spaces
Conditional Formatting (7 pages)

Download this chapter 		Creating Conditional Formats Applying a Pre-set Conditional Format ; Creating your own Conditional Format – a Case Study
Editing and Deleting Conditional Formats Changing the Order in which Rules Apply
More Sophisticated Rules Picking out Statistical Trends ; Using Formulae in Rules
Viewing Conditionally Formatted Cells/Rules Viewing All Conditionally Formatted Cells ; Viewing all Conditional Formatting Rules
Absolute and Relative References (3 pages) Relative Referencing
Absolute References When Relative Referencing doesn’t Work ; The Solution – Use Absolute References ; Fixing Just the Row or Column
Range Names (10 pages) What are Range Names? Going to a Range Name
Creating Range Names Creating Range Names using the Name Box ; Defining Range Names
Creating Formulae using Range Names Conditions under which Range Names Work ; Creating Formulae using Range Names ; Range Formulae using Columns ; Range Formulae using Single Cells
Changing Range Names Editing and Deleting Range Names ; Avoiding the Need to Change Range Names
Creating Range Names from Selected Cells
Scope of Range Names Choosing the Scope of a Range Name ; Duplicating Range Names by Copying Worksheets
Managing Range Names Listing out Range Names ; Applying Range Names
Quirks of Range Names Getting Aggregation Functions to Work ; Viewing Range Names ; 3-Dimensional Ranges
Cell Comments (5 pages) Overview of Comments The Comments Section of the Ribbon
Working with Comments Creating Comments ; Changing the Default User Name ; Editing and Deleting Comments ; Formatting Comments ; Printing Comments
Ways to Show Comments Hiding Red Triangles ; Showing/Hiding All Comments ; Selecting Cells Containing Comments
Printing (12 pages) Views of a Worksheet Page Layout View ; Page Break Preview ; Print Preview
Printing Choosing to Print ; Print Settings
Printing Areas of a Worksheet Setting a Print Area ; Setting Multiple Print Areas
Page Breaks Viewing Page Breaks ; Moving Page Breaks ; Inserting Page Breaks ; Removing Page Breaks
Fitting Worksheets to Pages Page Orientation ; Page Margins in Excel ; Changing Margins Using the Ribbon ; Changing Margins in Print Preview ; Scaling a Page ; Using Page Breaks to Scale a Page ; The Page Setup Dialog Box
Headers and Footers Adding a Header and Footer ; Inserting Headers and Footers with the Ribbon
Printing Titles Setting Print Titles
Formatting worksheets (12 pages) Methods of Formatting Using the Ribbon ; Using the Mini-Toolbar ; Using the Format Cells Dialog Box
Formatting Text Fonts and Sizes ; Simple Font Formatting
Cell Alignment Horizontal Alignment ; Vertical Alignment ; Other Alignments ; Rotating Text ; Indenting
Making Text Fit into Cells Column Widths and Row Heights ; Shrinking Text ; Wrapping Text ; Merging Cells
Formatting Numbers Choosing a Number Format ; More Number Formatting Options
Cell Backgrounds Fill Colours ; Fill Effects
Cell Borders Applying Borders ; Drawing Borders ; Border Styles and Colours ; Borders Using the Dialog Box
A Note about Gridlines
Multiple Worksheets (8 pages) Moving between Worksheets
Common Tasks Selecting and De-selecting Worksheets ; Renaming Worksheets ; Changing Worksheet Tab Colours ; Hiding and Unhiding Worksheets
Inserting and Deleting Worksheets Inserting Single Worksheets ; Inserting Multiple Worksheets ; Deleting Worksheets
Moving and Copying Worksheets Moving Worksheets within a Workbook ; Copying Worksheets within a Workbook ; Moving and Copying to a Different or New Workbook
Group Mode
Summing across Sheets
Consolidating Data (6 pages) Consolidation – Overview
Consolidation using Formulae
Consolidation by Position
Consolidation by Label Matching by Left Column ; Matching by Top Row ; Matching by Top Row and Left Column ; Consolidating the Data using your Label Choice
Linking to Source Data
Data Validation (8 pages) What is Data Validation?
Creating Validation Rules Step 1 – Saying what is Allowed ; Step 2 – Defining your Punishment for Mistakes
Referencing Formulae in Validation
Dropdown Lists Step 1 - Creating a Range Name ; Step 2 – Setting the List
Input Messages
Copying and Clearing Validation Settings Copying Validation ; Clearing Validation Settings
Highlighting Validated Cells Highlighting Cells which have Validation Applied ; Circling Invalid Data
Protection (8 pages) Types of Protection
Protecting Worksheets Unlocking Cells ; A Note on Unprotecting Cells using Styles ; Protecting and Unprotecting Worksheets
Protecting a Workbook’s Structure
Password-Protecting Workbooks
Preventing Accidental Changes to a Workbook
Hiding Worksheets Very Hidden Worksheets
Hiding Cells and Formulae Hiding Cells ; Hiding Formulae
Templates (6 pages) Why Use Templates?
Avoiding Templates
Preparing to Use Templates
Creating a Template
Setting the Default Template Step 1 - Finding your XLSTART Folder ; Step 2 - Moving or Copying your Template ; Step 3 – Calling your Template BOOK.XLTX ; Step 4 – Stop the Start-up Screen Appearing
Tables (14 pages) Definition of a Table
Creating and Removing Tables Creating Tables from Existing Data ; Creating Tables from Scratch ; Removing Tables
Sorting Tables Simple Sorting – 3 Methods ; Sorting by Two or More Columns ; Sorting by Colour
Simple Filtering Step 1 – Enabling Filtering ; Step 2 – Filtering by Columns
Removing Filters Clearing a Filter from a Single Column ; Clearing All Filters ; Removing Filtering
Filtering Specific Types of Data Filtering Numbers ; Filtering Text ; Filtering Dates
Removing Duplicate Rows
Advanced Filtering (Using OR and AND) Step 1 – Creating a Criteria Range ; Step 2 – Creating an Extract Range ; Step 3 – Applying Advanced Filtering
Formatting Tables (6 pages) Formatting Tables Changing the Scope of Formatting ; Applying a Format ; When Formatting appears not to Work
Creating Table Styles Step 1 - Creating the Table Style ; Step 2 – Formatting Parts of your Style ; Step 3 – Setting the Stripe Size
Subtotals (5 pages) What Subtotals Do
Creating Subtotals Step 1 – Sorting the Data Correctly ; Step 2 – Checking you can Create Subtotals ; Step 3 – Creating Subtotals ; Step 4 – Grouping and Outlining ; Step 5 – Copying the Visible Cells Only
Removing Subtotals
Pivot Tables (16 pages) Overview
Creating Pivot Tables Step 1 – Ensure you have a Table ; Step 2 – Create your Pivot Table ; Step 3 – Configure your Fields
Deleting Pivot Tables
Drilling Down
Working with Pivot Tables Renaming Fields ; Changing how Values are Calculated ; Changing Number Formatting ; Moving Rows and Columns ; Sorting Pivot Tables
Refreshing Pivot Tables Getting Pivot Tables to Refresh when you Open Workbooks
Working with Dates Grouping by Date
Filtering and Slicers Filtering a Pivot Table ; Creating Slicers ; Removing Slicers (or their Filters) ; Changing the Number of Slicer Columns ; Changing the Style of a Slicer
Formatting Pivot Tables
Choosing what to Display Hiding Field Captions ; Inserting Blank Rows ; Subtotals and Totals ; Changing Report Layout ; Repeating Item Labels
IF Functions (7 pages) Overview of IF Functions Relational Operators
Creating IF Functions Typing IF Functions ; Using the Function Wizard
Nesting IF Conditions Step 1 – Define the Conditions ; Step 2 – Create the Nested IF Function ; An Alternative: the IFS function
The SWITCH Function
Combining IF with Other Logical Functions
The VLOOKUP Function (7 pages) Types of Lookup Table Exact Matches or Not? ; Horizontal or Vertical?
Exact Match Lookup Tables Syntax of the Exact VLOOKUP Function ; Creating a Range Name for your Lookup Table ; Creating the Lookup Formula
Inexact Matches (Continuous Value Lookups) Creating the Lookup Table ; Giving the Lookup Table a Range Name ; Creating the VLOOKUP Formula
Trapping Errors Method 1 - Preventing Errors Happening with Data Validation ; Method 2 - Adding Outliers to your Input Table ; Method 3 – Converting Invalid Values using IF ; Method 4 – Trap Errors when they Occur
Other Lookup Functions (8 pages) Combining MATCH and INDEX Reasons to Prefer MATCH/INDEX to VLOOKUP ; The Range Names for our Example ; The Syntax of MATCH ; Using MATCH to Find Row/Column Numbers for our Example ; Syntax of the INDEX Function ; Creating the INDEX Function for our Example ; Combining MATCH and INDEX in a Single Function
The CHOOSE Function
The OFFSET Function Basic Offsetting ; Setting the Number of Rows/Columns Returned
The INDIRECT Function Example: Picking Out Figures from a Chosen Worksheet
Date and Time Functions (3 pages)

Download this chapter 		Returning Date Information Returning Parts of a Date ; Returning today’s Date/Time ; Returning Parts of a Time ; Converting Numbers/Text into Dates/Times
Manipulating Dates Returning Different Dates ; Returning the Difference between Dates
Text Functions (4 pages) List of Text Functions Searching and Replacing Text ; Extracting one String from Another ; Converting Strings ; Other Text Functions ; Joining Multiple Items ; Converting Numbers and Dates to Text
Text Functions – a Case Study
Information Functions (2 pages) The CELL and INFO Functions The INFO Function ; The CELL Function
Sheet Number / Numbers The SHEET Function ; The SHEETS Function
Inspection and Error Functions (2 pages) Inspection Functions Non-Error Inspection Functions ; Error Inspection Functions
Handling Errors General Error Trapping using IFERROR ; Avoiding the Error in the First Place
Conditional Statistics (4 pages) Overview of Conditional Statistical Functions Using IF instead of Conditional Statistical Functions ; The Special Functions Available
SUMIFS, COUNTIFS, etc Syntax of the Functions ; Worked Example ; Example with Multiple Criteria and Wildcards ; AVERAGEIF, COUNTIF and SUMIF ; Using with a Summation Range ; Combining the Criteria and Summation Range
The SUMPRODUCT Function (4 pages) Our Example
Solutions without SUMPRODUCT Summing the Results of IF Functions ; Using SUMIFS
The SUMPRODUCT Function What Summing Products Means ; Vectors of TRUE / FALSE values ; Forcing SUMPRODUCT to Calculate True/False Correctly ; Weighted Averages using SUMPRODUCT
Dynamic Range Names (4 pages) Examples of Dynamic Range Names
Making a Range Name Dynamic The Principle: the OFFSET Function ; Creating the Range Name ; Using Dynamic Range Names
Dynamic Range Names and Charts Creating the Range Names ; Making the Chart Refer to these Ranges
Grouping (Outlining) (4 pages) Using Grouping
Creating Grouping/Outlining Creating Automatic Outlines ; Manually Grouping Rows/Columns
Problems with Grouping/Outlining Changing Outlining Position ; Using Grouping/Outlining with Protection
Removing Outlining/Grouping Removing Outlining/Grouping from Selected Rows/Columns ; Removing All Outlining
Recording Macros (12 pages) Introducing Macros
Displaying the Developer Ribbon Tab
Recording a Macro Step 1 - Preparing to Record ; Step 2 – Starting to Record ; Step 3 – Record your Macro ; Recording Locations and the Personal Macro Workbook
Working with Recorded Macros Running a Recorded Macro ; Starting to Edit a Macro ; Using the VBA Code Editor
Macro Workbooks (Saving and Opening) Saving a Workbook Containing Macros ; Opening a Workbook Containing Macros
Relative References An Example Needing Relative References ; Absolute Code ; Switching to Relative Recording
Ways to Run Macros Assigning Macros to Shapes or Pictures ; Customising the Quick Access Toolbar ; Attaching Macros to Buttons
Charts (18 pages) Overview
Selecting Data The Golden Rules for Selecting Chart Data ; Selecting a Simple Data Range ; Selecting Multiple Ranges ; Selecting Non-Contiguous Ranges
Creating Charts
Chart Location Embedded Charts ; Printing Embedded Charts ; Separate Sheet Charts ; Switching between Embedded and Separate Sheet Charts
Chart Types, Templates and Defaults Changing a Chart’s Type (Including Chart Templates) ; Creating Chart Templates ; Setting the Default Chart
Formatting Charts
Formatting Parts of a Chart The Parts of a Chart (Chart Elements) ; Selecting Part of a Chart ; Selecting a Data Point or Legend Entry ; Formatting Part of a Chart ; Using the Formatting Task Bar ; Worked Example: Setting the Gradient Fill for a Chart Title
Showing/Hiding Parts of a Chart
Specific Chart Element Consideration Working with Data Tables ; Data Labels ; Axes, Scaling, Tick Marks and Gridlines
Editing Data Changing the Data being Charted Visually ; Switching Rows and Columns ; Filtering Data and Labels
Sparklines (6 pages) Introduction to Sparklines What Sparklines Are ; Sparklines can Contain Data
Creating Sparklines
Grouping and Ungrouping Sparklines Ungrouping Sparklines ; Grouping Sparklines
Removing Sparklines
Editing Sparklines Changing the Sparkline Type ; Changing Line and Marker Colours ; Sparkline Markers ; Changing the Axis Settings
Advanced Charts (1) (11 pages) Overview
Combination Charts
Trendlines Some Types of Trendline ; Adding Trendlines to Charts ; Choosing the Trendline Type ; Displaying Regression Statistics ; Extrapolating Trendlines
Scatter and Bubble Diagrams Scatter Diagrams ; Displaying a Regression Line ; Bubble Charts ; Customising Bubble Charts
Stock Charts
Lines and Up/Down Bars Up/Down Bars ; Series Lines
Error Bars
Picture Charts Tiling Pictures
Advanced Charts (2) (8 pages) Sunburst Charts
Treemap Charts
Waterfall or Bridge Charts
Histograms
Box and Whiskers Charts Creating a Box and Whiskers Chart ; Interpreting the Box and Whiskers
Pareto Charts
Modelling Guidelines (6 pages) Guideline 1 – Use a Cover Sheet
Guideline 2 – Separate Inputs and Calculations Checking for Rogue Formulae
Guideline 3 – Use Styles
Guideline 4 – Use Range Names
Guideline 5 – Keep Formulae Simple
Guideline 6 – Copy Formulae Across
Guideline 7 – Use Consistent Period Headings
Masking (2 pages) Masking
Logical Functions
Cashflow (3 pages) Template for a Cashflow Calculation
Calculating Interest Step 1 – Splitting the Cash Stream into Positive and Negative Amounts ; Step 2 – Calculating the Deposit/Loan Rate ; Step 3 – Calculating the Deposit/Loan Amount
Investment Appraisal (5 pages) Discount Rates
Discounted Cashflow
NPV and IRR Functions NPV Function ; IRR Function ; Using IRR or NPV to Assess an Investment ; Non-Periodic Cashflows using XNPV and XIRR ; Modified Internal Rate of Return Function
Array formulae (5 pages) What Array Formulae Do Distinguishing Features of Array Formulae
Working with Arrays Creating an Array Formula ; Editing and Deleting Arrays ; Selecting Arrays
Matrices
Array Constants Creating and Using a Range Name to Store the Constants
Scenarios (6 pages) Overview of Scenarios
Creating a Scenario Base case scenarios ; Starting the Scenario Manager ; Creating a Scenario
Showing Different Scenarios Showing Different Scenarios using the Ribbon ; Switching Scenarios using the Quick Access Toolbar
Editing, Deleting and Merging Scenarios Editing or Deleting Scenarios ; Merging Scenarios
Summarising Scenarios
Data Tables (5 pages) Overview of Data Tables One-Way and Two-Way Data Tables ; Example of a Data Table
One-Way Data Tables Step 1 – Create your Formulae ; Step 2 – Create your Inputs ; Step 3 – Create the Data Table Itself
Two-Way Data Tables Step 1 – Create your Formula ; Step 2 – Create your Inputs ; Step 3 – Create the Data Table
Goal-Seeking (2 pages) Goal-Seeking Example
Using Goal-Seeking
Excel Short-Cut Keys (8 pages) CTRL and SHIFT Key Combinations SHIFT and CTRL keys ; General CTRL Keys in Excel ; CTRL Keys with Letters
Function Keys F1 to F6 Keys ; F7 to F12 Keys
Other Special Keys Special Keys – Part 1 ; Special Keys – Part 2 ; Special Keys – Part 3

Excel VBA Macros courseware

Here is a list of all of our Excel VBA Macros manual chapters (you can download the ones marked):

Chapter Sections Subsections
The Visual Basic Editor (10 pages) The Visual Basic Editor Displaying the Developer Ribbon Tab ; Opening the VB Editor
The VBE Screen Opening and Closing Windows ; Repositioning Windows ; Docking Windows ; Controlling Which Windows are Dockable
The Main VBE Windows The Project Explorer ; The Properties Window ; The Object Browser
VBA Help Help in Office 2013 ; Launching the Help System
VBE Settings The Options Dialog Box ; Changing Font Formatting Options ; General Options ; Displaying Extra Toolbars
Useful Keyboard Shortcuts
Writing Simple VBA Code (14 pages) Modules Inserting a Module ; Opening and Closing Modules ; Renaming Modules ; Naming Rules in VBA ; Naming Conventions ; Removing Modules ; Exporting Modules ; Importing Modules ; Copying Modules to Other Projects
Writing Procedures Types of VBA Procedure ; Inserting Procedures ; Starting a Subroutine ; Switching off Syntax Error Messages ; Setting the Scope of a Procedure
Writing Neat Code Commenting Your Code ; Commenting Out Multiple Lines of Code ; Using Blank Lines and Indenting ; Indenting Multiple Lines ; Changing Indenting Settings ; The Continuation Character
Writing Simple VBA Instructions Objects ; Methods and Properties
Tools to Help with Writing Code Choosing Which Tools are Enabled ; Using IntelliSense to Write Code Faster ; Using Tooltips ; Viewing Data Tips
Useful Keyboard Shortcuts
Saving and Opening Files (6 pages) Saving VBA Code Where is Code Stored? ; Saving VBA Code ; Choosing the Correct File Type
The Personal Macro Workbook Creating the Personal Macro Workbook ; Viewing the Personal Macro Workbook in the VBE ; Viewing the Personal Macro Workbook in Excel ; Saving the Personal Macro Workbook ; Where the Personal Macro Workbook is Stored
Opening Files Which Contain VBA Code Choosing to Enable VBA Content ; Macro Security Settings
Trusted Documents Viewing Trusted Document Settings ; Disabling Trusted Documents
Running VBA Code (8 pages) Running Code from Excel Choosing from a List of Macros
Running Code from the VBE Running a Subroutine ; The Debug Toolbar ; Compiling Code ; Stepping Into and Through Code ; Reaching the End of a Procedure ; Interrupting a Running Procedure
When Things Go Wrong Syntax Errors ; Compile Errors ; Run-Time Errors
Useful Keyboard Shortcuts
Basic User Interfaces (8 pages) Keyboard Shortcuts Assigning Keyboard Shortcuts in Excel ; Assigning Keyboard Shortcuts in Code
Form Control Buttons Drawing Form Control Buttons ; Editing Form Control Buttons
ActiveX Command Buttons Drawing ActiveX Command Buttons ; Attaching Code to the Click Event
AutoShapes and Pictures Inserting Shapes and Pictures ; Assigning a Macro to a Shape or Picture
The Excel Ribbon Modifying the Quick Access Toolbar ; Creating Ribbon Tabs ; Altering Images on Ribbon Tools ; Resizing Ribbon Tools ; Resetting Ribbon Customisations ; Importing and Exporting Ribbon Customisations
Workbooks and Worksheets (10 pages) Working with Workbooks Referring to a Workbook by Name ; Referring to a Workbook by Index Number ; Referencing the Most Recently Opened Workbook ; The Difference between ActiveWorkbook and ThisWorkbook ; Opening a Closed Workbook ; Creating New Workbooks ; Saving Workbooks
Referring to Worksheets Referring to Worksheets by Name ; Why IntelliSense Doesn’t Work with Worksheets ; Referring to Worksheets by Index Number ; Using Sheet Codenames ; Referring to the Active Sheet ; The Difference between Worksheets and Sheets
Activating vs. Selecting Sheets Selecting Multiple Sheets ; Extending the Sheet Selection ; Activating a Sheet in a Selection ; Referencing Multiple Selected Sheets
Creating New Sheets Adding New Sheets ; Controlling the Position of New Sheets ; Adding Multiple Sheets
Deleting Sheets Deleting Single or Multiple Sheets ; Supressing Warning Messages ; Deleting all Charts or Worksheets
Working With Ranges (14 pages) Referring to a Range Object Referring to a Single Cell ; Referring to a Block of Cells ; Using Range Names to Refer to Cells ; Referring to Non-Contiguous Ranges ; An Old-Fashioned Way to Refer to Ranges
The Cells Property Referring to a Cell with Row and Column Numbers ; Referring to a Block of Cells ; Referring to Every Cell on a Worksheet
Rows and Columns The Range, Rows and Columns Properties ; The EntireRow and EntireColumn Properties
Referring to the Active or Selected Cell ActiveCell and Selection ; Activate vs. Select
The Offset Property Offsetting from the ActiveCell ; Offsetting a Block of Cells
The End Property The Four Directions of the End Property ; Finding the Start of a List ; Finding the Bottom Right Corner of a Table ; Finding the Next Blank Cell in a Column ; Selecting from the Top to the Bottom of a List ; Dealing with Blank Cells
Referring to Regions of Cells The CurrentRegion Property ; The CurrentArray Property
Resizing Ranges The Resize Method ; Resizing with the Range Property
Referring to Special Cells Specifying the Type of Values
Referring to Used Cells Referring to the Used Range ; The Last Used Cell ; The Last Populated Cell
Colours in VBA (2 pages)

Download this chapter 		Excel Colours The Two Colour Properties of a Range ; The ColorIndex Colours
Colour Numbers and Names VBA Colour Constants ; Excel’s RGB Constants ; The RGB Function
Displaying Messages (6 pages) The MsgBox Function Syntax and Parameters of a Message Box
Displaying Messages Displaying a Simple Message ; A Note on Using Parentheses ; Concatenating a Message ; Changing Text Alignment ; Using Multiple Lines ; Customising the Title
Icons and Buttons Modifying the Buttons ; Setting the Default Button ; Displaying Icons ; Combining Buttons and Icons
User Inputs (12 pages) Asking Users for Input Where to Store User Input
Asking a Question with a Message Box The Possible Results of a Message Box ; Storing the Result of a Message Box ; Testing Which Button was Clicked
The VBA InputBox Function Syntax and Parameters of the InputBox Function ; Using an Input Box to Ask a Question ; Setting a Default Value ; What Happens if You Click Cancel? ; Inputting Different Types of Data ; Dealing with Data Type Mismatches
The Excel-Specific InputBox Method Using the Excel Input Box ; Customising the Title and Default Value ; Setting the Data Type of the Input Box ; Entering an Invalid Value ; Returning Boolean Values ; Clicking Cancel ; Selecting Cells ; Returning a Reference to a Range ; Creating a Formula ; Returning an Array of Values
Variables and Data Types (14 pages) Data Types in VBA Summary of the VBA Data Types
Declaring and Using Variables The Dim Statement ; Writing To and Reading From Variables
Declared vs. Non-Declared Variables Non-Declared Variables ; Explicitly-Declared Variables ; Forcing Explicit Variable Declaration
Variables and Data Types Data Type Names vs. Type-Declaration Characters ; The Variant Data Type ; Declaring Multiple Variables ; Choosing the Correct Data Type
Converting Variable Data Types Implicit Data Type Conversion ; The Problem with Implicit Type Conversion ; Explicit Data Type Conversion ; Checking for Dates and Numbers ; Determining the Data Type of a Value
The Scope of Variables Procedure Level Variables ; Module Level Variables ; Project Level Variables ; Static Variables and Procedures
Constants Declaring a Constant ; The Scope of Constants
Working with Data (9 pages) Manipulating Data The Three Main VBA Data Types ; The Basic VBA Operators ; Manipulating Values using Functions ; Why Some Functions End with a $ Sign ; Using Excel’s Worksheet Functions
Working with Numbers Testing if a Value is a Number ; Useful Numeric Functions
Working with Dates Testing if a Value is a Date ; Writing Dates in the VBE ; Arithmetic with Dates ; Useful Date Functions ; Intervals for Date Functions ; Setting the First Day of the Week ; Formatting Dates
Working with Strings Concatenating Strings ; Character Codes ; Special Character Constants ; Case Sensitivity ; Useful String Functions
Testing Conditions (9 pages) The If Statement Single-Line If Statements ; The Else Clause ; Block If Statements ; Nested Ifs ; The ElseIf Statement
Logical Tests and Operators Comparison Operators ; Logical Tests and Boolean Values
Combining Conditions The Or Operator ; The And Operator ; The Xor Operator
Comparing Strings Testing if Two Strings are Equal ; Converting the Case of Text ; Making All Text Comparisons Case-Insensitive ; Relative Comparisons with Strings ; The Like Operator and Wildcards
Conditional Functions The IIf Function ; The Switch Function
The Select Case Statement A Basic Select Case Statement ; Testing Multiple Values ; Testing a Range of Values
For Next Loops (4 pages) The For Next Loop Looping a Set Number of Times ; The Step Statement ; Exiting from a For Next Loop ; Nesting For Next Loops ; Looping a Variable Number of Times ; Looping Through Collections
Conditional Loops (4 pages) The Do Loop Exiting from a Do Loop
Do Until Loops Writing a Do Until Loop ; The Loop Until Statement ; Breaking Out of a Loop
Do While Loops Writing a Do While Loop ; The Loop While Statement
The While Wend Statement Writing a While Wend Loop ; Disadvantages of While Wend
How VBA Works (12 pages) Object Oriented Programming The Building Blocks of an Object Oriented Language
Objects Referring to Objects by Name ; Referring to Objects by Index Number ; Qualifying References to Objects ; Using Keywords to Reference Objects ; Using Object Codenames ; Using Object Variables
Collections Referring to Collections ; Adding Items to a Collection ; Counting Items in a Collection
Methods Applying Methods to Objects ; Passing Arguments to Methods ; Returning Values and References from Methods ; When to use Parentheses
Properties Writing to a Property ; Read-Only Properties ; Property Data Types ; Reading from a Property ; Properties and Parameters
Getting Help in VBA The Object Browser ; Context Sensitive Help ; Recording a Macro
For Each Loops (6 pages) Looping Through Collections The For Each Loop ; A Basic Example
Looping Over Worksheets, Charts and Sheets Protecting all Worksheets ; Excluding Worksheets ; Looping Through Chart Sheets ; Looping Through All Sheets ; Looping Through Objects on a Sheet
Looping Over the Workbooks Collection Processing all Open Workbooks
Looping Over a Collection of Range Objects Specifying the Range to Loop Over ; Looping Through a Column of Data
Nesting For Each Loops Looping Over Shapes on All Worksheets ; Looping Through Sheets in All Open Workbooks
Writing Modular Code (7 pages) Modular Code Our Example
Breaking a Procedure into Parts Creating Module Level Variables ; Getting Input from the User ; Retrieving the Related Values ; Building and Showing a Message ; Putting it all Together
Procedures and Parameters Our Example ; Defining Parameters ; Calling a Procedure which has Parameters ; Optional Parameters ; Assigning Default Values to Parameters ; Testing for Missing Arguments
Debugging Modular Code Viewing the Definition of a Procedure ; Stepping Over a Procedure Call
Creating Functions (2 pages) Functions vs. Subroutines Returning a Value from a Function ; Returning a Reference from a Function ; Calling a Function ; Using Functions in a Worksheet ; Defining Function Parameters
Debugging (10 pages) Debugging Code Errors vs. Bugs ; The Debug Toolbar
Running Code Running a Procedure from Start to End ; Running a Procedure in Break Mode ; Stepping Through Code ; Changing the Next Instruction ; Editing Code in Break Mode
Breakpoints Setting and Removing Breakpoints ; The Stop Statement ; Breaking Conditionally
The Immediate Window Executing Instructions in the Immediate Window ; Asking Questions in the Immediate Window ; Printing to the Immediate Window
The Locals Window Observing Variables
The Watch Window Adding an Expression to Watch ; Types of Watch ; Adding a Quick Watch ; Editing and Removing Watches
The Call Stack Displaying the Call Stack ; Using the Call Stack
Useful Keyboard Shortcuts
Handling Errors (12 pages) Run-Time Errors in VBA
Error Handling in VBA Identifying Potential Run-Time Errors ; The On Error Statement
Using the On Error Statement Ignoring Run-Time Errors ; Disabling an Error Handler
Creating a Custom Error Handler Redirecting Your Code ; Writing the Error-Handling Section ; Exiting a Procedure before the Error-Handling Code ; The Complete Example ; Creating Multiple Error Handlers
Resuming After an Error Resuming at the Original Line ; Resuming at the Next Line ; Resuming at a Specified Line ; Why use Resume and Not GoTo?
The Err Object Getting the Error Number and Description ; A Catch-All Approach to Error-Handling
Raising Custom Errors Raising a Custom Error
Errors in Multiple Procedures Creating a Top-Level Error Handler
Events (14 pages)

Download this chapter 		Event Handlers Objects Which Have Events ; Event Procedures vs. Normal Procedures
Creating a Simple Event Handler Accessing the Object’s Code ; Choosing the Event ; Writing the Code ; Triggering the Event
Workbook Events The Before Close Event ; The Before Save Event ; The Before Print Event ; The New Sheet Event ; New Chart
Worksheet Events The Selection Change Event ; The Change Event ; Checking if the Target is Within a Specific Range ; Dealing with Multiple Changed Cells ; Referencing Cells in Worksheet Modules
ActiveX Controls Drawing ActiveX Controls ; Changing Properties of the Control ; Adding Code to the Control’s Events ; Prevent Controls from Taking the Focus
Application-Level Events Declaring a Variable with Events ; Viewing the Events of the Application ; Enabling the Application’s Events
Creating User Forms (16 pages) User Forms Creating a Working Form ; Our Example
Creating a User Form Inserting a User Form into a Project ; Switching Between Form Views ; Removing Forms
Form Properties Changing the Properties of a Form ; Some Common Form Properties ; Choosing Colours ; Setting Font Properties
Form Controls The Toolbox ; Drawing a Control on a Form ; Our Example ; Naming Controls
Manipulating Controls Selecting a Control ; Selecting Multiple Controls ; Resizing Controls ; Moving Controls ; Deleting Controls ; Copying and Pasting Controls
Laying Out Controls The Form Grid ; The UserForm Toolbar ; The Dominant Control ; Aligning Controls to Each Other ; Distributing Controls ; Aligning Controls to the Form ; Making Controls the Same Size ; Changing the Z-Order of Controls ; Arranging Buttons
Grouping Controls Grouping a Set of Controls ; Using Frames to Group Controls
Control Properties Naming Controls ; Naming Conventions for Controls ; Size and Position Properties ; Formatting Properties
Running User Forms (4 pages) Running a Form Choosing to Run a Form ; Closing a Running Form
Navigating a Form Tab Order ; Accelerator Keys ; Keyboard Shortcuts ; The Default and Cancel Buttons
Adding Code to Forms (12 pages) Making Forms Work Our Example
Running User Forms Running a Form as a Developer ; Running a Form as a User
Adding Code to a Form Viewing a Form’s Code
Referring to Forms and Controls Referring to a Form ; The UserForms Collection ; Looping Over the UserForms Collection ; Referring to Controls on a Form ; Looping Over the Controls Collection
Form and Control Events Initialising a Form ; Clicking the Cancel Button ; Clicking the Add to List Button ; Writing Modular Code in Forms
Validating User Inputs The Data Events of a Text Box ; Deciding on Your Validation Rules ; Creating Basic Validation Code ; Selecting the Text in a Text Box ; Ideas for Less-Intrusive Validation ; Resetting the Formatting Properties ; Using Hidden Labels ; Validation at the Form Level ; Setting the Focus to a Control ; Looping over Controls ; Validating Every Text Box in One Pass
Advanced Form Controls (16 pages) Beyond the Basics The Advanced Controls Available
Frames Drawing Frames and Controls ; Looping Through Controls in a Frame
Combo Box and List Box Controls Setting the Row Source ; The List Property ; Adding Items Individually ; Removing and Clearing Items ; Referring to the Selected Item ; Changing the List Style ; Restricting Choices in a Combo Box ; Allowing Multiple Selections in a List Box ; Referring to Multiple Selected Items ; Working with Multiple Columns
Option Buttons Grouping Option Buttons ; Framing Option Buttons ; Setting a Default Option for a Group ; Using the Value of an Option Button ; The Click Event
Check Boxes and Toggle Buttons Check Box and Toggle Button Values ; The Click Event
Spin Buttons and Scroll Bars Drawing Spin Buttons and Scroll Bars ; Scrolling Properties ; The Value Property ; The Change Event ; The SpinUp and SpinDown Events
MultiPage Controls Selecting Parts of a MultiPage Control ; Working with Pages ; The Index and Value Properties ; Looping Through Pages and Controls
TabStrip Controls Working with Tabs ; Drawing Tab Strips and Controls ; Using Tabs in Code ; Looping Over the Tabs Collection
Additional Form Controls (3 pages) Additional Form Controls Adding Items to the Toolbox ; Organising the Toolbox
Examples of Extra Controls The Date and Time Picker ; The RefEdit Control ; Progress Bars
Controlling Other Applications (12 pages) Referencing Object Libraries Setting a Reference to an Object Library ; The Default References ; References and the Object Browser ; Microsoft Office Version Numbers
An Example for Word Setting a Reference to the Word Object Library ; Declaring a Variable for Word ; Creating a New Instance of Word ; Showing and Activating Word ; Creating a New Document ; Writing and Formatting Text in Word ; Copying from Excel to Word ; Saving the Document and Closing Word ; The Complete Example
An Example for PowerPoint Setting a Reference to the PowerPoint Object Library ; Opening PowerPoint and Creating a Presentation ; Creating a Title Slide ; Copying from Excel to PowerPoint ; Moving and Resizing PowerPoint Objects ; Saving the Presentation and Closing PowerPoint ; The Complete Example
An Example for Outlook Setting a Reference to the Outlook Object Library ; The Complete Example
Controlling Applications without References The CreateObject Function ; Using Object Variables ; Converting Constants to Numbers
Crib Sheet (5 pages) VBA Reference Creating Procedures ; Selecting and Activating Things ; Selecting a Range Relatively ; Messages and Inputs ; Declaring Variables ; Conditional Statements ; Looping

Power BI for Excel courseware

Here is a list of all of our Power BI for Excel manual chapters (you can download the ones marked):

Chapter Sections Subsections
Introduction to PowerPivot (2 pages) The Course Database
The Power BI Components
Creating Data Models (12 pages) Going into PowerPivot
Importing Tables Step 1 – Connecting to the Database ; Step 2 – Choosing Data Tables ; Step 3 – Importing the Data ; Step 4 – Saving your Work (the xVelocity Engine)
Importing More Tables using an Existing Connection
Filtering Rows and Columns on Import Ignoring Columns ; Filtering Rows
Data and Diagram View Zooming In and Out in Diagram View
Hiding Columns and Tables from Client Views Hiding Columns ; Hiding Tables
Renaming Tables and Columns Renaming Columns ; Renaming Tables
Changing what you’ve Imported for a Table
Formatting Table Columns
Relationships (3 pages) Viewing Existing Relationships
Creating Relationships Creating Relationships in Diagram View ; Creating Relationships Manually
Pivot Tables using PowerPivot (16 pages) Creating the Data Model
Creating a Pivot Table Step 1 – Starting the Pivot Table ; Step 2 – Choosing Fields
Drill Down and Quick Explore Drill Down ; Deleting drill down results ; The Quick Explore Option
Working with Pivot Tables Renaming Fields ; Changing how Values are Calculated ; Changing Number Formatting ; Moving Rows and Columns ; Sorting Pivot Tables
Filtering
Slicers and Timelines Creating Slicers ; Removing Slicers (or their Filters) ; Changing the Number of Slicer Columns ; Changing the Style of a Slicer ; Inserting Timelines ; Controlling Multiple Pivot Tables with Slicers/Timelines
Formatting Pivot Tables
Choosing what to Display Hiding Field Captions ; Inserting Blank Rows ; Subtotals and Totals ; Changing Report Layout ; Repeating Item Labels
Using Excel Workbooks (4 pages)

Download this chapter 		Linking to Excel Tables Creating Tables ; Naming a Table ; Listing Tables ; Linking to Tables
Importing Excel Workbooks
Links to Other Data Sources (5 pages) Formats Supported
Linking to Access
Importing Cubes
Importing from the Clipboard Creating a New Table from the Clipboard ; Appending Existing Data from the Clipboard
Importing from a CSV File
Getting Data (8 pages) A Potted History of Get & Transform
What Get & Transform Does
Getting Data Step 1 - Creating the Query ; Step 2 – Load or Edit?
Managing Queries
Linking to a Website
Linking to an XML File
Other Data Sources
Transforming Data (12 pages) Editing a Query The Query Editor
Editing Query Steps The Hard Way – the M Language ; The Easy Way – Query Steps
Adding Simple Transforms Removing Columns ; Reordering Columns ; Filtering Rows ; Sorting Columns
Splitting Columns, etc. – a Case Study Step 1 – Splitting by Number of Characters ; Step 2 – Splitting by Delimiter ; Step 3 – Replacing Values ; Step 4 – Changing Data Types ; Step 5 - Merging Columns ; Step 6 – Inserting a Start Date Column ; Step 7 – Getting the Month Number ; Step 8 – Aggregating the Data ; Step 9 – Renaming Query Steps ; Step 10 – Running your Query
Managing your Queries Renaming Queries ; Grouping Queries
Recent Data Sources
Calculated Columns (7 pages) What are Calculated Columns? Differences between Calculated Columns and Excel Formulae
Creating Calculated Columns An Alternative Method, using the Keyboard
Working with Calculated Columns Moving Calculated Columns ; Sorting by Calculated Columns
The PowerPivot Function Wizard
Using RELATED to Link Tables
The BLANK Function Blank Arithmetic
Aggregator Columns and SWITCH Using SWITCH
Measures (10 pages) Query Context
Implicit Measures Viewing Implicit Measures
Using AutoSum Using an AutoSum Measure in a Pivot Table
Creating Measures in Excel Step 1 - Starting to Create the Formula ; Step 2 – Choosing the Table ; Step 3 – Completing the Dialog Box
Step 4 – Checking your Formula Step 5 – Using your Formula
Creating Measures in PowerPivot Step 1 – Choosing where to Start ; Step 2 – Creating the Formula ; Step 3 – Renaming the Measure ; Step 4 – Using the Measure
Measures vs. Columns – the Differences Difference 1 - Measures Aggregate ; Difference 2 - Use in Pivot Tables ; Difference 3 - Key Performance Indicators (KPIs)
Basic DAX Formulae (6 pages) Aggregation Functions
X-Suffix Functions Simple Aggregate Functions don’t Work with Expressions ; Aggregate Functions with X Suffix ; Our Example SUMX Function
Calculating Ratios Counting Rows ; Creating Ratios: Measures Needed ; The Final Pivot Table ; Summing Ratios doesn’t Work
DAX Variables
The CALCULATE Function (4 pages) Syntax of the CALCULATE Function
Replacing Filters Replacing a Single Filter ; Replacing More than One Filter
Removing Filters Removing a Single Filter ; Removing Multiple Filters
Using VALUES to Edit a Constraint On its own, VALUES is pointless ; Amending the Query Context Filter
The FILTER Function (3 pages) Syntax of the FILTER Function
Filtering to Show Subsets of Data
Removing Filters using ALL Removing and Partially Reinstating Filters
Ranking (3 pages) The RANKX Function Syntax of the Rank Function ; Entering RANKX in a Calculated Column
Using RANKX in Measures Step 1 – Creating an Intermediate Measure ; Step 2 – using ALL ; Changing the Sort Order ;
The EARLIER Function (2 pages) Case Study: Ranking using EARLIER
Case Study: Group Statistics
Calendars (13 pages) The Need for a Separate Date Table The Excel Approach – Date Functions ; The PowerPivot Approach – Create a Separate Table
Creating a Calendar Table Creating a Calendar in Excel ; Creating a Calendar Table in SQL Server ; Creating a Calendar Automatically
Using your Calendar Change 1 – Mark your Calendar as a Date Table ; Change 2 - Changing Column Data Types ; Change 3 - Creating Relationships
Creating Pivot Tables using Dates Sorting Months Correctly ; Sorting Months by Financial Year
Multiple Date Tables Solution 1 – Multiple Calendars ; Solution 2 – Multiple Relationships
Worked Example – Working Days Calculation Step 1 – Create a Bank Holiday Table ; Step 2 – Create a Relationship for the Bank Holiday Table ; Creating a Calculated Column for Working Days
Date Functions (9 pages) Formulae Covered The Underlying Principle
Period to Date The Year-to-Date Function Family ; Dates to Date Functions ; Fiscal Year Calculations
Comparing with Previous Periods The SAMEPERIODLASTYEAR Function ; Using DATEADD to Compare with any Previous Period
Parallel Periods Comparing with the Previous Year’s Year-to-Date
Moving Averages
Semi-Additive Measures Opening and Closing Balances – the Problem ; A Partial Solution using the LastDate/FirstDate Function ; Using LastNonBlank or FirstNonBlank
Hierarchies (3 pages) Using Hierarchies Advantages and Disadvantages of Hierarchies
Creating Hierarchies Step 1 – Getting the Correct Fields in a Single Table ; Step 2 – Creating the Hierarchy ; Step 3 – Including Fields in your Hierarchy ; Step 4 – Using a Hierarchy
KPIs (5 pages)

Download this chapter 		About KPIs The Three Parts of a KPI
Creating a KPI Step 1 – Create the Measures Needed ; Step 2 – Starting to Create the KPI ; Step 3 – Customising your KPI ; Step 4 – Displaying Parts of the KPI
Absolute Targets
Perspectives (2 pages) Overview of Perspectives What Perspectives Are ; Using Perspectives in PowerPivot
Creating and Editing Perspectives
Power View (4 pages) Enabling Power View
What is Power View?
Creating a Power View Visualisation
Why Not to Use Power View

Notes:

  1. Since Wise Owl print courseware double-sided to save trees, the number of physical pages in each chapter is roughly half of the number of sides quoted.
  2. Downloads are provided as examples only, and should not be used or distributed in any way or form without the prior written permission of Wise Owl.
This page has 0 threads Add post