Tables

One more time we will work with the movies dataset.

The following starts on page 459 in the book.

Excel has some features that start to make it look more like a database interface.

The table feature is nice, but perhaps unnecessary

Move to the Sorting worksheet

Build a table

On the Insert tab in the Tables command group select Table NOT PIVOT TABLE
Select the range

Much like filtering, this adds the drop down sort/filter menus to each field.

But it also formats the table.

You can change the table format somewhat on the Table Tools Design special tab.

Please Hide columns D, and I through N

A Slicer is a user interface tool

I suspect that these will get stronger as excel matures.

But for now, build a slicer for the rating.

Select Insert Slicer on the Tools command group of the Table Tools Design tab.
Click the box next to rating
Select OK.

Select different single ratings

Toggle the Multi-select button to select multiple ratings

Please filter on G rated movies only

Add a Total Row.

Click on the table to bring up the Table Tools Design tab.
Click on the Total Row checkbox in the Table Style Options command group of this tab.
This adds a Total Row to the table.

You can add various computations to the total row for columns in the table.

By default it is calculating the sum of the wards.
This probably doesn't make sense so change it
- Click in the cell.
- This will add a drop down menu handle.
- Drop down the menu and select count
Add a count to the ratings total as well.

Change the Slicer to only look at NR movies.

We don't have numerical data, but we can "play" with the ward

Notice that we can quickly compute averages, sums, standard deviations and such here.

Add a second slicer for day of the week.

Building a Data Form

Add the form tool to the quick access area

In the upper left hand corner of the screen, select the Customize Quick Access Toolbar dropdown
Drop down the menu and select More Commands
Change Choose commands from to be All Commands
It will take a second, but the list of commands available will expand greatly.
Scroll down and select Form then click on Add
Select Ok
This has added the Form tool to the Quick Access area

Making a form

Add and the delete a new record.

You can perform a multiple sort by selecting Criteria

We will learn more about forms in DSCI 201

Close the form

Adding a calculated field to the table

Move to cell P2
Enter the start of the formula =len(
Then click in cell E2
Then enter ) and press enter.
Notice that excel
- Copied the formula down the entire table.
Change the title in cell P1 to be Title Length
- Click in cell P1
- Edit in the Formula Bar
Select NR movies shown on Monday
- Using the total row, find the min, max and average length.
Removing a table
- On the Table Tools Design tab, select Convert To Range in the Tools command group.
- Before you do this you may want to
  - Remove the total row.
  - Remove formatting as much as possible.
- Or Later
  - Select the previous table area.
  - On the Home Tab in the Editing command group drop down the Clear menu and select Clear Formats