Filters

-NFL Player Database Sample-

Filters make it simple to, let's say, check out how many fantasy points Aaron Rodgers had in each 2016 regular season game:

Filters are located on the top right corner of each column.

Click the Filter in column B to choose a specific player.

You can type Aaron Rodgers in the search box...

...or you can click the box next to (Select All) to clear everything, and then check any player in the list of players.

(You can include as many players as you'd like) 

In this case, we are selecting Aaron Rodgers.

Filter column J to the 2016 season.

(Unless you only have the 2016 database)

If you do only have the 2016 database, do yourself a huge favor, and upgrade.

Filters are also helpful in column F to distinguish Home or Away...

...or column M to review just Monday Night Football games.

This is just scratching the surface of what you can do with these tools.

Hide/Unhide

-NFL Player Database Sample-

If there are any rows or columns that you are not interested in seeing, you can select them, right click, and select Hide.

This way you can get right to the stats.

If you want to Unhide, select the surrounding columns, right click, and select Unhide.

Sorting

-NFL Player Database Sample-

There are a few ways to Sort, but one way is right within the filter menu.

Sort Largest to Smallest in column C to easily see the most fantasy points that Aaron Rodgers scored in a game in 2016.

If you want to clear all of the Filters and Sorts that you've performed, go to the Data menu and select Clear next to Filters.

A quick Clear and Unhide gets us right back to where we started.

Pivot Tables

-NFL Team Database Sample-

First let's select all of our data by clicking the box in the top left corner by Column A and Row 1.

Choose the Insert Menu.

Select Pivot Table.

Make sure our entire range of data is selected.  In this case it's Column A to Column AS.

Now let's create this Pivot Table in a New Worksheet, and click OK.

Click anywhere in the Pivot Table on the left to open the Pivot Table Fields on the right.

All of the data that we originally selected is available in our list.  Let's click and hold our mouse on Team...

...and drag it down to the Rows box.

Drag the W/L field...

...down to the Column box.

Now we have our Rows and Column created.

Let's grab W/L again from the field list on top...

...and drag it to the Values section.  Make sure that it is set to Count.

Note: You can also click on the field to remove it, or change the setting.

 

You can find the sum, average, min, max, product, and much more.  Although this doesn't make much sense on the W/L field because it is only 2 variables, these other functions can be very helpful with things like average yards or total touchdowns.

 

You can also click on Number Format at the bottom to format to your results to different formats such as numbers, dates, percentages, and many more.

 

For now we are just going to keep the general format and count settings the our W/L field.

Now we have each teams total Wins, Losses and Ties since the year 2000.  Let's filter to only the 2016 season, and then we'll move the win column over to the left.

In our Pivot Table Fields, select Season...

...and drag down to Filters.

Now the Season filter is available above our Pivot Table.

Select the filter arrow to adjust filters.

Check the Select Multiple Items box.

Uncheck all filters.

Filter to the 2016 Season.

Now let's change the order from L-T-W to W-L-T to make our list look more traditional.

Place your mouse just above the W in cell D4 until you see this down arrow, and click it to select the entire column within the Pivot Table.

Now place your mouse anywhere around the sides of our selection until you see these 4 arrows.  Now click, hold, and drag the column to the left of the L Column.

Right-click any number in the column, and select Sort, and the Sort Largest to Smallest.

Now our Pivot Table is in order of most Wins in 2016.

We can do much more within the Pivot Table, but for now, let's select our data from cell A4 to D36.

Right-click and select Copy, or press Ctrl + C.

Paste the data in cell H1.

Now lets add a Win % column.  Type Win % in cell L1.

in cell L2, type = to begin a calculation (function).

We are going to divide Wins in cell I2...

...and divide by the total games which would be the sum of I2:K2.  Here is the complete calculation: =I2/sum(I2:K2)

This is still in a general format...

...so let's click the percentage format in the Home menu.

Now we have a percentage.

You can drag this calculation down by clicking the bottom right corner of the cell...

...and drag down to cell L33.

Let's say we want to change the Win % to the Win % against the spread.  You can always click anywhere within the Pivot Table to see the Pivot Table Fields on the right side of the screen.

Click and hold on the W/L field in the Columns section.

Drag the W/L field back up to the field list to remove from the Column Section.

Now do the same with the W/L field in the Values section.

Now let's add the W/L/T ATS field instead.

Make sure the drag the W/L/T ATS to both the Column section, and the Values section.

Drag the W column over to the left like before.

And again, we'll sort Wins by Largest to Smallest.

Copy the data (A4:D36).

Paste in H1.

Now we have each teams Win % Against the Spread in 2016.  We'll use this data in the next section to create a chart.

Charts

-NFL Team Database Sample-

Let's build a Chart from the data that we just created in the Pivot Tables section above.  REVIEW PIVOT TABLES HERE  First click and highlight cells H1 to H33.

Now hold Option and select cells L1 to L33.  Holding Option keeps your previous selection (H1:H33), and combines it with your new selection.

Now that we have both Team and Win % selected, open up the Insert Menu.

Select Recommended Charts.

You can select from the Recommended Charts, or click the All Charts tab to select from the entire list of charts.

Some of the most common charts for this type of data are Line Charts...

Bar Charts...

In this case, we are going to choose a Column Chart.

Click on the Title to edit.  Let's call this chart Win % Against the Spread.

You can also Adjust the Size of the chart by clicking and dragging the sides or corners.