Converting a Pivot Table into a Frequency Distribution In Excel
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.
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.
#2. Drag the ‘Amount’ field into the Rows as well as into the values area.
#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.
#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.
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.
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.