OpenCourser brand icon
Sign in
We may earn an affiliate commission when you visit our partners.

Master Excel Pivot Tables - Excel 365 and Excel 2019

Tom Meservy

Do you want to help your company or organization make better decisions? Do you want to advance your career because you bring real value to the organization? Do you want to save hundreds of hours a year in creating reports and charts? Are you ready to make an impact because you understand the patterns and trends in your company’s data? Do you want to master Excel Pivot Tables? This course is for you.

Master Excel Pivot Tables in this Comprehensive Course

Read more

Do you want to help your company or organization make better decisions? Do you want to advance your career because you bring real value to the organization? Do you want to save hundreds of hours a year in creating reports and charts? Are you ready to make an impact because you understand the patterns and trends in your company’s data? Do you want to master Excel Pivot Tables? This course is for you.

Master Excel Pivot Tables in this Comprehensive Course

This comprehensive course contains concise illustrations yet detailed topic coverage that will benefit both Beginners and Advanced Users of Excel. In just an hour and a half, you will learn to effectively use Excel Pivot Tables including how to:

  • Create and Format Pivot Tables
  • Select, Move, and Clear Pivot Tables
  • Design Pivot Tables
  • Value Field Settings – Summarize Values By
  • Value Field Settings – Show Values As
  • Filter Data
  • Manually and Automatically Group Data
  • Create Calculated Fields and Items
  • Explore Other Useful Pivot Table Concepts

Learn Pivot Tables by Doing

Learn one of the most powerful features of Excel that will allow you to dig deep into your company’s data and answer complex questions with only a few mouse clicks. Learn to analyze vast amounts of data, create useful reports, and impress top management or prospective employers.

Most that say they know Excel Pivot Tables really only have a grasp on the basics. Within the first 10 minutes of this course, you will likely surpass their knowledge and by the end of the course you will master even some of the most complex aspects of Pivot Tables.

Throughout the course, you can follow along with the numerous concise examples that illustrate the power and features of Pivot Tables using the included worksheets. Further, “homework” examples are provided to allow you to apply these same concepts to another data set so that you can know that you have mastered the concepts.

  • Create pivot tables from data in an existing worksheet and answer basic questions from the data.
  • Summarize pivot table values by sum, count, average, max, min, and other types of calculations.
  • Select different parts of the pivot table, move it within and between worksheets, and clear or reset the contents of the pivot table.
  • Adjust the design aspects of the pivot table including totals, layout, and visual aspects of the pivot table.
  • Adjust pivot table calculations to show the results as a percentage of a total, difference from another field, or other value calculation.
  • Filter data out of the pivot table using various techniques including field filters, table filters, slicers, and timelines.
  • Automatically and manually group results within your pivot table using a variety of approaches.
  • Create and utilize new pivot table fields that are calculated using existing data in the pivot table and new items that consist of formulas that refer to other rows.
  • Appropriately reference pivot table data.
  • Create pivot charts and understand the relationship between pivot charts and pivot tables.
  • And More.

We are professional instructors who have helped thousands of students Master Excel Pivot Tables and become even more effective at work. We invite you to join us on a short journey to advance your career.

Enroll now

What's inside

Learning objectives

  • Create complex pivot tables to answer valuable questions and provide useful insights
  • Understand how to effectively use excel pivot tables

Syllabus

Know what the course is about, who is teaching it, and know which files to use during the course.
Introduction

This lecture is an introduction to the course files that we will use. These course files are attached to this lecture. We encourage you to download these files and follow along with the examples.

Read more
Create a pivot table from data in an existing worksheet and answer basic questions from the data.

A gentle introduction to the basics of pivot tables including how to create them and how to select content for the rows, columns, and the "values" area.

Learn how to format summarized data in pivot tables.

Learn to sum data in pivot tables.

Adjust pivot tables by changing row values.

Summarize pivot table values by sum, count, average, max, min, and other types of calculations.

Learn how to summarize values by "count" in pivot tables.

Understand how to nest multiple fields in pivot tables.

Further investigate nested fields. Discover the drill down feature.

Learn how to summarize values by "average" in pivot tables.

