We may earn an affiliate commission when you visit our partners.
Course image
Alex Mozes

Master Excel Pivot Tables and save the world.

Read more

Master Excel Pivot Tables and save the world.

This course discusses one of the most powerful tools of the Office Ninja's arsenal, Excel Pivot Tables and how to use pivot tables. It's potential for analyzing and summarizing data is legnendary, but few have mastered its intricacies. Through a series of easy to follow lessons on how to use pivot table in excel, you will be introduced to the concepts, techniques and powerful features of this mysterious excel tool, and can follow along with each step with the included sample file.

We will cover:

● Pivot Table Concepts

● Creating A Pivot Table

● Modifying Pivot Table Elements

● Filtering Extraneous Data (the Slicer tool)

● Creating Multiple Pivot Tables

● Creating Pivot Charts

● Advanced Analysis Techniques

Learn how to use pivot table in excel today.

Enroll now

What's inside

Learning objectives

  • Master excel pivot tables
  • Learn to harness these skills to get a raise
  • Solve world hunger

Syllabus

section 1
Welcome!
The basics
EXERCISE FILES
Read more
White Belt Training

This First lecture sets the stage for understanding Pivot Tables by first explaining how to create automatic Subtotals.  In this lecture you will learn how to:

  • Analyze large data sets by the different types of field data
  • Quickly create subtotals
  • Collapse lists for a given type of groupable data
Exercise: Subtotals
Answer: Subtotals

This Lecture explains the fundamental concepts behind what a Pivot Table is and why it is useful. At the end of this lecture you will understand what a pivot table is doing and how it acts as a "Super Subtotal

In this lesson you will create your first pivot table. Learn how to:

  • Insert a pivot table to your workbook
  • Work with the Field List
  • Modify your Pivot Table
  • Remove fields
Exercise: Create a Pivot Table
Answer: Create a Pivot Table

Take this quiz and see if you've been paying attention :)

Now that you have created a Pivot Table, we want to learn how to control how values are displayed in that table. In this lesson, you will learn how to:

  • Change the Pivot Table function from the default Sum to other functions
  • Change the Way values are displayed to % of ...
Exercise: Changing the Values Field Settings
Answer: Changing Value Field Settings

In this lesson, we uncover a nifty feature of Pivot Tables, the Drill To feature which allows you to easily display the original records that made up a specific value in the pivot table.

Answer: Drill-To Feature

This lesson explores how to format your Pivot Table so it looks the way you want, and so you can share it with other people in a professional format. You will learn how to:

  • Apply a Style to your Pivot Table
  • Change the Number Format of your values
Exercise: Formatting and Pivot Table Design
Answer: Formatting and Pivot Table Design
Black Belt Training

In this lesson, we raise the stakes and begin to explore the true power of Pivot Tables by adding more than one field to the row and column areas. This will allow you to get even more specific on the type of trends you are able to extract from your data. You will learn how to:

  • Add multiple fields to the Row and Column labels
  • Re-organize the layout of the fields
  • Manage multiple fields in the Pivot Table
Exercise: Adding and Manipulating Multiple Fields
Answer: Adding and Manipulating Multiple Fields

When dealing with large data sets, it's often important to be able to filter ou the extraneous data that is cluttering the answers you're looking for.  In this lesson, you learn how to do that from within the Pivot Table, and with a new filtering tool to Office 2010, the Slicer.

  • Filter the row and column labels to hide certain values
  • Create a slicer to filter the entire table without showing values
  • Use the slicer tool to quickly manipulate your data
Exercise: Filtering and Slicing Pivot Data
Answer: Filtering & Slicing Pivot Data

This lesson explores how to create a dashboard, or rollup of lots of different aspects of your dataset onto one sheet with multiple Pivot Tables.  This is especially useful when the data is changing regularly or you want to look at different trends in your data at the same time.

  • Add multiple Pivot Tables to the same sheet
  • Manage multiple Pivot Tables
  • Connect one Slicer to multiple Pivot Tables
Exercise: Creating Pivot Dashboards
Answer: Creating Pivot Dashboards

By graphically representing data, you can more easily identify trends or key performance indicators in your data, and make it easier to explain your findings to others.  In this lesson, we see how to create a Pivot Chart that reflects the Pivot Table, and how to manipulate that chart

  • Create a Pivot Chart
  • Format a Pivot Chart
  • Add additional elements to a Pivot Chart
