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.
  • Tables
  • Subtotals
• 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
          • Filter on the status.
        • 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.