Chapter 5, Subtotals, Pivot Tables and PivotCharts

• Please do the hands on exercises in this chapter.
• We will begin to try to turn data into information.
• You can find data all over the net.
• I have homicide statistics from the Erie Metropolitan Area from 2000 - 2016.
  • The information is from the PA Uniform Crime Reporting System.
  • Here is a slightly modified worksheet from this page.
  • I grabbed this using the Get External Data button on the Datatab.
• We have information on
  • The date of the crime
  • The Victim and offender
  • The relationship between the two.
  • The weapon used
  • And counts of victim and offenders.
• There are around 170 records.
• The first step to make things a bit easier is to add a year column.
  • add a column
  • Use the year function on column A.
  • Change the format to be number.
  • Copy the equation down.
• Note that it is already sorted on the year.
  • For the next part to work, we need data grouped by the category we will be exploring.
• On the Data tab, click on Subtotal in the Outline group.
  • This will bring up the Subtotal dialog box.
  • Select Year in the At each change in: area.
  • Select sum in the Use Function: area.
  • Select Victims and Offenders
  • Click on Summary below data
  • And ok.
• This will take a moment, but soon you will be presented with the data summarized by year.
  • There are totals for each year.
• Insert another subtotal
  • Make sure that Replace current subtotals is not selected.
  • Keep At each change in to be year.
  • But select count as the Use Function:
  • And select any category (Victim Sex is a good one)
  • Notice we now have two summary rows per year.
• Notice also that we have some buttons on the left.
  • Explore these.
• You can use the Remove All button on the Subtotal dialog to remove subtotals.
• Let's do one more summary
  • Sort by Offender Sex then by Death Circumstance
  • Add a subtotal by Offender Sex, just count offenders.
  • Click on the number 2 on the far left.
  • Of the 172 crimes, 20 were committed by Females, 124 by Males and 28 by Unknown.
  • Now Add another Subtotal By Death Circumstance.
    • I kept the count, but this time counted on victims.
  • Click on 2, then on + by Unknown
    • We can now see what crimes were committed by Unknown.