To use pivot tables in Excel, make sure the data have headers or titles and there are
no entirely empty columns or rows.
Click somewhere on the data Click Insert, then PivotTable
The entire data will be automatically selected.
Click OK First, suppose I want a grouped frequency
distribution for Mileage.
Then I’ll drag mileage into the Rows area here,
And drag it again into the Values area.
Because mileage is quantitative, Excel automatically shows the sums.
Since I’m interested in frequencies, I will right-click anywhere in the sum column and
select: Summarize Values by count.
Now, I want to group the data into these class intervals:
So, I’ll to right-click anywhere in the Row data and select Group.
Since the lower limit for the first class in my distribution is 0, I’m going to be
starting at 0.
The Ending at is going to adjust itself based on the class width I select.
Since my class width is 10,000, I’m going to leave By at 10,000.
And that’s the frequency distribution table.
Now, if I want a relative frequency distribution instead, I can just right click on any count
value and Show Values As: I can use % of Grand Total or % of Column
Total since I have the only one column.
And that’s the relative frequency distribution.
I’m going to change it back to frequency.
Now, I want to display the frequency and relative frequency side-by-side.
I’m going to drag mileage again into the Values field, then I’m going to right click
somewhere on the second one and Show Values As: % of Column Total
Next, suppose I want to see the mileage distribution of American cars only, then I can drag Origin
into Filters, and then click on the dropdown here and select American.
And that shows the distribution for American cars only.
I’m just going to uncheck origin here to remove it.
And also remove the second mileage field here.
Suppose, I now want to see the mileage distribution for all car types at the same time, then I
can just drag Type into Columns.
I’m going to highlight the entire table, right click and centre for better display.
I can also click on the Column dropdown here and select only the car Types I want.
And I can show this as % of Grand total as in a contingency table.
Or as % of column total to reveal mileage distribution within each car type.
Now, I’m going to clear the areas by unchecking the fields here.
Suppose I now want to know the percent of German cars that are convertibles.
I can drag Origin into Rows and Type into Columns, and then drag any of them into Values.
That gives us the 2-way table.
Note that it does not matter which one is in Rows and which is in Columns.
Since I want the percent of German cars (note that the key words here are “of German”),
then I’ll right click somewhere in the table data and Show Values As: % of Row totals
since German is in the Rows.
Note also that the Rows totals are 100%.
Therefore, the % of German cars that are convertibles is 8.11%.
Now, suppose I want the mean price of all SUVs sold.
I’m going drag Type into Rows here and drag Price into the Values area.
Since I want the mean price, I’m going to right-click on a value and Summarize Values
By: Average.
I can also can set them to 2 decimal places by highlighting the values and pressing Ctrl+Shit+1.
And these are the mean prices for the different car types.
If I want the sample standard deviations instead, then I’ll right click, Summarize Values
By, More options… : select the sample standard deviation
Click OK.
Now, suppose I want to find another measure like the median price for minivans, and median
is not listed under More Options… here One thing I can do is to double-click on the
minivans value here.
And that will show me the table containing minivan data only.
Then I can just type =median(
click on E to select the entire column and enter.
And that the median.
And that’s it for this video.
Thanks for watching.