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.
28 Apr 19 at
10:49
Hello,
Is it possible to buy VBA manuals from you?
Andy B
Reply to Andy BReply
28 Apr 19 at
19:19
Your best bet is to purchase our Excel VBA book using Lulu (a worldwide, print-on-demand publishing company). You can see more details about the contents of the book here.