__Example / Demonstration__:For this demonstration, we are going to be utilizing the,

**“Removing Duplicate Entries (MS-Excel).csv”**data file. This file can found within GitHub data repo, upload data: July 12, 2018. If you are too lazy to navigate over the repo site, the raw .csv data can be found down below:

*VARA,VARB,VARC,VARD*

Mike,1,Red,Spade

Mike,2,Blue,Club

Mike,1,Red,Spade

Troy,2,Green,Diamond

Troy,1,Red,Heart

Archie,2,Orange,Heart

Archie,2,Yellow,Diamond

Archie,2,Orange,Heart

Archie,1,Red,Spade

Archie,1,Blue,Spade

Archie,2,Red,Club

Archie,2,Red,Club

Jack,1,Red,Diamond

Jack,2,Blue,Diamond

Jack,2,Blue,Diamond

Rob,1,Green,Club

Rob,2,Orange,Spade

Brad,1,Red,Heart

Susan,2,Blue,Heart

Susan,2,Yellow,Club

Susan,1,Pink,Heart

Seth,2,Grey,Heart

Seth,1,Green,Club

Joanna,2,Pink,Club

Joanna,1,Green,Spade

Joanna,1,Green,Spade

Bertha,2,Grey,Diamond

Bertha,1,Grey,Diamond

Liz,1,Green,Spade

Mike,1,Red,Spade

Mike,2,Blue,Club

Mike,1,Red,Spade

Troy,2,Green,Diamond

Troy,1,Red,Heart

Archie,2,Orange,Heart

Archie,2,Yellow,Diamond

Archie,2,Orange,Heart

Archie,1,Red,Spade

Archie,1,Blue,Spade

Archie,2,Red,Club

Archie,2,Red,Club

Jack,1,Red,Diamond

Jack,2,Blue,Diamond

Jack,2,Blue,Diamond

Rob,1,Green,Club

Rob,2,Orange,Spade

Brad,1,Red,Heart

Susan,2,Blue,Heart

Susan,2,Yellow,Club

Susan,1,Pink,Heart

Seth,2,Grey,Heart

Seth,1,Green,Club

Joanna,2,Pink,Club

Joanna,1,Green,Spade

Joanna,1,Green,Spade

Bertha,2,Grey,Diamond

Bertha,1,Grey,Diamond

Liz,1,Green,Spade

Let’s get started!

First, we’ll take a nice look at the data as it exists within MS-Excel:

The easiest way to start building pivot tables, is to utilize the

**“Recommended PivotTables”**option button located within the

**“Insert”**menu, listed within Excel’s ribbon menu.

This should bring up the menu below:

Once this has been completed, click

**“OK”**.

This should generate the following menu:

**“Sum of VARB by VARD”**– This table is summing the total of the numerical values contained within

**VARB**, as they correspond with

**VARD**entries.

**“Count of VARA by VARD”**– This table is counting the total number of occurrences of categorical values within variable column

**VARD**.

**“Sum of VARB by VARC”**– This table is summing the total of numerical values contained within

**VARB**, as they correspond with

**VARC**entries.

**“Count of VARA by VARC”**– This table is counting the total number of occurrences of categorical values within variable column

**VARA**.

**“Sum of VARB by VARA”**– This table is summing the total of the numerical values contained within

**VARB**, as they correspond with

**VARA**entries.

Now, there may come a time in which none of the above options match exactly what you are looking for. In this case, you will want to utilize the

**“PivotTable”**option button, located within the

**“Insert”**menu, listed within Excel’s ribbon menu.

Go ahead and select all row entries, across all variable columns.

Change the option button to

**“New Worksheet”**, instead of

**“Existing Worksheet”**.

Once this has been complete, click

**“OK”**.

Once this has been accomplished, you’ll be graced with a new menu, on a new Excel sheet (same workbook).

I won’t go into every single output option that you have available, but I will list a few you may want to try yourself. Each output variation can be created by dragging and dropping the variables listed within the topmost box, in varying order, into the boxes below:

If

**VARA**and

**VARC**are both added to Rows, you will view the categorical occurrences of variable entries from

**VARC**, with V

**ARA**acting as the unique ID.

Order matters in each pivot table variable designation place.

So, if we reverse the position of

**VARA**and

**VARC**, and instead list

**VARC**first, followed by

**VARA**, then we will a table which lists the categorical occurrences of

**VARA**, with

**VARC**acting as a unique ID.

If we include

**VARA**and

**VARC**as rows (in that order), and set the values variable to Sum of

**VARB**, then the output should more so resemble an accounting sheet, with the sum of each numerical value corresponding with

**VARA**, categorized by

**VARC**, is summed (

**VARB**).

**“Count of VARB”**, which presents the following options:

**“Value Field Settings”**.

This presents the following menu, from which we will select

**“Count”**.

The Pivot Table creation menu also allows for further customization through the addition of column variables.

In the case of our example, we will make the following modifications to our table output:

**VARC**will now be designated as a column variable,

**VARA**will be a row variable, and the count of

**VARB**will be out values variable.

The result of these modifications is shown below:

Our output format now contains a table which contains the count of each occurrence of each color (

**VARC**), as each color corresponds with each individual listed (**VARA**) within the original data set.In conclusion, the pivot table option within MS-Excel, offers a variety of different display outputs which can be utilized to display statistical summary data.

The most important skill to develop as it pertains to this feature, is the ability to ascertain when a pivot table is necessary for your data project needs.

I will see you next time, Data Head.

-RD