Wise Owl Courseware
Microsoft Excel courseware and training manuals
We train on all manner of Excel courses (everything from pure Excel through PowerPivot to advanced VBA macro programming):
You can see details of how to license our Microsoft Excel courseware (including volume requirements) here.
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 - original file (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 | |
Pivot Tables (19 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 | |
Creating New Groups in a Pivot Table | Ungrouping ; | |
Displaying % of Grand Totals | ||
Report Filter Pages | ||
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? |
Inexact Matches (Continuous Value Lookups) | Creating the Lookup Table ; Giving the Lookup Table a Range Name ; Creating the VLOOKUP Formula | |
Exact Match Lookup Tables | Syntax of the Exact VLOOKUP Function ; Creating a Range Name for your Lookup Table ; Creating the Lookup 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 |
Advanced VBA courseware
Here is a list of all of our Advanced VBA manual chapters (you can download the ones marked):
Chapter | Sections | Subsections |
---|---|---|
VBA Recap (5 pages) | VBA Reference | Creating Procedures ; Selecting and Activating Excel Objects ; Selecting an Excel Range Relatively ; Messages and Inputs ; Declaring Variables ; Conditional Statements ; Looping ; Exiting from a Loop |
Object Oriented Programming (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
(12 pages)
Download this chapter |
Looping Through Collections | The For Each Loop ; A Basic Example |
Looping Over Excel Worksheets, Charts and Sheets | Protecting all Worksheets ; Excluding Objects from a Loop ; Looping Through Chart Sheets ; Looping Through All Sheets ; Looping Through Objects on a Sheet | |
Looping Over the Excel Workbooks Collection | Processing all Open Workbooks | |
Looping Over a Collection of Excel 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 | |
Looping Over Collections in Word | Looping Over Documents ; Looping Over Paragraphs ; Looping Over Sentences, Words and Characters | |
Looping Over Collections in PowerPoint | Looping Over Presentations ; Looping Over Slides ; Looping Over Objects on a Slide | |
Looping Over Collections in Access | Looping Over Controls on a Form ; Looping Over Access Database Objects | |
Looping Over Collections in Outlook | Looping Over Inbox Items ; Looping Over Email Attachments | |
Controlling Other Applications (17 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 ; Auto-Instancing Variables ; 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 ; Getting a Reference to a Running Application ; Testing the Version of an Application | |
Referencing Other VBA Projects | Setting a Reference to a VBA Project ; Creating Excel Add-Ins ; Loading Excel Add-Ins | |
Connecting to Databases (14 pages) | ActiveX Data Objects | A Brief Version History ; Referencing the ADO Library |
Connecting to an External Database | Setting the Connection String | |
Creating Connections in Access | Referencing the CurrentProject’s Connection | |
ADO Recordsets | Creating a Recordset ; Setting the Source of the Recordset ; Setting the Lock Type ; Setting the Cursor Type ; Opening and Closing a Recordset ; Copying Data into Excel | |
Moving in a Recordset | Moving the Cursor ; Reaching the End of a Recordset ; Looping Over a Recordset ; Referring to Fields | |
Finding and Filtering Records | The Find Method ; Repeated Finds ; Applying a Filter ; Removing a Filter ; Adding Criteria to a SQL Select Statement ; Creating Dynamic SQL Statements | |
Modifying Data | Adding New Records ; Editing Existing Records ; Deleting Records | |
ADO Commands | Creating a New Command Object ; Setting the Command Text ; Executing the Command | |
Using DAO | Referencing the Correct Object Library ; Opening a Database ; Creating a Recordset | |
Files and Folders (8 pages) | Working with Files and Folders | The Scripting Runtime Library ; Creating a FileSystemObject |
Basic File and Folder Techniques | Testing if a File or Folder Exists ; Creating a Folder ; Copying and Moving Files and Folders ; Deleting Files and Folders ; Renaming Files and Folders ; Getting a Reference to a File or Folder | |
Looping Over Files and Folders | Looping Over Files ; Looping Over Folders ; Recursively Looping Over Subfolders | |
Working with Text Files | Creating and Writing to a Text File ; Opening a Text File ; Reading from a Text File | |
Using VBA’s FileSystem Methods | Creating Folders ; Deleting Files and Folders ; Copying Files ; Renaming Files | |
File Dialog Boxes (6 pages) | Working with File Dialogs | Types of File Dialog Box ; Displaying a File Dialog Box ; Performing the Default Action |
Customising File Dialogs | Changing the Title and Button Name ; Setting the Initial Location ; Allowing Multiple Selections ; Creating File Filters | |
Picking Files and Folders | Returning a File or Folder Path ; Testing Which Button was Clicked ; Dealing with Multiple Selections ; Using Multiple File Dialogs | |
Class Modules (12 pages) | What are Class Modules? | Why Create Classes? ; Important Terminology ; Debugging in Class Modules |
Designing a Class | Our Example Film Class | |
Creating a Class | Inserting a Class Module ; Renaming a Class Module ; Creating a New Instance of a Class | |
Creating Basic Properties | Basic Properties ; Disadvantages of Basic Properties | |
Creating Full Properties | Assigning a Value to a Property ; Reading a Value from a Property ; Assigning an Object to a Property ; Writing Additional Code in Properties ; Read-Only Properties | |
Creating Methods | Writing Methods in a Class Module ; Using Class Methods | |
Class Module Events | Creating Class Module Event Handlers ; Triggering Class Events | |
Sharing Class Modules | Step 1 – Rename the VBA Project ; Step 2 – Make the Class Public ; Step 3 – Create a Function to Return an Instance of the Class ; Step 4 – Reference the Class Project ; Step 5 – Consume the Class | |
Collections and Dictionaries (12 pages) | What are Collections? | Custom Collections and Dictionaries |
Untyped Collections | Creating a New Collection ; Adding Items to a Collection ; Adding Custom Classes to a Collection ; Referencing Collection Items ; Removing Items from a Collection ; Looping Over Collections | |
Typed Collections | The Problem with Untyped Collections ; Creating a Collection Class ; Populating a Typed Collection ; Looping Over a Typed Collection ; Referencing Items in a Typed Collection | |
Dictionaries | Referencing the Scripting Runtime Library ; Creating a New Dictionary ; Adding Items to a Dictionary ; Referring to Dictionary Items ; Automatically Creating Keys ; Checking if a Key Exists ; The Compare Mode ; Removing Items from a Dictionary ; Replacing Dictionary Values ; Replacing Dictionary Objects ; Looping Over Dictionaries | |
Arrays (7 pages) | Overview of Arrays | Viewing the Contents of Arrays |
Declaring Arrays | Setting the Dimensions of an Array ; Changing the Base of Arrays ; Declaring Multi-Dimensional Arrays | |
Populating Arrays | Assigning Values to an Array ; Assigning Objects to Arrays | |
Reading from Arrays | Referring to a Specific Element ; Looping Over an Array ; The Bounds of an Array ; Using For Each Loops | |
Dynamic Arrays | Declaring an Empty Array ; Re-Dimensioning an Array ; Preserving the Contents of an Array | |
Arrays in Excel | Assigning a Range to an Array ; Calculating in an Array ; Assigning an Array to a Range | |
Modular Code, Parameters and Functions (11 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 ; ParamArrays | |
Passing Arguments ByRef and ByVal | Passing Arguments by Reference ; Passing Arguments by Value ; Passing Arguments in Parentheses | |
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 Modular Code | Viewing the Definition of a Procedure ; Stepping Over a Procedure Call | |
Constants and Enumerations (4 pages) | Working with Constants | Declaring Constants ; Referencing Constants |
Enumerations | Declaring Enumerations ; Referencing Enumerations ; Using Enumerations as Data Types ; Converting an Enumeration to Text ; Enumerations for Colours | |
Recursive Programming (3 pages) | Recursive Procedures | Why Use Recursive Procedures? ; Viewing the Call Stack |
Organisational Hierarchies | Creating the Base Organisation Chart ; Recursively Adding Nodes | |
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)
Download this chapter |
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 | |
Shapes (18 pages) | Introduction to Shapes | The Shapes Collection |
Referring to Shapes | Names and Index Numbers ; Referring to a Range of Shapes ; Referring to Selected Shapes ; Referring to Newly Added Shapes ; Looping Over the Shapes Collection | |
Shape Size and Position | Changing the Size and Position ; Sizing and Positioning Relative to Other Objects | |
Adding Shapes | Adding a Basic AutoShape ; Labels and Textboxes ; WordArt ; Pictures ; Form Controls | |
Formatting Shapes | Changing Shape Colours ; Colour Gradients ; Other Formatting Options ; Setting Default Shape Formats ; Copying Formats between Shapes ; Using Shape Styles | |
Shape Adjustments | Referring to Adjustments ; Adjusting Adjustments | |
Adding Text to AutoShapes | The TextFrame and TextFrame2 Objects ; Adding Text to a Shape | |
Formatting Text in a Shape | Basic Font Formatting ; Changing the Colour of Text ; Formatting Part of the Text ; Aligning Text in a Shape ; Changing Text Orientation | |
Connectors and Lines | Drawing Straight Lines ; Adding Multi-Point Lines and Curves ; Drawing Freeform Lines ; Creating Enclosed Shapes ; Connectors |
Notes:
- 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.
- 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.