Microsoft Excel Pivot Tables - Beginner to Advanced

Name: Microsoft Excel Pivot Tables - Beginner to Advanced
Rating: 4.7 (4609 reviews)

Quickly calculate, summarize and analyze large data sets with Microsoft Excel Pivot Tables

Created byKyle Pew • 2,000,000+ Students, Office Newb

Last updated 1/2023

English

What you'll learn

Prepare Microsoft Excel data for PivotTable reporting
Connect PivotTables to various data sources
Create and manage PivotTable calculations
Format PivotTables for presentations
Update PivotTable source data
Create and manage Excel Data Models to pull in data from multiple sources
Create and manage data relationships
Create Dashboard elements in a PivotTable

Course content

10 sections • 59 lectures • 4h 32m total length

Microsoft Excel PivotTables - Beginner to Advanced Course Introduction5:45
Excel PivotTable Course Exercise Files (DOWNLOAD)2:33
Download the exercise files zip from the course resources, unzip to access all Excel files, and follow along as I describe tools and features of Excel pivot tables.
Excel PivotTable Course QA (ASK QUESTIONS)2:56
CHALLENGE 01: Post to the Course QA

Proper Microsoft Excel List Design and Layout0:49
Microsoft Excel - Do's and Don'ts of Proper Excel List Design6:55
Cleaning Up Excel List Data4:11
Proper Excel List Headers3:36
Removing Empty Excel List Rows6:12
Microsoft Excel PROPER() Function4:57
Microsoft Excel TRIM() Function3:29
Microsoft Excel VALUE() Function4:07
Microsoft Excel UPPER() Function2:36
CHALLENGE 04: Cleaning Up Excel Data
Apply Excel Data Validation to List to Ensure Data Integrity5:07
Highlight Invalid List data3:29
Identify and correct invalid list data in Excel by circling values that violate data validation, using a dropdown of valid shipping methods. Clean misspellings to ensure proper pivot table groupings.
Removing Duplicate Records from Excel List5:58
Identify and remove duplicate records in Excel before creating pivot tables by using conditional formatting to spot duplicates and the remove duplicates tool on the order ID column.
Format Excel List as Table2:25
CHALLENGE 05: Applying Validation to PivotTable Source Data

Sorting Data in an Excel PivotTable5:39
Using Custom Lists to Sort in an Excel PivotTable6:29
Sorting PivotTable Column Headers2:45
Filtering Data in an Excel PivotTable7:12
Working with PivotTable Slicers to Filter Data5:28
Explore how to replace standard pivot filters with slicers in Excel to create interactive, dashboard-like filtering of pivot tables, including multi-select and clear-filter options.
Removing a PivotTable Slicer1:24
Working with Excel PivotTable Timeline Filters3:46
Use a timeline filter to filter pivot tables by dates, via insert timeline in the pivot table analyze tab. Show years or quarters and adjust date range for dashboard insights.
Sorting and Filtering Excel PivotTable Data

Inserting an Excel Pivot Chart3:09
Create a pivot chart by linking it to your pivot table, then insert a pie chart and customize labels, legend, and formatting like a regular chart.
Combining Charts with PivotTable Slicers2:53
PivotTable and Pivot Chart Connection3:39
See how a slicer connects pivot table and pivot chart, so filtering updates both in real time, and switch to a clustered column chart to show categories and shipping methods.
Formatting a Pivot Chart with Chart Styles2:51
Adding and Removing Chart Elements5:33

Why Excel Power Query?4:00
Explore how Excel Power Query connects to internal and external data, transforms and combines it, loads it for pivot tables and charts, and refreshes from the original source.
Introduction to External Data Sources2:56
Creating a New Query4:04
Transform Power Query Data2:58
Connect to Excel data in the Power Query editor, transform it without disturbing the source, filter by country like South America, and load to a worksheet for pivot table report.
Loading a Query to an Excel Worksheet3:13
Load the Power Query results into an Excel worksheet by choosing close and load two, load as a table in a new worksheet, and maintain a live connection for updates.
Updating the Original Source Data3:07
Creating a PivotTable based on the Query4:22
Introduction to Excel Table Relationships5:38
Append Multiple Queries through Power Query7:32
Merge Multiple Queries through Power Query9:17
Create a PivotTable based on Multiple Datasets3:14
Learn to create a pivot table from multiple datasets by appending and merging tables with Power Query, establishing relationships, and summarizing across data in Excel.

Requirements

Familiar with working in Microsoft Excel

Description

WHY EXCEL PIVOT TABLES

Mastering Microsoft Excel PivotTables will change the way you approach reporting. Through a few clicks of the mouse Excel PivotTables allow you to quickly and efficiently summarize large data sets.

COURSE DESIGNED TO HELP YOU SUCCEED

This Microsoft Excel PivotTables - Beginner to Advanced course will take your skills from absolute PivotTable beginner to Advanced PivotTable user. Each section of the course has been designed to focus on a specific skill set. Once that skill set is mastered, then you will move to the next section that builds upon the previous skill set. After completing the first few sections of the course you will feel like you can conquer the world with the reports you can create, but there's more.

Each section contains instructor lead lectures with step-by-step instruction and encouragement to try the various topics for yourself, using the course provided exercise files. Each section also contains mini challenges where you can practice the skill you are mastering with additional exercises and quizzes on the topics.

WHAT YOU'LL LEARN

Create dynamic reports that will help with making intelligent business decisions
Capture data from a single source or from multiple related sources
Apply proper list design techniques to make reporting a breeze
Summarize data with built in Excel functions and custom calculations
Format your report for clear presentation

JOIN ME

Enroll now and learn to harness the power of Microsoft Excel PivotTables.

Who this course is for:

This course is intended for users familiar with working in Microsoft Excel but wish to master working with Excels PivotTable feature.
Microsoft Excel users who want to become more efficient in summarizing and reporting on large datasets within Excel

Microsoft Excel Pivot Tables - Beginner to Advanced

What you'll learn

Explore related topics

Course content