How to Convert Pivot Table into a Frequency Distribution in Excel

Published on May 23, 2017
image_pdfimage_print

Converting a Pivot Table into a Frequency Distribution In Excel

How To Convert Pivot Table Into Frequency Distribution

Pivot Table is a very useful tool that our favorite MS Excel provides us with (I know you know that! Lolz!). Pivot Table helps us make sense of a large data. This post will explain one such way to making sense of data using Pivot Tables, and that is by converting a Pivot Table into a Frequency Distribution.

This post is not a post where I’d explain Pivot Tables and its uses in detail. I’d rather focus on one important way that you can use Pivot Tables and that is by making a Frequency Distribution.

Click here to download the example file on which I’d be basing my discussions.

FrequencyDistribution1

Now, in the example file, you’ll see six columns of data. The first column has the serial numbers, the second column has names of certain products which a company deals in, the third column contains the categories to which the products belong, the fourth column has the amounts of sales made, the fifth column has the date of sale and the sixth column has the carrier names through which the products were shipped.

This data, by itself, does not give us any useful information. For example, just by looking at this data, one cannot tell the amount of sale of products in the ‘Sports’ category in the year 2012, shipped via UPS. Pivot Table arranges the data in such a way that it allows the user to churn out useful information, or as I said earlier ‘make sense’ of the data.

Now, assuming that you’ve downloaded the example file, let’s start the process. The steps are given below.

#1. Make sure that a cell in the Pivot Table is selected. Click on ‘Insert’ in the main menu and click ‘Pivot Table’. ‘Create Pivot table’ dialogue box will open. Simply, click OK to insert a Pivot Table in a new tab.

FrequencyDistribution2

#2. Drag the ‘Amount’ field into the Rows as well as into the values area.

FrequencyDistribution3

#3. Right-click on any cell in the Sum of Amount column and select Value Field Settings option.

#4. Select ‘Count’ and click OK. This will change the sum of the amounts to the count of the amounts, i.e. the frequency of occurrence of each amount.

FrequencyDistribution4

#5. Right-click on any cell in the Row Labels column and click ‘Group’.

#6. In the dialogue box that opens, you’ll need to fill 3 values. Now, after looking at the amounts in the original data, I know that the lowest amount is around $1 and the highest amount is around $2000. So in the ‘Starting at’ and ‘Ending at’ fields, you’ll need to put 1 and 2000 respectively, as that is the range we want a frequency distribution for. The ‘By’ field is where you need to put the size of one range in the distribution. So if you put, say 100 there, the data would be divided into 20 ranges of $100. Put 100 in the By field and click OK.

FrequencyDistribution5

You have your frequency distribution ready in front of you now. Now you can easily tell the number of sales for amounts 1 to 100, 101 to 200 ad so on till 1901 to 2000. Easy, wasn’t it?

Now you can also insert a Pivot Chart to get a better comparative picture of the different ranges. To insert a Pivot Chart, just click on any cell in the pivot table and go to the Analyze tab and click Pivot Chart. You’ll see all sorts of options here. Select the type of chart you want to see, and click OK. You’ll have your Pivot Chart for the frequency distribution ready.

FrequencyDistribution7

Hope you found the post useful.

If you like what I do, please share the post with your friends on Facebook, Twitter or LinkedIn using the buttons right above and below this post.

I’ll see you very soon with another useful article.

Till then, take good care and…Happy Excelling!

GET MY EXCEL GUIDE FOR FREE
COMPLETE EXCEL FUNCTIONS AND SHORTCUTS GUIDE

Subscribe to our newsletter and get my Complete Excel Functions and Shortcuts Guide for FREE!

Thank you for subscribing.

Something went wrong.

HUGE LIMITED TIME DISCOUNT ON THE BEST ADVANCED EXCEL COURSE ENROLL NOW!!
excel_look_professional
Making Excel Worksheets Look Professional – A Case Study
14 Excel tips
14 Excel Tips – Make Yourself Super-Efficient in Excel
DATA CLEANING EXCEL
Ultimate Guide to Data Cleaning in Excel – 11 Super-Powerful Data Cleaning Techniques
RANDOMIZE LIST RANDBETWEEN
Randomize a List in Excel Using RANDBETWEEN Function
FREEZE PANES
Freeze Panes in Excel – The Complete Guide
HOW TO LEARN EXCEL
How to Learn Excel and Be a Spreadsheet Champion
BEST BOOKS COURSES EXCEL VBA
Best Books and Courses to Learn Excel VBA
BECOME AN EXCEL NINJA COMPLETE REVIEW
Become An Excel Ninja – Course Review
BEST EXCEL COURSES
10 Best Online Courses to Make You an Excel Wizard
BEST BOOKS COURSES EXCEL VBA
Best Books and Courses to Learn Excel VBA
How to Sort Excel Worksheet in Alphabetical Order
How to Sort Excel Worksheets in Alphabetical Order
VBAPOST1
How to Add And Name A New Sheet At The Same Time Using Excel VBA
ROUNDING NUMBERS IN EXCEL
Rounding Numbers in Excel – The Complete Guide
number_of_words
How to Find the Number of Words in a Text String in Excel
HOW TO FIND IF A TEXT STRING CONTAINS SPECIFIC TEXT IN EXCEL
How to Find if a Text String Contains Specific Text in Excel
How To Indentify ULP case in a text string
How To Identify If A Text String Is In Upper, Lower Or Proper Case
Pivot Tables
How To Convert Pivot Table Into Frequency Distribution
How to Convert Pivot Table into a Frequency Distribution in Excel

GET MY EXCEL GUIDE FOR FREE
COMPLETE EXCEL FUNCTIONS AND SHORTCUTS GUIDE

Subscribe to our newsletter and get my Complete Excel Functions and Shortcuts Guide for FREE!

Thank you for subscribing.

Something went wrong.

Share
Tweet
+1
Pin
Share
Stumble
Enjoyed this video?
How To Convert Pivot Table Into Frequency Distribution
"No Thanks. Please Close This Box!"