Exercise: Pivot Charts
Answer: Pivot Charts

In this final lesson, we explore more real world examples of how Pivot Tables can answer important business questions using the tools we've discussed.

Answer: Putting It All Together

Good to know

Know what's good
, what to watch for
, and possible dealbreakers
Helps learners harness analytical skills to get a raise or solve world hunger, indicating a connection between developing skills and real-world benefits
Explores Excel Pivot Tables, a powerful tool for data analysis and summarization, indicating relevance to spreadsheet users and analysts
Taught by Alex Mozes, indicating the involvement of an industry professional
Provides step-by-step lessons and an included sample file for hands-on practice, indicating a practical approach and support for learners
Covers various techniques and features of Pivot Tables, indicating comprehensive instruction

Save this course

Save Excel Deep Dive: Pivot Tables Workshop to your list so you can find it easily later:
Save

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in Excel Deep Dive: Pivot Tables Workshop with these activities:
Review Prerequisite Resource
Review key concepts in Pivot Tables and related topics to strengthen your foundation and prepare for the course.
Browse courses on Pivot Tables
Show steps
  • Revisit the basics of Pivot Tables and their uses.
  • Review common Excel functions used in Pivot Tables, such as SUM, COUNT, and AVERAGE.
  • Recall techniques for organizing and filtering data in Pivot Tables.
Brush Up on Excel Basics
Ensure proficiency in fundamental Excel skills to support your learning in Pivot Tables.
Browse courses on Excel Functions
Show steps
  • Revisit basic Excel operations, such as creating and formatting spreadsheets.
  • Review common Excel functions and formulas.
  • Practice navigating and manipulating data within Excel.
Participate in Peer Discussions
Engage with fellow learners by sharing tips, insights, and questions related to Pivot Tables, fostering a collaborative learning environment.
Show steps
  • Join an online forum or study group focused on Pivot Tables.
  • Ask questions, share your experiences, and provide support to other learners.
  • Attend virtual or in-person meetups to connect with other Pivot Table enthusiasts.
Four other activities
Expand to see all activities and additional details
Show all seven activities
Practice Creating Pivot Tables
Gain hands-on experience by creating Pivot Tables from scratch using sample datasets.
Show steps
  • Set up a sample dataset in Excel.
  • Create a Pivot Table based on the dataset, choosing appropriate fields for rows, columns, and values.
  • Practice modifying Pivot Table elements, such as adding filters or changing value fields.
Explore Advanced Pivot Table Techniques
Delve deeper into Pivot Tables by following guided tutorials on advanced techniques, such as creating multiple Pivot Tables and using slicers.
Show steps
  • Identify advanced Pivot Table features covered in the course.
  • Seek out tutorials on these advanced techniques.
  • Work through the tutorials, implementing the techniques in your own practice Pivot Tables.
Contribute to Open Source Pivot Table Projects
Gain practical experience and contribute to the Pivot Table community by participating in open-source projects related to Pivot Tables.
Show steps
  • Identify open-source libraries or projects related to Pivot Tables.
  • Review the project documentation and codebase.
  • Contribute bug fixes, feature requests, or documentation improvements.
Create a Pivot Table Dashboard
Demonstrate your mastery by creating a comprehensive Pivot Table dashboard that summarizes and visualizes key insights from a dataset.
Show steps
  • Prepare a dataset with multiple data dimensions.
  • Create multiple Pivot Tables based on the dataset.
  • Combine the Pivot Tables into a single dashboard, using slicers to filter and interact with the data.

Career center

Learners who complete Excel Deep Dive: Pivot Tables Workshop will develop knowledge and skills that may be useful to these careers:

Reading list

We haven't picked any books for this reading list yet.

Share

Help others find this course page by sharing it with your friends and followers:

Similar courses

Our mission

OpenCourser helps millions of learners each year. People visit us to learn workspace skills, ace their exams, and nurture their curiosity.

Our extensive catalog contains over 50,000 courses and twice as many books. Browse by search, by topic, or even by career interests. We'll match you to the right resources quickly.

Find this site helpful? Tell a friend about us.

Affiliate disclosure

We're supported by our community of learners. When you purchase or subscribe to courses and programs or purchase books, we may earn a commission from our partners.

Your purchases help us maintain our catalog and keep our servers humming without ads.

Thank you for supporting OpenCourser.

© 2016 - 2024 OpenCourser