Learn how to summarize values by "min" and "max" in pivot tables.

Discover other ways to summarize pivot table data.

Select different parts of the pivot table, move it within and between worksheets, and clear or reset the contents of the pivot table.

Learn the basics of selecting, moving, and clearing pivot table data.

Adjust the design aspects of the pivot table including totals, layout, and visual aspects of the pivot table.

Change the design or your pivot table.

Discover how to add subtotals and grand totals to your pivot tables.

Explore how to layout your pivot table in different ways.

Adjust pivot table calculations to show the results as a percentage of a total, difference from another field, or other value calculation.

This lecture provides an overview of the Show Values As option.

Learn how to show values as a "percent of another number" in pivot tables.

Learn how to show values as a "percent of" another field in pivot tables.

Learn how to show values as a "percent of a parent total" in pivot tables.

Learn how to show values as a "difference from" another field in pivot tables. Also discover how to rank order pivot table data.

Learn to format pivot table data as running totals.

Enjoy learning about the Index function and discover something that hardly anyone knows!

Practice problem 1 for understanding how to summarize data as a percentage of another number.

Practice problem 2 for understanding how to summarize data as a percentage of another number.

Practice problem 3 for understanding how to summarize data as a percentage of another number.

Practice problem 4 for understanding how to summarize data as a percentage of another number.

Practice problem for understanding how to summarize data as a difference from another number.

A practice problem to reinforce all of the concepts that you have used up to this point in time.

A second practice problem to reinforce all of the concepts that you have used up to this point in time.

Filter data out of the pivot table using various techniques including field filters, table filters, slicers, and timelines.

Learn the basics of filtering data in pivot tables.

Learn how to use multiple filters at the same time.

Discover the simplicity of slicers.

Explore how you can format and adjust slicers.

Discover how to easily filter using the timeline.

Explore how you can format and adjust timelines.

Learn a really neat trick for creating multiple tailored reports for each item in your filter.

Automatically and manually group results within your pivot table using a variety of approaches.

Learn how to manually group items into custom groups that can each have their own subtotals.

Discover automatic grouping of data within pivot tables.

Learn how to group data into specific ranges.

Create and utilize new pivot table fields that are calculated using existing data in the pivot table and new items that consist of formulas that refer to other rows.

Learn how to create calculated fields - a new field created from other fields in the pivot table.

Learn about how to create calculated items that use the contents of other items within a pivot table.

Appropriately reference pivot table data. Create pivot charts and understand the relationship between pivot charts and pivot tables. Understand how to refresh a pivot table.

Learn to use pivot table data and results in other parts of your spreadsheet by referencing pivot table cells appropriately.

Discover the ease of using sparklines with pivot tables.

Easily implement pivot charts in your spreadsheets and understand how they are linked to pivot tables.

Pivot tables cache data when they are created. If underlying data is updated you need to refresh it. Quickly learn how to refresh data.

Verify that you have mastered Pivot Tables.

This lecture provides an overview of homework practice problems so that you know that you have mastered the content of the course. Use the attached workbook to do the problems and then check your answers using the quizzes in this section.

The problem and data for this quiz is contained in the Homework Practice Problem Spreadsheet and the Basic Concepts tab.

The problem and data for this quiz is contained in the Homework Practice Problem Spreadsheet and the Grouping tab.

The problem and data for this quiz is contained in the Homework Practice Problem Spreadsheet and the Referencing Cells tab.

The problem and data for this quiz is contained in the Homework Practice Problem Spreadsheet and the Calculated Fields tab.

Good to know

Know what's good
, what to watch for
, and possible dealbreakers
Teaches how to create calculated fields and items, allowing users to perform complex calculations within pivot tables, which is useful for advanced data analysis
Includes practice problems and quizzes, which allows learners to reinforce their understanding and master the concepts taught in each section of the course
Covers filtering data using slicers and timelines, which are relatively recent features in Excel, and this may not be applicable to users of older versions
Explores pivot table design and layout options, which allows users to create visually appealing and informative reports, enhancing their presentation skills
Explains how to summarize values by count, average, min, and max, which are fundamental statistical calculations that are useful in data analysis
Focuses on Excel 365 and Excel 2019, so users with older versions may find some features or interface elements are different or unavailable

