Excel Pivot Tables

Who should attend

This class is for users familiar with standard Excel features but who need more efficient and powerful ways of manipulating their data. Others may have inherited pivot tables, and want to understand and modify them.

Team Pass by Learnit

Excel Pivot Tables

From $0
Two, 2-hour Modules (9am - 11:30am PT)

Pivot tables are arguably Excel's most powerful feature, and most likely to impress your boss. At first glance, pivot tables seem quite mysterious: with a quick drag-and-drop, your data somehow reorganizes itself into interesting groupings and useful totals. There are more than a few organizations whose critical business decisions rely on carefully designed pivot tables. Unfortunately, because they accomplish so much behind the scenes" it's hard to figure out how pivot tables actually work — and scary to try to modify existing ones. That's where our trainers come in: they love nothing more than demystifying tricky features like pivot tables. After this class, you'll be the one creating pivot tables to capture insights about your company's data. Here's what you'll learn:

  • Cleanup. The data in your spreadsheet needs to meet certain criteria before it can be turned into a pivot table. We'll show you how to clean the data, designate the cells you want to become a pivot table, place it exactly where you want, and format it nicely in color.
  • Manipulation. The advantage of a pivot table is being able to entirely rearrange your view of the data with nothing more than a few mouse clicks. For example, which is more useful: orders by city, revenue by customer, or orders by salesperson? Being able to easily switch these parameters — within the same worksheet — is what makes pivot tables so useful.
  • Aggregate Functions. Seeing different views of the data is only useful if you can summarize (aggregate) information about that data: what were the totals, averages, medians and counts for each different view of the data? We'll show you how to apply these functions and filter the results to find the true insights.
  • Pivot Charts. As with normal tables of data, it's helpful to create charts that illustrate the pivoted views of the data. We'll show you how to present the data from pivot tables in colorful visualizations (unless you prefer black and white).
  • Big Data and "Power" Features. Excel has a limit of only one million rows, and if you've ever tried using them all, your computer probably slowed to a crawl. Enter the family of "Power" features: Power Query, Power Pivot, Power View, Power Map, and Power BI. These features allow you to import hundreds of millions of rows of data — even from multiple databases simultaneously — and use them in a spreadsheet. We'll show you how to get this so-called "big data" using Power Query and then apply calculations using Power Pivot. Ultimately, you'll be able to turn them into pivot tables, pivot charts and other data visualizations.
Course ID
EXCELPbuip
Available times
June 17 & June 18, 2021
9:00am-11:30am
June 17 & June 18, 2021
9:00am-11:30am
June 17 & June 18, 2021
9:00am-11:30am
June 17 & June 18, 2021
9:00am-11:30am
June 17, 2021
9:00am-11:30am
June 17, 2021
9:00am-11:30am
June 17, 2021
9:00am-11:30am
June 17, 2021
9:00am-11:30am
June 17, 2021
9:00am-11:30am

Course Outline

Module 1: Creating and Managing Pivot Tables

Learning Outcomes:

1. Organize and clean data in spreadsheets for pivot table analysis

2. Manage and update pivot table source data to display new values

3. Edit pivot table report formats and settings

Topics:

  • Organizing Data
  • Inserting Pivot Tables
  • Data Categories
  • List Design Guidelines
  • Data Normalizing Strategies
  • Common Data Mining Techniques
  • Standardizing Data
  • Adding Related Data with VLOOKUP Function
  • Managing Pivot Tables
  • Naming Pivot Tables
  • Refreshing and Changing Pivot Table Data
  • Layering Fields of Data
  • Filtering and Slicing Pivot Table Data
  • Functions and Number Formats
  • Show Details of Specific Pivot Table Results
  • Creating Pivot Charts
  • Pivoting Data Fields
  • Basic Chart Formatting
  • Filtering and Slicing Pivot Chart Data

Module 2: Advanced Pivot Table Strategies Custom Pivot Table Design

Learning Outcomes:

1. Use Power Pivot to connect data sources

2. Format default layouts for pivot table reports

3. Create a data model and pull data in from various tables for pivot table analysis

Topics:

  • Analysis Tables vs. Data Lists
  • Choosing Report Layouts
  • Using and Creating Table Styles
  • Subtotals and Grand Totals
  • Pivot Table Options
  • Calculated Fields and Items
  • Power Pivot
  • Data Modeling Basics
  • Adding Data to Model
  • Creating Relationships
  • Designing Measures
  • Adding Custom Columns
  • Using Basic DAX Expressions
  • Power Pivot Tables
  • Creating Power Pivot Tables
  • Preview of Power Query & Power BI

Skills covered

No items found.

Excel Pivot Tables

Reviews

"It was very easy to follow along and I learned a few new tricks."

Misty M.
Misty M.

"Loved learning about creating tables, how to add data/see it update and using the duplicate finder tool. Our instructor did a great job, in my previous role I was an educator and taught tech, so I know good teaching and engagement, great work! A+"

Eliya H.
Eliya H.

"It was a great course and I really enjoyed Josh’s ability to be engaging and keep your attention. Overall, I rate Josh 10/10 and would recommend courses from him to colleagues, friends working in other companies and even to some fellow grad students!"

Lauren R.
Lauren R.

"Mo was amazing. Did not feel rushed. Very informative and interactive. I want him to teach all the classes offered through my organization."

Jennifer R.
Jennifer R.

"Josh was great. Very patient, great pace, and passionate about helping us learn."

Linda G.
Linda G.