Excel - Sort, filter, summarize and calculate your PivoteTable data Tutorial

In this video, you will learn how to sort, filter, summarize, and calculate your PivotTable data.
The video covers various ways to analyze PivotTable data, including sorting it to quickly identify trends, filtering it to focus on specific criteria, and using slicers to efficiently filter the data.
Additionally, the video demonstrates how to summarize the data using different functions and display it in various formats, such as percentages.
This knowledge will help you effectively analyze and understand your PivotTable data, enabling you to make informed decisions.

3:49
4425 views

Available in Microsoft365

Objectifs :

This video aims to teach viewers how to analyze PivotTable data effectively by utilizing sorting, filtering, and summarizing techniques. It emphasizes the importance of these methods in identifying trends and insights within data, particularly in the arts and photography genre.

Chapitres :

Introduction to PivotTable Analysis
Analyzing PivotTable data can be done in various ways, with sorting being one of the most common methods. This technique allows users to quickly identify trends in their data, especially within specific genres such as arts and photography.
Sorting Data
To sort your PivotTable data, point to the 'Sort' option and click 'Sort Largest to Smallest.' This action organizes the genres based on their total sales amounts, from the highest to the lowest. If you need to revert this action, you can click 'Undo' on the Quick Access Toolbar.
Filtering Data
Filtering is another powerful feature that helps focus on specific data points. To filter your PivotTable, click the down arrow next to 'Row Labels.' From there, you can select 'Label Filters' and choose an option like 'Begins With.' For instance, typing the letter 'C' will display only the genres that start with that letter. To remove the filter, click the down arrow again, which will now appear as a funnel icon, indicating that a filter is applied.
Using Slicers for Filtering
Slicers are an effective way to filter PivotTable data visually. For detailed instructions on using slicers, refer to the fourth video in this course. Slicers, timelines, and pivot charts can significantly enhance your data analysis capabilities.
Selecting Specific Items
To view only specific items in your PivotTable, select the cells containing the desired items, which can be text or dates under row labels (note that numbers cannot be selected). Right-click on the selected genres, point to 'Filter,' and choose 'Keep Only Selected Items' to display only those genres.
Displaying Top Genres
To show only the top three genres based on grand totals, point to 'Filter' and select 'Top 10.' Although it may seem counterintuitive to click 'Top 10' to see the top three, simply change the number from 10 to 3 and click 'OK' to display the top three genres.
Summarizing Data
Until now, the values in the PivotTable have been displayed as the sum of the sales amount field. However, you can use other functions for summarization. Right-click the cell in the Grand Total column to access options like 'Min,' 'Max,' and 'Average.' Selecting 'Average' will summarize the values in the PivotTable as averages.
Show Value As
In addition to summarizing sales figures, you can modify how values are displayed in the Grand Total column. Point to 'Show Value As' to explore options such as 'Percent of Row Total' and 'Percent of Column Total.' For example, selecting 'Percent of Grand Total' will reveal that the arts and photography genre represents a little over 10% of total sales, along with insights into how each store contributes to that figure.
Conclusion
In this video, we explored various methods to analyze PivotTable data, including sorting, filtering, and summarizing. These techniques are essential for gaining insights into your data, particularly in specific genres like arts and photography. Up next, we will delve into using slicers, timelines, and pivot charts for further analysis.

FAQ :

What is a Pivot Table?

A Pivot Table is a powerful Excel feature that allows users to summarize and analyze large datasets by organizing data into a more manageable format.

How do I sort data in a Pivot Table?

To sort data in a Pivot Table, click on the 'Sort' option in the Quick Access Toolbar and choose to sort from largest to smallest or vice versa.

What are Slicers and how do I use them?

Slicers are visual tools that allow you to filter data in a Pivot Table easily. You can add a slicer by selecting the Pivot Table and choosing the 'Insert Slicer' option.

Can I filter data based on specific criteria?

Yes, you can filter data in a Pivot Table by clicking the down arrow next to Row labels, selecting 'Label filters', and entering your criteria.

What functions can I use in a Pivot Table?

You can use various functions in a Pivot Table, including SUM, AVERAGE, MIN, and MAX, to summarize your data in different ways.

How do I display only the top 3 items in a Pivot Table?

To display only the top 3 items, right-click on the relevant field, select 'Filter', then 'Top 10', and change the number to 3.

Quelques cas d'usages :

Sales Analysis for Retail

A retail manager can use Pivot Tables to analyze sales data by sorting and filtering to identify top-selling products and trends over time.