Save this course

Save Master Excel Pivot Tables - Excel 365 and Excel 2019 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 Master Excel Pivot Tables - Excel 365 and Excel 2019 with these activities:
Review Basic Excel Functions
Strengthen your foundational Excel skills to better understand and utilize pivot tables effectively.
Browse courses on SUM
Show steps
  • Identify key Excel functions used in data analysis.
  • Practice using these functions with sample datasets.
  • Review how these functions can be applied to data summarization.
Pivot Table Practice Exercises
Reinforce your pivot table skills through targeted exercises focusing on data summarization, filtering, and grouping.
Show steps
  • Download sample datasets with varying complexities.
  • Create pivot tables to answer specific questions about the data.
  • Experiment with different filtering and grouping techniques.
  • Compare your results with provided solutions.
Create a Pivot Table Tutorial
Solidify your understanding by creating a tutorial explaining a specific pivot table concept or technique.
Show steps
  • Choose a pivot table topic to explain.
  • Create a sample dataset to illustrate the concept.
  • Record a video or write a blog post explaining the steps.
  • Share your tutorial with others and gather feedback.
Three other activities
Expand to see all activities and additional details
Show all six activities
Analyze Sales Data with Pivot Tables
Apply your pivot table knowledge to a real-world scenario by analyzing sales data to identify trends and insights.
Show steps
  • Obtain a sample sales dataset or use your own.
  • Create pivot tables to analyze sales by region, product, and time period.
  • Identify key trends and insights from the data.
  • Present your findings in a concise report.
Read 'Excel 2019 Bible'
Expand your knowledge of Excel's capabilities and how they relate to pivot tables by reading a comprehensive Excel guide.
View Putting Knowledge to Work on Amazon
Show steps
  • Obtain a copy of 'Excel 2019 Bible'.
  • Read the sections related to pivot tables and data analysis.
  • Experiment with the techniques described in the book.
Automated Reporting Dashboard
Create an automated reporting dashboard using pivot tables to streamline data analysis and reporting processes.
Show steps
  • Identify a reporting need within your organization or a sample scenario.
  • Design a dashboard layout with key performance indicators (KPIs).
  • Create pivot tables to calculate the KPIs.
  • Connect the pivot tables to the dashboard elements.
  • Automate the data refresh process.

Career center

