Microsoft Excel Pivot Tables are a quick and effective way to obtain various summary measurements about a dataset. It is then possible to use the Pivot Table results to calculate secondary measurements. In this tutorial, you will create a simple Pivot Table of frequency counts, and then use the results to calculate relative and percent frequencies.

The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below.

If you see "blanks" in your Pivot Table, filter those out by selecting the down arrow to the right of the Soft Drink row in the PivotTable Fields task pane.

PivotTable Fields

Choose fields:

Soft Drinks

$\backslash (\backslash$checkmark $\backslash )$ Add to Report Filter

DIl Add to Row Labels

$$ Add to Column Labels

$\backslash (\backslash$sum $\backslash )$ Add to Values

$$ Add as Slicer

$\backslash (\backslash$mathrm$\{$Z$\}\backslash$downarrow $\backslash )$ Sort Ascending

$\backslash (\backslash$mathrm$\{$Z$\}\backslash$downarrow $\backslash )$ Sort Descending

Sort By Value...

Clear Filter from 'Soft Drinks'

Label Filters

Value Filters

Filter...

Filter Dr$.$ Pepper

$-$ Mountain Dew

$\backslash (\backslash$checkmark $\backslash )$ Pepsi

$-$ Sprite

$($blank$)$

Next, in the Pivot Table task pane, drag Soft Drinks to the Rows box and Values box. Make sure the Values box is set to Count of Soft Drinks. You now have created a Frequency Table.

Drag fields between areas below:

If the Values box is not set to Count, click the down arrow to the right, and select Value Field Settings. There you will see options for analyzing your data and Count is among those options.

In Column F and G$,$ create columns for "Relative Frequency" and "Percent Frequency $(\backslash \%)"$ that align with the Pivot Table headers.

In your new "Relative Frequency" and "Percent Frequency $(\backslash \%)"$ columns, proceed with those calculations so down the length of the Pivot Table and enter those values below.

Relative Frequency Percent Frequency $(\backslash \%)$ Coca$-$Cola

Pepsi

Sprite

Mountain Dew

Pepsi

Diet Coke

Pepsi

Coca$-$Cola

Coca$-$Cola

Dr$.$ Pepper

Coca$-$Cola

Diet Coke

Sprite

Coca$-$Cola

Mountain Dew

Pepsi

Pepsi

Dr$.$ Pepper

Pepsi

Diet Coke

Coca$-$Cola

Pepsi

Sprite

Sprite

Sprite

Diet Coke

Coca$-$Cola

Diet Coke

Pepsi

Pepsi

Coca$-$Cola

Diet Coke

Pepsi

Coca$-$Cola

Sprite

Pepsi

Mountain Dew

Pepsi

Sprite

Mountain Dew

Sprite

Pepsi

Pepsi

Coca$-$Cola

Coca$-$Cola

Sprite

Coca$-$Cola

Diet Coke

Pepsi

Mountain Dew

Sprite

Diet Coke