American FactFinder Database-compatilbe Tables

The table to the right is the web
display of estimates of married-
couple families in NYS with
children under 18 yrs.

Below, the same data with the
column headers as they appear
in an AFF Excel file (.csv).

ACS Data Table Display
ACS Excel Dispaly

The Problem:  In this format the data cannot be used by statistical or mapping software because:

  • a data element sometimes has multiple values assigned to it (e.g. the element, married families with children under 18, has four values: estimate, MOE, percent, and percent MOE);
  • header information describing data within a column can extend over multiple cells in the column;
  • within a column there is no unique value that can serve as the variable name.

Correcting the Problem assuming the AFF file was not modified within AFF and therefore has:

  • all cells in one worksheet with the file name assigned to the worksheet,
  • each row represents a geographic area with a geographic identfier in the left most cell,
  • columns contain the values assigned to each element of the table.

Steps that solve the problem are illustrated both
         in an online video linked here
         in the text that follows.

Start with
          Excel Techniques Needed, some of which are available only in Excel 2010.

Then choose
          Reformatting Table Headers Only

Use these steps to create a data table containing all cells in the table downloaded from American Factfinder and another table that documents contents of the file.

Excel Techniques Used in The Steps

Copying all active cells in a table

This requires highlighting all the active cells then clicking on the copy icon. For large tables rather than dragging your cursor from an upper corner to the lower opposite corner try:

  • clicking on cell A1 then; 
  • while holding down the Ctrl and Shift keys, repeatedly clicking on the down arrow until you reach the bottom row of the table then;
  • while continuing to hold down the Ctrl and Shift key, clicking on the right arrow key until you reach the bottom right cell in the table.

Copying an entire worksheet

  • Highlight the entire table by positioning the cursor on the gray square in the upper left of the table (above the row numbers and to the left of the column letters).
  • Then click on the copy icon.
  • Click on a new worksheet tab.
  • In the new work sheet click on cell A! and then paste

Transposing Cells

Transposing is a special form of paste that is used to switch adjacent cells across a row to display as adjacent cells down a column or vice versa.

  • Highlight the cells by clicking on the first cells and dragging the cursor across the row (column) to the last cell in the row (column). Then click on the Copy icon.
  • Click on the first cell in the empty column (row) in the location you want for the results.
  • Open the Paste menu by clicking on the small arrow under the word Paste.
  • Click on the Transpose icon (image is highlighted in red).

excel paste-copy icon Paste and Copy Icons
highlight a table One Cick to Highlight a Worksheet
Excel paste options Transpose Icon in the Paste Menu

The transpose feature works for an entire table; just start with the copy table technique described above.

Any transpose will work only when the destination for the cells is within the same Excel file as the cells being copied.  It can be in the same or a different worksheet within the same file but not in another file.

Reformatting Table Headers Only

Follow these steps to create a data table containing all data cells from the original and a documentation file that describes the content.

  1. To retain a copy of the original file copy the entire worksheet generated by AFF and paste it into a new worksheet.
  2. Label this worksheet, "datafile."
  3. Highlight and cut the row with the value in the first cell and paste it into the first row starting the cell A1 thus creating an empty row.  Delete this empty row.
  4. Highlight and cut the row with the value in the first cell and paste it into the first row starting the cell A1 thus creating an empty row.  Delete this empty row.
  5. Delete the contents of all cells in the first row except for the first 3 cells; that is the cells.
  6. Starting in the fourth cell in the row (the first blank cell in that row), type V001.
  7. To populate the rest of the cells in this row; move the cursor on to the cell containing V001;
    -with the cursor over that cell the cursor shoudl appear as a white cross (if not right clickO;
    -move the cursor to the bottom right corner of the cell and it will turn to a black cross;
    -left click and drag the black cross across the remaining blank cells in the row.
  8. This will populate the cells atop the data columns with unique values: V001, V002, V003, etc.
  9. Copy the block of cells that contain all the header information (for large tables highlight from with the lower right cell in the block up to cell A1).
  10. Click on a new worksheet tab, click on cell A1 and then paste-transpose.
  11. Label this worksheet "datadoc" as its rows list, starting with a unique identifier,  a full description of what is in each data column.
  12. Go back to the datafile worksheet and delete all the rows with header information except for the first row (the one you created).
  13. Open a new Excel file and then copy the worksheet datafile and paste it in to the new bank file.
  14. Save it as an Excel file and you have a file that is compatible with application software.
  15. Save the original file with the all the worksheets.  It will serve as documentation for the file you created, as a record of what you downloaded, and as a copy of what you took in to your analysis step.

The image to the right illustrates what the cells, which were used as examples at the beginning of this guide, look like on the new file you created for use in application software.

Below is the datadoc worksheet that that contains rows that correspond to and fully describe each column in the new file.

ACS data output for input to application software
ACS data documentation file