USING EXCEL TO ANALYZE NATIONAL SECURITY DATA (PART II)

Subtotals give you another powerful way of summarizing information. You often receive data already grouped in categories. You can use the subtotal feature to organize and perform mathematical functions on these various categories. In the next example, we’re going to count how many of the casualties from Afghanistan have occurred in each branch of service (Army, Air Force, Navy and Marine Corps).

Again, the video below will take you on a visual tour of the steps in this subtotals exercise. The text version follows.

 

The text version:

First, we need to remove the filter we added in our previous example. Click on the Pay Grade arrow again, scroll up and click on the “(Select All)” option. (This tells the spreadsheet to remove the subtotals and show all the values.) Next, we need to organize the data so the subtotal function can perform the proper operation. Click on the cell A4 under the header “Service,” then click the Data menu, Sort, then OK. As we learned in the previous lesson, this tells the spreadsheet to organize the data in alphabetical order by branch of service — “A” for Army, “F” for Air Force, and so on. (There is a key at the bottom of the spreadsheet for both the service and component columns.)

Now, with your cursor still in cell A4, click on the “Data” menu, then “Subtotal” in the “Outline” box. Now use the pull downs to indicate “At each change in: Service,” “Use function: Count,” Add subtotal to: Service” (uncheck Race/Ethnic if that box is already checked), like in the screenshot on the right.

Click OK. You should see a new column A (this is where Excel put the subtotals). Scroll down the spreadsheet until you reach the end of the Army (use the last names as a guide). If you’re working in the same spreadsheet, you should see “A Count” in cell A1275, with the number 1,271 in the adjacent cell B1275. That’s how many rows it counted with the value “A” in the service column.

Now scroll back up to the top of the page. In the upper-left-hand corner of the spreadsheet, you should see three small boxes with the numbers 1, 2 and 3. Click on 2. This collapses the rows to show only those with the subtotal counts. In this example, the counts reflect the number of casualties in each branch of service — exactly what we wanted to find.

Not surprisingly, the ground forces (Army and Marine Corps) account for the vast majority — more than 90 percent — of the fatalities in Afghanistan.

Next up: PIVOT TABLES.