Learners who complete Master Excel Pivot Tables - Excel 365 and Excel 2019 will develop knowledge and skills that may be useful to these careers:
Data Analyst
Data Analysts are responsible for collecting, cleaning, and analyzing data to provide insights that drive business strategies. This position requires expertise in data manipulation tools, including Excel, and proficiency with creating reports and visualizations. This course helps data analysts build a foundation in working with pivot tables, which are invaluable for summarizing data, identifying trends, and creating data-driven presentations. The focus on filtering data and grouping results within this course directly supports the work of a Data Analyst, helping them transform raw data into actionable information. This course is an important first step in learning to perform these kinds of tasks.
See salaries and explore the career path for Data Analyst
Financial Analyst
Financial Analysts use data to evaluate a company's financial performance, create forecasts, and provide recommendations to management. This work often involves extensive use of spreadsheets and data analysis tools. This course is particularly relevant because proficiency in Excel Pivot Tables is essential for manipulating large financial datasets and creating reports. The course's lessons on creating and formatting pivot tables, along with the ability to group data, calculate fields, and show values as percentages or differences, allows financial analysts to quickly answer key questions and find hidden patterns. A Financial Analyst should take this course to learn to leverage the full potential of pivot tables.
See salaries and explore the career path for Financial Analyst
Sales Analyst
Sales Analysts are responsible for analyzing sales data to identify trends, track performance, and forecast future sales. This role involves working with large datasets, often in spreadsheets, and creating reports for sales management. This course, which offers a deep dive into Excel Pivot Tables, is particularly helpful for building practical skills in data summarization, filtering, and calculation. The course’s hands-on approach to creating reports and charts from sales data means this course is very relevant for aspiring Sales Analysts. A Sales Analyst will use this to analyze performance and strategize for the future.
See salaries and explore the career path for Sales Analyst
Business Analyst
A Business Analyst uses data to identify trends and patterns that inform business decisions. This role involves analyzing large datasets, often using tools like Excel, to create reports and visualizations. This course, which focuses on mastering Excel Pivot Tables, helps build the core skills necessary for a business analyst to extract meaningful insights from raw data, summarize key findings, and present them effectively. The ability to create calculated fields and items, filter data, and group results are all critical for a successful Business Analyst. This course provides targeted practice in these essential skills.
See salaries and explore the career path for Business Analyst
Marketing Analyst
Marketing Analysts analyze marketing data to evaluate campaign performance, measure ROI, and inform marketing strategies. This work requires extracting insights from large datasets and creating reports for marketing teams. This course, which focuses on mastering Excel Pivot Tables, helps develop the practical skills that a Marketing Analyst needs to summarize campaign data, identify trends and patterns, and perform calculations. The lessons on calculating fields, filtering data, and grouping results will be directly relevant in a Marketing Analyst’s daily tasks. A Marketing Analyst will find this course very valuable.
See salaries and explore the career path for Marketing Analyst
Market Research Analyst
Market Research Analysts explore consumer behavior and market trends to inform business decisions. This work involves collecting and analyzing large datasets, and presenting findings to management. This course on Excel Pivot Tables is quite valuable for Market Research Analysts because it helps build the required skills to sift through market data, summarize survey results, and create reports. The course’s emphasis on grouping and filtering data, as well as creating calculated fields, is directly useful in identifying key market patterns and trends. A Market Research Analyst will use these techniques to understand consumer behavior and produce data-driven insights.
See salaries and explore the career path for Market Research Analyst
Operations Analyst
Operations Analysts focus on improving efficiency and productivity within an organization. They analyze operational data, identify bottlenecks, and make recommendations for improvements. This course concentrating on Excel Pivot Tables helps an Operations Analyst learn how to effectively summarize operational data, identify performance trends, and create reports to support their findings. The ability to group data, filter results, and create calculated fields are all vital to this role. An Operations Analyst can use this course to master using pivot tables to make decisions.
See salaries and explore the career path for Operations Analyst
Supply Chain Analyst
Supply Chain Analysts are responsible for optimizing the flow of goods and materials within a company. They analyze data related to inventory, transportation, and logistics to identify inefficiencies and areas for improvement. This course allows a Supply Chain Analyst to build the skills to use Excel Pivot Tables to summarize supply chain data, identify bottlenecks, and create reports. These are valuable in improving the overall efficiency of the supply chain. An understanding of how to filter data and group results within a pivot table is a specific, directly relevant skill that a Supply Chain Analyst will use.
See salaries and explore the career path for Supply Chain Analyst
Human Resources Analyst
Human Resources Analysts collect and analyze employee data to support HR initiatives, identify trends, and make informed decisions about staffing, compensation, and benefits. Utilizing Excel Pivot Tables, as taught in this course, allows an HR Analyst to summarize employee data, find trends, and create reports for managers. The ability to filter data, group results, and calculate new fields can help an HR analyst discover important insights that can lead to increased organizational effectiveness. For an HR Analyst, this course is very relevant.
See salaries and explore the career path for Human Resources Analyst
Management Consultant
Management Consultants advise organizations on improving their performance and efficiency; they often need to analyze extensive data sets to evaluate current operations and make recommendations for change. The ability to use Excel Pivot Tables, as taught in this course, is essential for a Management Consultant to organize complex data, identify patterns, and create reports to support the recommendations they make. The course will help improve a consultant's ability to summarize values, filter data, and group results. Management Consultants who take this course will make more effective data-driven choices.
See salaries and explore the career path for Management Consultant
Project Manager
Project Managers are responsible for planning, executing, and closing projects, often needing to track budgets, timelines, and resource allocation. Knowing how to analyze project data using Excel Pivot Tables is critical for monitoring project performance. The ability to summarize values, filter data, and group results, as taught in this course, allows Project Managers to quickly create reports and identify potential issues. This course will allow Project Managers to make more effective data driven choices. A Project Manager will find the skills taught in this course highly beneficial.
See salaries and explore the career path for Project Manager
Research Assistant
A Research Assistant works under the direction of a researcher, and may perform tasks like collecting, organizing, and analyzing data. This position often involves using tools like Excel to manage and summarize research results. This course, which focuses on mastering Excel Pivot Tables, may be useful for a Research Assistant by teaching them to summarize large datasets, identify patterns, and create reports. The ability to group data, filter results, and calculate fields, covered in this course, are all skills a Research Assistant would use. A Research Assistant might find this course helpful for improving their research.
See salaries and explore the career path for Research Assistant
Accountant
Accountants are responsible for preparing and analyzing financial records, ensuring accuracy and compliance with regulations. This position requires strong analytical skills and a deep understanding of financial data. This course on Excel Pivot Tables may be helpful for an Accountant, as it teaches techniques for summarizing financial data, creating reports, and identifying patterns. An Accountant could use the skills they learn in this course to make more effective use of spreadsheets and be more efficient at their work. The ability to filter data, group results, and do calculations are all relevant skills.
See salaries and explore the career path for Accountant
Statistician
Statisticians apply statistical methods to collect, analyze, and interpret data in various fields. While statisticians often use specialized statistical software, the ability to manipulate and summarize data using tools like Excel is also valuable. This course may be useful for a statistician, as it focuses on building skills in data summarization and reporting using Excel Pivot Tables. These skills will allow them to organize data and generate basic reports. Though Statisticians use other tools, this course may still be a useful component of their skill set.
See salaries and explore the career path for Statistician
Data Scientist
Data Scientists use advanced statistical and machine learning techniques to extract insights from data, develop models, and make predictions. While this role typically involves advanced programming and statistical software, the ability to quickly explore and summarize data using tools like Excel can be helpful for initial analysis. This course may be useful for a Data Scientist by teaching them how to use Excel Pivot Tables to summarize and explore large datasets, identify trends, and create reports. The course's focus on data summarization makes it a possible useful component of a data scientist's tool belt.
See salaries and explore the career path for Data Scientist

