Working With Tables

In the time of massive data, tables give us a great way to begin to really understand our data.
Start with Chapter 4, the section on "Creating and using tables".
- He discusses tables in general and the idea of tables.
- He demonstrates how to create a table.
- You saw a table in your checkbook example.
- Notice that the columns have heading.
- He shows us how to change some simple formatting in the tables.
- He explains slicers
  - A slicer allows us to select a column and filter out data matching a criteria.
  - He shows how to add slicers and select data
  - And how to clear the slicer.
- He shows how to add new data to the table (rows and columns)
- He also shows how to add a total row.

Database Features, Chapter 12

Sorting data
- Blocks of data can be sorted by columns
- blank rows in data makes this more complex.
- As long as the data has no blank rows/columns you can just sort the table without selecting the entire data set.
- He shows how to deal with title rows.
- He shows how to sort using multiple criteria.
Inserting Subtotals in a sorted list
- Given a sorted list, calculate sub totals
- Excel provides tools to do this automatically
- But the data must be in some reasonable order.
- He demonstrates how to add such lines.
- And use the outline feature that total lines add.
- He shows how to copy totals/subtotals
  - This is not a standard copy/paste.
Using filters
- When you have a large amount of data, the ability to filter the data is extremely important.
- The status bar is important to show us the number of records displayed.
- This is very straight forward in excel.
- And reasonably sophisticated
Splitting data into multiple columns
- This is a cool feature, especially when you don't control your data source completely.
- Like most other operations, however, you need to pay attention.
- Flash fill is cool!
Removing duplicate records.
- Very easy as well and probably useful if you don't have control of your data.

Explore this data
For column I, split it into two columns.
- Insert two new columns to the right
- Select column I
- Select Text to Columns on the DATA tab.
- It is delimited, so select this (or just next if it is selected)
- Split the data on commas, so select this and Finish
- Rename column I to status
- Delete the new column K
- Hide column J
Convert the data into a table.
- Click anywhere in the data
- Select Format as Table on the HOME tab.
- Select a pleasant shading scheme. I prefer one without colors in the rows for this data.
- If the My table has headers is not checked, check it.
- Click OK
You can also insert new data into the table.
- Move to column AE and in cell AE1 type total
- In cell AE2 enter =ad1+ac1 and notice how that propagates through the table.
You can now sort and filter the table.
Or add a slicer
Convert the table back to a range (Table Design Tab, Convert to Range)
On the DATA tab, you can sort and filter as well.
And compute subtotals.