Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Pivot Table
3.
Create a Pivot Table
4.
Create Frequency Distribution using Pivot Table
5.
FAQs
6.
Key Takeaways
Last Updated: Mar 27, 2024

Frequency Distribution using Pivot Table

Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

A pivot table is a sophisticated data summarising tool that can sum, count, and sort data from tables and show the results in excel. The advantage of pivot tables is that this summarized data makes it simple to design a frequency distribution in excel.

We will help you understand the steps thoroughly through which you can create a pivot table using the data and create a frequency distribution using pivot table.

Pivot Table

A pivot table is a table that contains a summary of your data that allows you to report on and examine patterns depending on your data. Pivot tables are convenient if you have a lot of long rows or columns with data that you need to keep track of and compare.

We will now see how we can create pivot tables in excel and construct frequency distribution using pivot tables below.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Create a Pivot Table

Let us go through the steps to create a pivot table using sample data of food orders consisting of 244 records and eight columns. We will create a frequency distribution using pivot table created below. A screenshot of the first few records is given below:

 

Source of data: https://www.contextures.com/xlsampledata01.html#food

 

Step 1: Select the range of data or select all the data by clicking Ctrl + A.

Step 2: From the INSERT tab, select Pivot Table Option. 



Step 3: The following dialog box will appear. The data range will be auto-filled. Choose the option to insert the table into the existing worksheet. 

Step 4: Select the cell where you want to insert the pivot table.

 

 

Step 5: A modal dialog box or sidebar will appear for pivot fields configuration. Please select the desired field and drag it to the VALUES and ROW fields both.

 

 

Step 6: Click on the Values field (here, Sum of TotalPrice) and select count as the calculation method. This will help in creating frequency distribution using pivot tables.

 

 

Step 7: Now, your Pivot table needs to be grouped. Right-click on any of the Row elements (left side data) and a select 'group' from the menu. Fill in the following/desired values for grouping the data for usage in the frequency distribution column chart.

 

 

Your pivot table will look like this.

Create Frequency Distribution using Pivot Table

A frequency distribution is a graphical or tabular representation of the frequency of repeated elements. It displays the frequency of things or the number of times they happened in a graphic format. Let's take a closer look at frequency distribution in this section.

We will now create a frequency distribution using pivot tables.

Step 1: Select any cell inside the pivot table. Go to PivotTable Analyze/ Analyze tab and click on Pivot Chart from the options.

Step 2: Select the column chart from the opened modal and click ok.

We will finally have a frequency distribution using pivot table.

 

Output:

You can further configure the chart, or we have an entire article on charts here in Library.

FAQs

  1. What is the usage of Pivot tables?
    A PivotTable may be used to study numerical data in-depth and answer unexpected queries about your data.
     
  2. What is the difference between excel tables and pivot tables?
    An excel table is an elementary database made up of only one table. It contains data elements (columns) and a collection of members with those data elements (rows). 
    A Pivot Table is a reporting and summarising tool for excel that provides information about a table.
     
  3. What is the difference between report filters and pivot tables?
    Multiple items appear in the report filter, indicating that two or more items have been chosen. The summary values for the selected elements are displayed in the pivot table.
     
  4. What do you mean by pivot charts?
    A pivot chart is a visual depiction of a pivot table or any other tabular data that aids in the summarization and analysis of datasets, patterns, and trends.

Key Takeaways

In this article, we have extensively discussed the steps to create a pivot table using a given data or table and create frequency distribution using pivot table. 

We hope that this blog has helped you enhance your knowledge regarding pivot tables in excel and creating frequency distribution using pivot table. If you would like to learn more about pivot chartsbar charts and area charts, check out our articles on Library. Do upvote our blog to help other ninjas grow. Happy Coding!

Previous article
Multilevel Pivot Table in Excel
Next article
Pivot Charts
Live masterclass