Reading list

We've selected one books that we think will supplement your learning. Use these to develop background knowledge, enrich your coursework, and gain a deeper understanding of the topics covered in Master Excel Pivot Tables - Excel 365 and Excel 2019.
Cover image
Putting Knowledge to Work
Save
The 'Excel 2019 Bible' provides a comprehensive guide to all aspects of Excel, including pivot tables. It useful reference for understanding the underlying features and functionalities that support pivot table creation and manipulation. is particularly helpful for those who want to delve deeper into Excel's capabilities beyond the scope of this course. It is commonly used as a reference by industry professionals.
Putting Knowledge to Work
Paperback
Check
Putting Knowledge to Work
Kindle Edition
Check

Share

Help others find this course page by sharing it with your friends and followers:
Facebook
LinkedIn
Reddit
X
Link
Email

Similar courses

Similar courses are unavailable at this time. Please try again later.
Effort
1.5 total hours
Via
Udemy
Instructor
Tom Meservy
Language
English
Good to know
Teaches how to create calculated fields and items, allowing users to perform complex calculations within pivot tables, which is useful for advanced data analysis
Includes practice problems and quizzes, which allows learners to reinforce their understanding and master the concepts taught in each section of the course
Covers filtering data using slicers and timelines, which are relatively recent features in Excel, and this may not be applicable to users of older versions
Explores pivot table design and layout options, which allows users to create visually appealing and informative reports, enhancing their presentation skills
Explains how to summarize values by count, average, min, and max, which are fundamental statistical calculations that are useful in data analysis
Focuses on Excel 365 and Excel 2019, so users with older versions may find some features or interface elements are different or unavailable
Share and help others discover this course.
Facebook LinkedIn X Reddit Link Email
Don't miss out
Enroll today to gain access to this course
Enroll in this course
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 - 2025 OpenCourser