**VetPop2007 Tutorial**

**January 2008**

Table of Contents

[How to Access VetPop2007 Tables Using Microsoft
Excel](#Access_VetPop_Tables)

> [Learn more about Pivot tables (link)](#PivotTables101)
>
> [Access a free Excel viewer](#FreeExcelViewer)

[State and National Data](#state-and-national-data)

##### [**County Data**](#County)

[Modifying PivotTables](#Modifying_Tables)

[Examples](#Examples)

> [Introduction to Pivot Table Wizard](#PivotTable_Wizard)

[Using PivotChart](#using-pivotchart)

[Using the General Features of Pivot
Tables](#using-the-general-features-of-pivottables)

[Saving the Results](#Saving_Results)

**Contact:**

Mike Wells, Office of Policy and Planning

Department of Veteran Affairs

\(202\) 461-5829

Michael.Wells@va.gov

[]{#Access_VetPop_Tables .anchor}**[How to Access VetPop 2007
Tables]{.underline}**

The Department of Veterans Affairs (VA) provides the official estimates
of the veteran population using the Veteran Population Model (VetPop).
The current version, VetPop 2007, contains estimates of the number of
veterans classified by selected characteristics for the time period
between April 1, 2000, and September 30, 2036. To access VetPop 2007
tables on the internet, go to <http://www1.va.gov/vetdata/> and click on
"Demographics." Then select the appropriate geography: National, State,
County/VISN, or Congressional District.

*Microsoft Excel*

[]{#PivotTables101 .anchor}VetPop reports are presented in special Excel
files known as PivotTables. Microsoft Excel describes a PivotTable
report as "an interactive table that quickly combines and compares large
amounts of data. You can rotate its rows and columns to see different
summaries of the source data, and you can display the details for areas
of interest. You can use this report to analyze the data, make
comparisons, detect patterns and relationships, and discover trends." If
you need more detailed information than shown in the examples of this
document, click on the following link to learn more about PivotTables.

<http://office.microsoft.com/en-us/assistance/HA010346321033.aspx>

[]{#FreeExcelViewer .anchor}Users who do not have Excel on their
computers will not be able to view the Excel PivotTables. However, Excel
Viewer is available free of charge from Microsoft. Excel Viewer will
enable you to open, view, and print Excel workbooks but not to modify
the default layout for the report. Click the following link to download
this application with accompanying documentation:

<http://office.microsoft.com/en-us/assistance/HA011620741033.aspx>

###### State and National Data

VetPop 2007 State and National Tables consist of 17 reports describing
veterans in terms of 3 types of estimates, 7 variable groupings, and
geography. The 3 types of estimates are:

L Tables: the number of veterans living at specific points in time;

D Tables: deaths over specific periods of time; and

S Tables: separations over specific periods of time.

  --------- ------------------ ------------------------ ------------ ------------ -----------------
  **ROW**   **COLUMN**         **PAGES**                **LIVING**   **DEATHS**   **SEPARATIONS**

  State     Age (5-yr)         Year, Gender, Period of  1L           1D           1S
                               Service                                            

  State     Period of          Year, Gender, Age (5-yr) 2L           2D           2S
            Service\*                                                             

  State     Race/Ethnicity     Year, Gender, Age (5-yr) 4L                        

  Year      Race/Ethnicity     Age (1-yr), Gender,      5L                        
                               Period of Service                                  

  Year      Branch of Service  Age (1-yr), Gender,      8L           8D           8S
                               Period of Service                                  

  Year      Officer/Enlisted   Age (1-yr), Gender,      9L           9D           9S
                               Period of Service                                  

  Year      Gulf War Division  Age (1-yr), Gender,      10L          10D          10S
                               Period of Service                                  
  --------- ------------------ ------------------------ ------------ ------------ -----------------

> \* Differs from the \"Period of Service\" variable appearing in all
> other reports in that it contains subtotals of certain periods.

-   The user should first determine the key variables of interest (under
    the headings "COLUMN" and "PAGES") and the level of geography (under
    the heading "ROW") desired. Then, refer to columns on the far right
    that indicate separate tables for living veterans (L), deaths (D),
    and separations (S).

-   State level data are provided in Tables 1, 2, and 4, and national
    data are provided in Tables 5, 8, 9, and 10.

-   In order to keep the size of the state data sets small enough to
    manage and the cells large enough to have statistical credibility,
    data in the state tables were combined into 5-year age ranges.
    Single-year-of-age classification is used in the national tables.

[]{#County .anchor}**[County Data]{.underline}**

County data (both living and deaths) are presented using Excel
PivotTables at <http://www1.va.gov/vetdata/>. Click on "Demographics,"
and under "Geography" choose\
"County / VISN." Veterans are classified in county data by 4 age groups,
gender, and year (4/1/2000 - 9/30/2030). Counties have been aggregated
to yield VISN groupings useful for VA health planning purposes. County
veteran population data have also been used to create estimates and
projections for the 110^th^ Congressional Districts.

As an example of using VetPop county data in PivotTable format, consider
how one would obtain the living veteran population of Los Angeles County
in California as of 9/30/2006:

1)  Go to <http://www1.va.gov/vetdata/>, and click on "Demographics."
    Click "County / VISN" under "Geography."

2)  Click on the table next to "State" and under "Living".

3)  Click the down arrow button next to the "State" (Cell B3)

4)  Select "California," and you will see Los Angeles County veteran
    population estimates listed for various dates including 9/30/2006.

or . . .

2)  Click on the table next to "VISN" and under "Living".

3)  Click the down arrow button next to the "VISN" (B3)

4)  Select "VISN 22" where Los Angeles County is included.

> Los Angeles County has an estimated 406,565 veterans as of 9/30/2006.
> Rounding to the nearest 1,000 - which is always appropriate for VetPop
> estimates - we can say that a reasonable estimate of the number of
> veterans in Los Angles County as of 9/30/2006 is 407,000.

[]{#Modifying_Tables .anchor}**[Modifying Pivot Tables]{.underline}**

The reference date for a given table depends on the type of estimate.
The L Tables are based on a point in time: September 30 of each year.
The D and S tables, in contrast, represent the number of deaths or
separations occurring over an entire fiscal year. The reference date is
found in Cell B1 (first of the page variables) in the state tables, and
in Column A (first of the column variables) of the national tables.

+----------+-----------------------------------------------------------+
| Page     | Title                                                     |
| v        |                                                           |
| ariables |                                                           |
+----------+-----------------------------------------------------------+
|          | Row variables                                             |
+----------+-----------------------------------------------------------+
| Column   | Data                                                      |
|          |                                                           |
| v        |                                                           |
| ariables |                                                           |
+----------+-----------------------------------------------------------+

By clicking on the drop down arrow next to the variable, you can change
the value. Often, the value is initially set to ALL. A simple change
might be to select Gender=Male in order to see data for Males only.

[]{#Examples .anchor}**Examples**

To illustrate the power of the reporting capability of VetPop2007 data
provided in PivotTable format, we provide three examples below and
suggest that the user duplicate the steps as described.

Note that changes made to the original on-line PivotTables cannot be
saved because they are read-only documents. However, after you have made
desired changes to a PivotTable (e.g., by manipulating row, column, and
page variables, and possibly, by grouping rows and/or columns), it is
possible to save your work (and economize on file size) by opening up a
new Excel file on your PC and using Paste Select/Values to paste the
unformatted data into a new spreadsheet that can be manipulated in the
usual ways.

[Example 1]{.underline}: Calculate the percentage change in the number
of living veterans in Florida from 9/30/00 to 9/30/2010.

[Steps]{.underline}:

1.  What are the requirements to calculate the rate of change between
    2000 and 2010? We need data describing living veterans classified by
    state and year. The table that has this information is State Table
    1L.

2.  Go to <http://www1.va.gov/vetdata/> , and click on "Demographics."
    Choose "State" under "Geography."

3.  Click button ![](media/image1.png){width="0.4375in"
    height="0.1875in"} and then Open.

4.  Click the down arrow button next to "Date" (cell B1) and select
    9/30/2000. Florida's estimated veteran population as of 9/30/2000 is
    1,870,180.

5.  Repeat Step 4 and select 9/30/2010; Florida's projected veteran
    population as of 9/30/2010 is estimated to be 1,650,876.

6.  Calculate the percentage decrease \[(1,870,180 -- 1,650,876)/
    1,870,180\] = 11.7%.

7.  Use the Back button to return to the main table.

[]{#PivotTable_Wizard .anchor}[Short Cut and Introduction to PivotTable
Wizard]{.underline}:

The above steps involve making two tables: one for the date 9/30/2000
(Step 4) and a second for the date 9/30/2010 (Step 5). There is an
easier way: use the PivotTable Wizard and design the single table that
you want by changing the information identified in columns from "Age
Group" to "Date."

New Step 4 (N4): Go to any cell in the body of Table 1L and RIGHT-click.

N6: Choose "PivotTable Wizard."

N7: Choose "Layout."

N8: Drag the variable "Age Group," now a Column heading, over to the
left stack of Page variables and put it below the Gender variable. Then,
drag "Date" from the Page variable stack over to the Column heading
where "Age Group" was originally. We have switched a Column and Page
variable. Now we have a design for a single table that is exactly what
we want: veterans classified by state (Rows) and date (Columns).

N9: Hit "OK."

N10: Hit "Finish."

N11: Close any dialogue boxes that may clutter your screen, and you now
have the desired single table. Go to the Florida row, and pick off the
9/30/2000 and 9/30/2010 entries.

The above PivotTable Wizard approach may seem like more and unnecessary
steps at first. However, using the Pivot Table Wizard is very quick.
More important, it is a good idea to get acquainted with using the power
of PivotTables to design the table you want in any number of ways
(re-specifying row and column variables being particularly important)
rather than accepting the default table design. And finally, if you
wanted to graph the veteran population of Florida over time, you would
need the single table that we just created using the PivotTable Wizard.
The easy way to create such a graph is by using PivotChart, and this
Excel feature will be described shortly.

# [Example 2]{.underline}: Compare the death rates for the veteran population in Florida and the total veteran population in Fiscal Year 2001 (FY 2001).

[Steps]{.underline}:

1.  Requirements: deaths, living veterans, state, and FY 2001.

2.  Open Table 1D, by selecting the
    button![](media/image2.png){width="0.4270833333333333in"
    height="0.16666666666666666in"}.

3.  We will need to modify the default report for Table 1D by pivoting
    the Fiscal Year to the column position. To get Fiscal Years as
    column headings, let's practice using the PivotTable Wizard we just
    learned about.

4.  RIGHT-click any cell in the PivotTable, select the "PivotTable
    Wizard" option, and then click "Layout".

5.  Drag the "Fiscal Year" button from the Page position to the Column
    position, and drag "Age Group" button from the Column to the Page
    position. Click "OK", and then click "Finish".

6.  Record deaths in FY 2001 for the state of Florida (53,598). The
    estimated number of total veteran deaths (nationwide) in FY 2001 is
    636,899.

7.  Repeat steps from Example 1 to get the relevant denominators: living
    veterans (nationwide and in Florida) as of 9/30/2000, the beginning
    of FY 2001. We find that as of 9/30/2000 there were 1,854,155 living
    veterans in Florida and 26,092,046 total living veterans.

8.  Computing death rates, we conclude that in FY 2001 the Florida crude
    death rate was 2.9%, and the national veteran population crude death
    rate was 2.4%. Florida has a 18.4% \[= (2.9/2.4) - 1\] higher crude
    death rate than the nation in FY 2001.

9.  Use the Back button to return to the main table.

[Example]{.underline} 3: Calculate the rate of increase for living
veterans age 85 and older in Florida from 9/30/2000 to 9/30/2010.

[Steps]{.underline}:

1.  Requirements: living veterans, state, age group 85 and older, and
    year.

2.  Open Table 1L by selecting the
    button![](media/image1.png){width="0.4375in" height="0.1875in"}.

3.  We will use the Group function, and then the Hide Details function
    to obtain the sum of all veterans age 85 and older.

-   First, select cells P6 and Q6 (the two age groups: 85-89, and 90+)

-   Holding the cursor over the selected area, click the right mouse
    button.

-   Select the "Group and Show Detail" feature, and then select "Group".

-   You have just created a new Group1. Rename "Group1" to "85+".

-   Click on the newly created "85+" age group cell.

-   Click the right mouse button, choose "Group and Show Detail" again
    and then click "Hide Detail". The two selected age groups are
    summed, and formatted in one column.

4.  Click the down arrow button next to the Date (Cell B1) and select
    "9/30/2000." Record the projected veteran population age 85 and
    older for Florida for 9/30/2000 (53,125). Then change Cell B1 to
    9/30/2010, and record the projected veteran population age 85 and
    older for Florida for 9/30/2010 (131,766).

5.  VetPop2007 projects a 1.5-fold increase (148% increase) in the
    number of 85 and older veterans in Florida from 2000 to 2010.

####### Using PivotChart

Looking at the last example, perhaps you are interested in creating a
chart (or graph) of the number of veterans 85 and older by state.
Continuing with the previously opened file, Table 1L, select the chart
icon ![](media/image3.png){width="0.2604166666666667in" height="0.25in"}
on the PivotTable toolbar. This action creates Pivot Charts. If the
PivotTable toolbar is not visible, click on "View", "Toolbars", and
check "PivotTable". A PivotChart can produce a large number of charts,
one for each combination of the pivot variables: period of service, age,
gender, state, and year. However, the initial chart displayed reflects
the entire complexity of the underlying table. For this example,
hundreds of data points are displayed, just as hundreds of data points
are shown in the Excel PivotTable. In fact, the amount of data displayed
renders the chart incomprehensible. To simplify this chart, deselect all
age groups other than the 85+ group in the pull down menu for the
grouped age categories (see "Age Groups2"). The chart should now be
clearer. With this quick adjustment you should have created an
informative chart showing which states have the greatest number of older
veterans.

# 

# [Using the General Features of PivotTables]{.underline}

Some of the many ways to change the default report (table) are the
following:

1.  [An alternative page can be selected]{.underline} by using the drop
    down menus in the cells at the top of column B. Click on the arrow
    in one of the cells and a selection list will appear. Click on your
    selection (and in Excel 2000 click "OK') and a new tabulation will
    appear that is restricted to the selected group.

2.  [Rows (or columns) can be combined]{.underline}. Hold the \"Ctrl\"
    key and use the mouse to select the rows by clicking on the row
    label cells to combine (release "Ctrl"). Right click and then select
    \"Group and Outline\" on the pop-up menu and then click \"Group\" on
    the sub-menu. Make sure the cursor is within the selected rows. As
    before, right click and select "Group and Outline." Now click \"Hide
    Detail.\" The selected rows will be replaced by a single row
    containing the sum of the originally rows. The combined row will
    have the heading \"Group N\" where N is an integer. It can be
    replaced with more meaningful text.

3.  [Variables can be interchanged between rows, columns, and pages and
    more than one variable can be put into a row or column]{.underline}.
    This can be done by dragging variable names from one place to
    another on the spreadsheet itself. But it is easier to do it by
    using Step 3 of the PivotTable Wizard (and in Excel 2000 and 2002
    the "Layout" option). As we have shown, one way to get to the
    PivotTable Wizard is to place the cursor within the table and
    RIGHT-click. Then, click PivotTable Wizard on the menu that appears.
    (One can also get to the Wizard from the PivotTable toolbar, or from
    the Data menu on the Menu Bar.)

4.  [There are certain restrictions]{.underline}. The column variable in
    Tables 2 (period of service column headings) cannot be moved to a
    page variable because it has subtotals; it can be moved to a row
    variable. The column variables in the county tables cannot be moved
    to the page variable position. They also can not be moved to the row
    position using the PivotTable Wizard. Many users will not be able to
    have more than two variables in either the row or column position
    due to memory limitations of your particular computer. It is also
    inadvisable for some users modifying PivotTables using the
    PivotTable Wizard to select the New Worksheet option, which requires
    more memory than selecting the Existing Worksheet option.

5.  [Deleting or changing rows, columns, or cells cannot be done the way
    it is usually done in Excel]{.underline}. In Excel 2000 deleting a
    row or column can be done using the drop down menu obtained by
    clicking the arrow in the cell with the name of the variable. For
    rows, the arrow is at the head of the column of row labels; for
    columns, it is at the left end of the row of column labels. In Excel
    97, it can be done by double clicking the cell containing the name
    of the variable. When a row or column is hidden in this way the sum
    of the totals and sub-totals are unchanged.

6.  By [double clicking on a data cell]{.underline} within the table of
    a PivotTable you can see the underlying data that were used to
    derive the value in the cell. The underlying data will be displayed
    on an additional worksheet of the PivotTable.

7.  []{#Saving_Results .anchor}[Saving the Results]{.underline}. The
    Excel files of the default reports are "read-only"; you can print
    them or copy them but you can't change them permanently. However, if
    you save a copy of the file under a new name, then any changes made
    to the PivotTable can be preserved. Alternatively, if you wish to
    have full spreadsheet capability in place of PivotTables capability,
    then copy the table and paste it into a new spreadsheet as values,
    using Paste Special in the Edit drop down menu. If the formats are
    also desired, use Paste Special a second time to paste the formats
    as well. The Paste/Special-Values method uses approximately 50% less
    disk space when saved than saving the PivotTable directly.