USING EXCEL TO ANALYZE NATIONAL SECURITY DATA (PART II)

This feature very well could be the only thing you need to take away from this CAR session. Pivot tables are extremely powerful and flexible, and allow you to quickly drill into the data for more detailed analysis. They not only give you subtotals with the click of a mouse, they also give you the percentage of the total that each subtotal represents — thus doing the heavy lifting for you. Of course, you should always double-check by hand any numbers you plan to publish. With that, let’s get our hands dirty.

First, remove the subtotals. Click on “Subtotal” in “Outline”, then “Remove All.” Then delete Column A by right-clicking on the column.

To make a table, click on the “Insert” menu, then “PivotTable” in the “Tables” box. This opens up the Create PivotTable screen. Click OK. The table will automatically select the entire range of data — so long as your cursor is on a cell that contains data. The new window, the Field List, lets you build a PivotTable using any of the columns of data in your spreadsheet. Let’s build on what we’ve done previously.

Instead of counting the number of fatalities in just the junior enlisted ranks, let’s count the totals for each paygrade. But before we do anything, let’s visualize how we want the data displayed. It’d be nice if the pay grades were listed in ascending order, with the total number of fatalities listed in an adjacent column.

To display this, click on the PayGrade field and drag it under the “Row Labels” box. Then click on the PayGrade box again and drag it into the “Values” box. Your Field List should look like the screenshot above, right.

What do you see?

Now let’s change the parameters slightly to count the total number of fatalities in each pay grade — for each branch of service. This is basically a combination of the calculations we have performed thus far and while it may be more detail than you need for a particular story, it’s a good example of the power and speed with which you can manipulate data using PivotTables.

Right-click anywhere inside the table of data, scroll down to PivotTable Wizard, then click Layout. Now drag the Service box to the Column area. The wizard layout should look like the image to the right.

Click OK. You should see a detailed breakdown of OEF casualties, like the image below.

Subtotals and pivot tables are good practice if you ever transition to a database manager. They essentially group together various categories of data, which can be performed in database land with “Group By” and “Sum” functions.

Next up: CHARTS AND GRAPHS.