Performance Tracking in Marketing

A marketing analyst can utilize Pivot Tables to summarize campaign performance data, using slicers to filter results by different demographics or time periods.

Financial Reporting

An accountant can apply Pivot Tables to summarize financial data, using functions like AVERAGE and MIN to provide insights into expenses and revenues.

Inventory Management

A warehouse manager can leverage Pivot Tables to track inventory levels, using filters to focus on specific product categories and assess stock levels.

Project Management

A project manager can use Pivot Tables to analyze project timelines and resource allocation, applying filters to view specific phases or tasks.

Glossaire :

Pivot Table

A data processing tool used in Excel to summarize, analyze, explore, and present large amounts of data in a concise format.

Sorting

The process of arranging data in a specific order, such as ascending or descending, to identify trends or patterns.

Filtering

A method to display only the data that meets certain criteria, allowing users to focus on specific information.

Slicers

Visual filters in Excel that allow users to filter data in a Pivot Table easily and intuitively.

Timelines

A visual control in Excel that allows users to filter data based on dates in a Pivot Table.

Grand Total

The total sum of all values in a specific field within a Pivot Table.

Functions

Predefined formulas in Excel that perform calculations on data, such as SUM, AVERAGE, MIN, and MAX.

Percent of Grand Total

A calculation that shows how much a specific value contributes to the overall total, expressed as a percentage.

00:00:07

One of the most common ways is sorting;

00:00:10

it helps you quickly see trends in your data:

00:00:14

right click a value, such as the Grand total

00:00:17

for the arts and photography genre;

00:00:20

point to Sort;

00:00:22

click Sort largest to smallest, and the genres are sorted

00:00:27

from the largest to smallest grand total sales amounts.

00:00:32

On the Quick Access Toolbar, click Undo, to undo the sort.

00:00:39

You can also filter your pivot table;

00:00:42

it helps you focus on the data you want to analyze:

00:00:45

click the down arrow next to Row labels.

00:00:49

Since we clicked the down arrow for a label, point to Label filters;

00:00:55

click an option, such as Begins with;

00:01:00

type your criteria, such as the letter C;

00:01:05

and click OK,

00:01:07

and only genres of books that start with C are displayed.

00:01:12

To remove the filter, click the down arrow again,

00:01:16

it now looks like a funnel because a filter is applied,

00:01:20

and click Clear filter from genre.

00:01:26

Slicers are one of the best ways to filter your pivot table data.

00:01:30

For information about using slicers, see the fourth video in this course.

00:01:35

Use slicers, timelines and pivot charts to analyze

00:01:39

your pivot table data.

00:01:42

To view only the items in your pivot table that you want,

00:01:45

you can select the cells that contain the items.

00:01:48

These can be text, or dates, under row labels.

00:01:52

You can't use numbers.

00:01:54

Right click them, genres in this example;

00:01:58

point to Filter;

00:02:00

click Keep only selected items,

00:02:04

and only the selected genres are displayed.

00:02:09

To show only the three genres with the highest grand totals:

00:02:13

right click a genre; point to filter;

00:02:17

click Top 10; I know, clicking Top 10 to see the top 3

00:02:22

doesn't seem to make sense, but look, change 10 to 3;

00:02:27

click OK, and the top three genres are displayed.

00:02:34

Until now, the values in the pivot table have been

00:02:36

displayed as the sum of the Sales amount field,

00:02:40

but you can use other functions.

00:02:43

For example, right click the cell in the Grand total column;

00:02:47

point to Summarize values by;

00:02:51

there are a lot of options such as Min and Max;

00:02:55

click an option, such as Average,

00:02:58

and now the values in the pivot table are summarized as averages.

00:03:05

In addition to summarizing the sales figures,

00:03:08

you can show them as a calculation.

00:03:11

you can cell on the Grand Total column;

00:03:15

point to Show value as; there are a lot of options,

00:03:20

such as Percent of row total, and Percent of column total;

00:03:27

pick an option, such as Percent of grand total,

00:03:31

and you can see that arts and photography represents a little

00:03:34

over 10% of sales, and how each store contributes to that figure.

00:03:41

Up next: use slicers, timelines and pivot charts

00:03:46

to analyze your pivot table data.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2672 Views

Video Training Microsoft Excel - Advanced level

With our "advanced" tutorials, discover and master the full potential of Excel, using complex functions, macros and the power of DCTs and Po...

ALL COURSES