Microsoft Excel - Pivot Tables

From Novice to Ninja

Testimonials (285 reviews)

Features

Premium video tutorials

Award-winning instructors

Personalized learning

Get certified

Learn at your own pace

Mobile (learn on-the-go)

Unlimited tests and quizzes

Regularly updated content


Overview

New to Pivot Tables or want to get more out of them? This online course is designed to give you a solid understanding of Pivot Tables, taking you from novice to ninja!

Pivot Tables are a powerful tool within Excel that can be used to analyze, sort, filter and present data in an understandable way.

In 38 engaging lessons you will learn best practices for preparing source data, using database data, how to preserve formats and create custom styles, sorting and filtering techniques, and much more.

Whether you use Excel for work or study, these tutorials will start you on your journey to becoming a Pivot Tables Ninja! Learn more about how a GoSkills Excel certification can boost your career.

The course is Excel 2016, 2019 and Microsoft 365 compliant (previously known as Office 365), so you can choose the version that works best for you.

If you’d like to learn more about Excel try our Basic, Advanced or Basic and Advanced Excel courses.

Want to be a more efficient Excel user? Start learning 200 of the best Excel shortcuts for PC and Mac.

Once enrolled, our friendly support team and tutors are here to help with any course related inquiries.


Summary

Skill level: Beginner
Certificate: Yes (Excel certification)
Lessons: 38
Accredited by: CPD
Pre-requisites: None
Versions supported: 2016, 2019, 365
Video duration: 3h 12m
Estimated study time: 19h for all materials

Accreditations and approvals

CPD - The CPD Certification Service.


Syllabus

1

What is a PivotTable - and Why Do You Care?

PivotTables open up your world to quickly summarize data and build solutions that others can explore.

Video time: 04m 17s

2

The PivotTable Life Cycle

Understanding how the life cycle of a PivotTable solution is different than an ad-hoc reporting solution.

Video time: 04m 28s

3

Creating Your First PivotTable

Creating your first PivotTable is the first step to unlocking serious Excel power in data optimization.

Video time: 05m 30s

4

Field Well Options

Determining the Field Well options you’d like to use to view your PivotTable data.

Video time: 04m 24s

1

Source Data Best Practices

Setting up your data sets to ensure that they will be able to be consumed by a PivotTable.

Video time: 05m 17s

2

Using Excel Ranges

Choosing between building quick and dirty PivotTables or future-proofing PivotTables for future updates.

Video time: 05m 28s

3

Preparing Source Data

When you need to aggregate multiple tables or create new fields in order to display properly in a PivotTable.

Video time: 05m 34s

4

Using External Data

Connecting to external data sources (such as databases), and pulling the data into Excel.

Video time: 05m 35s

5

Managing Power Queries

Updating a Power Query solution to a new file path, allowing us to refresh the file with a click.

Video time: 04m 39s

1

Totals and Sub-Totals

Use to turn totals and subtotals on/off where needed in your PivotTable.

Video time: 05m 27s

2

Choosing PivotTable Layouts

Determining the basic layout you’d like to use to display your PivotTable data.

Video time: 04m 53s

3

Grouping

Use to group different row or column fields together for a more logical display.

Video time: 05m 54s

1

Aggregation

When you wish to show different aggregation on your PivotTable fields than what is provided by default.

Video time: 05m 01s

2

Running Totals

When you need to display a running total on your PivotTable.

Video time: 05m 10s

3

Percent of x Calculations

When you need to display a value as the % of another value on your PivotTable.

Video time: 06m 13s

4

Difference From Calculations

When you need to display values that are a difference from another value in a PivotTable. This is very useful for showing growth/decline from previous periods.

Video time: 04m 37s

5

Percentage Difference From Calculations

When you need to display values that are a percentage of difference from another value in a Pivot Table.  This is very useful for showing growth/decline from previous periods.

Video time: 04m 10s

6

Top and Bottom x Items

When you want to filter your PivotTable to show the top/bottom x items or top/bottom x% of items in the data set.

Video time: 05m 17s

7

Calculated Fields

Understanding how to create calculated fields in a PivotTable.

Video time: 06m 31s

8

Calculated Items

Understanding how to create calculated items in a PivotTable.

Video time: 05m 51s

1

Value Field Formats

Various ways to display numbers in the values area of a PivotTable.

Video time: 04m 30s

2

Preserving Cell Formats on Update

How to force a PivotTable to preserve cell formats and column widths when it is updated.

Video time: 03m 41s

3

PivotTable Styles

Using pre-defined styles with a PivotTable.

Video time: 03m 14s

4

Custom PivotTable Styles

Creating your own style to use with a PivotTable.

Video time: 05m 16s

5

Conditional Formatting on PivotTables

Basics of applying conditional formats to a PivotTable.

Video time: 04m 55s

1

Basic Sorting

Basic sorting of row and column headers in a PivotTable.

Video time: 03m 24s

2

Custom Sorting

Creating custom sort orders for a PivotTable.

Video time: 05m 11s

3

Sorting Values

Sorting the values area of a PivotTable.

Video time: 05m 37s

1

Report Filters

Using Report Filters to narrow down results in a PivotTable.

Video time: 04m 37s

2

Built-in Filter Controls

Using row and column filters to narrow down results in a PivotTable.

Video time: 05m 18s

3

Slicers

Slicers are user-friendly filters for PivotTables.

Video time: 05m 17s

4

Timelines

Timelines are date specific filters for PivotTables.

Video time: 03m 48s

5

Filtering Multiple PivotTables

Filtering multiple PivotTables at once with Slicers.

Video time: 05m 22s

1

Show Details

Using Show Details to extract details from a summarized value.

Video time: 05m 04s

2

GETPIVOTDATA

Using the GETPIVOTDATA function to extract specific fields from a PivotTable.

Video time: 05m 06s

1

Creating PivotCharts

Creating PivotCharts out of a PivotTable.

Video time: 05m 04s

1

Automating Refresh

Automating PivotTable refresh operations.

Video time: 06m 21s

2

Pre-release Considerations

Items to consider before releasing your file to users.

Video time: 06m 06s

Download syllabus