Microsoft training videos created by Andrew Gould of Wise Owl (page 1 of 10)

Showing videos 1-20 (out of 187)

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.

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.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.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.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.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.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.

SQL Server 2016 Pt8 - The Wise Owl Movies Database

Posted by Andrew Gould on 15 December 2016

This video explains how to install the Wise Owl Movies database which is something you'll need to do if you want to follow most of our other tutorial videos! The video also explains how to create a database diagram to help visualise the structure of the database, as well as how to view and edit the data in the tables. The last part of the video explains a few of the design decisions we've taken to simplify things for the purposes of teaching and offers some alternative approaches to solving certain design challenges.

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 49 - Downloading Files from Websites

Posted by Andrew Gould on 21 November 2016

Excel VBA doesn't have a native method for downloading files from websites but you can declare an API function that will enable you to do this. This video takes you through the process of declaring the API function and using it in your code, along with a bunch of other useful techniques such as using folder pickers, creating folders with FileSystemObjects and opening a Windows Explorer window using the Shell function.

Excel VBA Part 50.2 - Embedding Charts in Worksheets

Posted by Andrew Gould on 21 November 2016

ChartObjects are charts that are embedded in a sheet in an Excel workbook. This video teaches you about the basic differences between charts and chartobjects, as well as how to change one type into another. You'll also learn a couple of neat tricks for positioning chartobjects on a sheet, including how to arrange multiple chartobjects so that they don't overlap.

Excel VBA Part 50.1 - Introduction to Charts in VBA

Posted by Andrew Gould on 17 November 2016

This video helps you to get started with charts in Excel VBA. You'll learn how to create a new chart sheet and set the chart's data source using a variety of techniques, including how to dynamically set the data source based on conditions. You'll also see lots of ways to change the appearance of the chart, including using chart layouts, chart colour schemes, chart styles and even how to create your own custom chart template.

Excel VBA Part 48 - Scraping Multiple Web Pages

Posted by Andrew Gould on 17 November 2016

What's better than scraping one web page? Scraping lots of them with the same procedure, of couse! This video explains how to loop over multiple pages using Microsoft's HTML and XML object libraries. You'll learn about HTML tags and classes, the Document Object Model and how to loop over elements on a page.

Excel VBA Part 47 - Browsing to Websites and Scraping Web Page

Posted by Andrew Gould on 14 November 2016

Scraping a web page in VBA involves getting a reference to an HTML document and then processing its elements to extract the useful parts and write them to another location in a readable format. This video shows you how to do that using both Internet Explorer and basic XML HTTP requests. You'll learn a bit about the Document Object Model, as well as how to identify HTML elements by name and by tag type. You'll also see how to loop through various collections of HTML elements and their children using an example involving exchange rate tables.

Excel VBA Part 46 - Querying Web Pages with Query Tables

Posted by Andrew Gould on 14 November 2016

Querying web pages in Excel VBA is simple when you use Query Tables. This video shows you how to create a query table linked to a web page, how to choose which tables from the web page to return, and how to pass values to a URL query string to create dynamic, updatable web queries in your Excel workbooks.

SQL Server 2016 Pt 7 - Computed Columns

Posted by Andrew Gould on 10 November 2016

A computed column in a SQL Server table allows you to store an expression which calculates a new value based on the data in other columns of the same table. This video explains how to create computed columns and how to persist the result of the calculation so that it's stored in the table. You'll also learn a little about SQL Server's expression syntax, including how to use some of its built-in functions, the difference between deterministic and non-deterministic functions, how to use Case expressions to test conditions and how to concatenate strings. We'll do this by building a few example computed columns including constructing a full name from first and last name columns, calculating age in days using the DateDiff function and calculating age in years using DateDiff, DateAdd and a Case expression.

SQL Server 2016 Pt 6 - Designing and Creating Relational Databases

Posted by Andrew Gould on 10 November 2016

Creating the optimal design for relational database can be a long and complex process - this video takes you through the key steps of normalising data including identifying repeating values, creating separate tables to store those values, and creating primary keys, foreign keys and relationships. The video also includes a section drawing database diagrams, which is the most fun you can have when designing a database!

SQL Server 2016 Pt 5 - Basic Table Design, Data Types and Constraints

Posted by Andrew Gould on 10 November 2016

Designing basic tables in SQL Server involves deciding which columns the table should include, what the data type of those columns should be (not always an easy choice!) and a few additional properties such as whether the column can be left empty (or NULL) and whether there should be any constraints on the values entered into the column. This video takes you through that process to design and create a simple table to store information about people. The video contains a discussion of the main text, number and date data types in SQL Server - you'll learn the difference between char, varchar, nchar and nvarchar data types, as well as the difference between exact and approximate numerics. The video also covers the basics of inserting and editing data before the final part shows how to automatically generate scripts that will recreate the table and its precious data in the event that you delete it.

Excel VBA Part 27b - Creating and Updating Linked Word Tables

Posted by Andrew Gould on 07 November 2016

Using Excel VBA to create a linked table in a Word document is pretty straightforward. Where things get tricky is when you move or rename the original Excel file. This video explains how to create the original linked table followed by how to update all of the links in a Word document by looping over its Fields collection. The end of the video also shows how to attach the code to the save events of the workbook so that you don't even have to remember to run it yourself!

SQL Server 2016 Pt 4 - Databases, Logins, Users, Roles and Schemas

Posted by Andrew Gould on 02 November 2016

Creating a new SQL Server database is pretty simple, as the first part of this video shows. What's a little more complex is configuring access to that database for other users. This video explains the basics of logins, users, server and database roles, permissions and schemas to provide you with an overview of what's possible.

This page has 0 threads Add post