Tables in Excel

Once again, please use the dataset
- This is where we ended last time
  - Blanks replaced with NA
A cool feature of Excel is tables
- Reference page 459 on in the book.
Some of this will look like what we did last time, but some will be different.
Build a table
- As long as your data is in rows, columns representing fields, no blank rows, no blank columns, a border around the outside
- Click in the table.
- On the Inserttab, in the Tables command group, click on Table (not Pivot Table).
- Click on My table has headers if it is not selected.
- Check to see that the rang is ok.
- And click ok.
Notice that
- Headers are frozen.
- Sorting and filtering tabs are automatically applied
- A formatting is applied.
Note the Table Tools special tab
- You can format the table with a different style.
The Total Row button in the Table Style Options is useful.
- Click on it.
- Scroll (or ctrl-end, or ctrl-downarrow, or ...) to the end of the table.
- Notice we can click in the boxes and get different types of computations on the row.
- Filter on NR movies only
- Notice the counts adjust to the filtered table.
- I should have mentioned this last time, but notice it filters by hiding rows.
Let's copy the filtered data.
- Insert a new worksheet.
- Select the visible data and copy and paste.
- Note only the visible data was copied.
- This is useful some times.
Filtering and sorting are as before.
But you can insert Slicers
- On the Table Tools tab, select Slicer from the Tools command group.
- Select Day
- This creates a slicer.
- Note we can select the data we are interested in.
  - This is just a fancy interface to filtering.
- Play a little.
- Notice the checkmark in the upper right, this selects 1 or many
- The clear filter.
- Add another slicer for the rating
- Notice how they interact.
- You can clean up the slicers using the Slicer Tools tab.
Naming Ranges.
Go the Raw Data table.
Select the entire table.
In the Name box type RawData
Select the Title data (just D1:E211, not the entire column)
In the Name box type MovieTitles
We will look at naming ranges next time.

The Advanced Filter on the Data tab in the Sort & Filter command group is very useful.

This is not connected to tables, but is somewhat associated.
This does not appear to work on data in a table.
Add a new worksheet.
We want a list of unique movie names.
Click on Advanced Filter
- Select Copy to another location
- Select Unique Records Only
- In the List Range box type MovieTitles
- In the Copy To box select A1
- Click on OK.
It is important to include the Column Title when selecting the range. Excel will do strange things if you don't.

A second way to advanced filter

Select Advanced Filter again.
Select Copy to another location
Select Unique Records Only
CopyTo select C1
List range, select the Movie titles by hand.
DO NOT CHANGE WORKSHEETS BACK, JUST CLICK ON OK.
- This is important when entering multisheet computations.
- A third way
  - Copy the range of all the movie titles.
  - Paste this in cell E1 of the worksheet
  - On the Data tab in the Data Tools box select Remove Duplicates
- We will want to do this for multiple data sets and columns...
- Do all three of these for park names.
- This next feature is probably accelerated.
  - You will probably do this in far more detail in dsci201 if you take it.
  - This is the proper use of advanced filters.
  - It will let us filter a data set based on a criteria.
  - Grab a new worksheet.
  - Copy all of the headings from the Data table to this worksheet.
  - In cell O2, put 41
  - Filter, selecting A1:O2 as the criteria.
  - Set RawData as the range.
  - Add Mon to cell A2
  - Filter again just below the first table.
  - Move Mon to cell A3
  - Increase the range and filter again.
  - Try 41 or 42, Mon, Thu
  - Try filtering in place
  - Try >15 <20