Tables In Excel, Tables
- This information is from the first part of Chapter 15.
- This data is my work from last time.
- This is the data we worked on last time. You can use mine or yours.
- Our goal is to explore this data to see if we can spot any information
that might be useful.
- There are two tools I would like to explore today.
- Unfortunately they don't play well together so we will explore one at a time.
- Tables
- Build the table.
- On the table tools design tab
- Table style options
- Header row, banded row, banded columns ...
- Total row.
- Notice you can compute stats on the different columns.
- Is the cost difference significant?
- Sum the Extend Cost column
- Sum the Cost Difference Column
- This is a 10% deviation, probably a problem.
- Table styles too.
- The nice part of building a table is the ability to sort and filter quickly.
- Filtering
- Let's look at all of the canceled orders
- You can filter mathematically too
- Look for values in given ranges.
- Notice on the Percent Markup we can filter by color
- And filter by text, find all months beginning with "J"
- Clearing filters with data sort & filter clear.
- Sorting
- Let's sort these by year, use the drop down
- Add a multi level sort with data/sort.
- Use this to sort by order number then by line number.
- We can also sort by color.
- We can add a slicer or two.
- Change formats.
- Delete, might need to clear filters.
- Return the table to a range (table tools)
- Sort by Order number then line number.
- It is very important to do a sort first for this particular tool.
- Total extended cost by Order number
- Remove all subtotals then sort by Territory and Country
- Build a subtotal by territory.
- Build a second subtotal by country, don't remove the first total.