Training videos for learning EXCEL VBA - PIVOT TABLES

Excel VBA - pivot tables - 8 videos

Excel VBA Part 51.1 - Introduction to Pivot Tables in VBA

Posted by Andrew Gould on 22 November 2016

A pivot table is a fantastic tool for summarising a large quantity of data. This video shows you the basics of creating pivot tables using VBA, starting with the creation of a pivot cache. You'll also see how to create new pivot tables, how to manipulate pivot fields and pivot items and how to apply filters to the pivot table.

Excel VBA Part 51.2 - Pivot Tables using an Access Database

Posted by Andrew Gould on 15 December 2016

This video explains how to create a pivot table based on an Access database. You'll learn how to create connections to external data sources, how to edit connection strings and how to create a pivot cache using the external connection. The video also shows you how to create queries in Microsoft Access, along with how to write basic SQL statements in your VBA code to create flexible queries that you can use to create pivot tables.

Excel VBA Part 51.3 - Pivot Tables using a SQL Server Database

Posted by Andrew Gould on 15 December 2016

This video explains how to create an Excel pivot table based on a SQL Server database. You'll learn how to create connections to external data sources, how to edit connection strings and how to create a pivot cache using the external connection. The video also shows you how to create views in SQL Server Management Studio, along with how to write basic SQL statements in your VBA code to create flexible queries that you can use to create pivot tables.

Excel VBA Part 51.4 - Pivot Tables and Consolidation Ranges

Posted by Andrew Gould on 15 December 2016

This video explains how to create an Excel pivot table based on multiple ranges of data spread across several worksheets. You'll learn how to use the Pivot Table Wizard to create a pivot table based on multiple consolidation ranges, as well as how to write code to achieve the same results. The video explains how to use an array to hold references to the different ranges of cells involved and how to populat this array dynamically for all of the data sheets in a workbook.

Excel VBA Part 51.5 - PowerPivot Data Models

Posted by Andrew Gould on 15 December 2016

The PowerPivot Data Model allows you to combine data from a variety of sources into one single object in a workbook - you can then use this model to create pivot tables using the joined data. This video explains how to create and edit the PowerPivot data model using dat from Excel worksheets, Access databases and SQL Server databases. You'll learn how to create a data model, how to add and remove tables and how to import or create relationships between the different tables in the model. The final part of the video explains how to create a single data model which combines data from Excel, Access and SQL Server

Excel VBA Part 51.6 - Pivot Charts

Posted by Andrew Gould on 15 December 2016

This video explains how to create and manipulate pivot charts in Excel VBA. You'll learn how to create a pivot chart based on a pivot table, how to create embedded chart objects and separate chart sheets and how to modify the fields of the pivot chart. The video also shows you how to apply and remove filters on the chart and how to quickly format the chart and chage its appearance.

Excel VBA Part 51.7 - Pivot Table Slicers

Posted by Andrew Gould on 15 December 2016

Slicers provide an interesting way to apply filters to pivot tables. This video explains how to write VBA code to create a slicer cache and a slicer, along with how to change the slicer's position and dimensions. You'll learn how to use a slicer to filter a pivot table and how to connect a slicer to multiple pivot tables to enable filtering of multiple pivots with a single slicer.

Excel VBA Part 51.8 - Pivot Table Date Fields and Timelines

Posted by Andrew Gould on 15 December 2016

You can do several useful things with dates in Pivot Tables, including grouping, filtering and applying timelines. This video shows you how to do all those things using VBA.

This page has 1 thread Add post
28 Dec 18 at 16:02

Hi,

I am following your videos on using VBA to apply filters to Pivot Tables and would like a copy of the movies database, as an Excel spreadsheet.

Can you please direct me to the appropriate location to download this.

Thanks

 

 

14 Jan 19 at 10:08

Hi Duggie,

You should find a link to download an Excel version of the Movies list on this page.  I hope that helps!

15 Jan 19 at 11:40

Thanks, hope you had a nice break.

 

 

02 Jan 19 at 12:55

Andrew is having a well-earned holiday, and will get back to you around the middle of January with an answer to this.

02 Jan 19 at 13:07

Break? I thought he enjoys his work so much, it feels like a break every day!

Andy B  
02 Jan 19 at 14:30

On behalf of Andrew, a Christmas "Ho Ho"!