If you find these tutorials useful and would like to link to them from your own pages,please contact the Drew Spatial Data Center Director, Dr. Catherine Riihimaki via email. You are invited to leave comments or ask questions in any of the Comment areas of each tutorial page.

Finding and Downloading Census Data

These are directions for downloading Census data as tables that you can edit in Excel and upload into ArcMap.

Go to the American FactFinder page at : http://factfinder.census.gov/home/saff/main.html?_lang=en

In the left-hand menu choose Data Sets --> Decennial Census. 

On the new Decennial page, there's a block labeled Other Resources on the right hand side of the page.  In this block, click on the Download Center link.

In the Download Center, click on the Census 2000 Summary File 1 (SF 1) 100-Percent Data link. You'll be taken to a Geography page, in which you can narrow the amount of data you want by geographic location.  The choices are listed in the table from largest area (United States) to smallest area (All Blocks in a County).  Remember:  The larger an area you choose, the larger your downloaded dataset will be.  Census data is dense, with many sub-categories within major categories like income.  Try to be as specific as possible when choosing a geographic area for data download.  This will save download time and time you use to look through the data to find what you want.

Once you've narrowed your geographic parameters sufficiently, you will choose a Download Method.

Not all kinds of data are available at every geographic level.  For instance, income data are not available in each Summary File.  Other data are not available at the smaller geographic levels (Block level).  There are times when you might have to settle for larger geographic coverage than you might want, or to get smaller sample sizes from the Census data than desirable. 

Most data that is useful for GIS is available in the Summary File 1 or Summary File 3 data sets. 

Formatting Long Strings of Numbers - Census Data

When Excel detects long number strings in any format other than Text or Number, it automatically displays them in Scientific format.  ArcMap will not import columns formatted as anything but General for the purposes of creating spatial joins.  Because of the DISPLAY of long number strings in General format as Scientific, ArcMap cannot read them.  This is especially problematic in data files downloaded from the Census Bureau.  Census Geographic Identifiers (ID field or Geo 2 field for data from before 2010) - crucial to joining Census data to TIGER shapefiles - always download in General format when imported to Excel. 

You will first need to remove the unnecessary annotation at the beginning of the number string in the ID field (or Geo 2 field) so that it will properly match the STFID field in Census Bureau shape files.

  1. Highlight the ID/Geo2 column
  2. In the Home Tab, navigate to Editing → Find and Select
  3. Choose the Replace Option.  The Find and Replace dialog box will open.
  4. Enter the value of the unwanted numbers and letters at the beginning of the string for ID/Geo2 into the Find what: field.  The value is usually 1000000US, but double check to see which portion of the ID/Geo2 field and the STFID field do not match.
  5. Leave the Replace with field blank.  This will simply delete the unwanted string of letters and numbers.
  6. Click Find Next.  When the program highlights a cell, click Replace.  Test this with a few cells to make sure that the program is only deleting the numbers and letters you want.
  7. If the first few test cells are successful, with only the ID/Geo2 column highlighted, click Replace All.  A dialog box will appear letting you know from how many cells it deleted the specified string.
  8. Click OK, then click Close.

Do the following to resolve the problem of Excel storing these numbers as scientific notations

  1. Create a new, empty column next to the ID field (Geo 2 field for Census data before 2010).
  2. Format the new column as General.  Name it Geo 3
  3. Type in the formula =Left(cell# of cell in Geo 2 field, #of digits of number)  This will convert the number to a regular, General format.
  4. Copy/paste the formula into every cell adjacent to a Geo 2 cell you need to convert
  5. Copy the entire newly formatted set of fields in the new column
  6. In same column, with all fields still highlighted, right-click and choose Paste Special.  Choose Values from the available menu.
  7. Click OK.  This should paste the actual Value of the Geo 2 field in General format into the new column and display it normally, rather than as a Scientific format.
  8. Use the Geo 3 field in your Spatial Join.
  9. This procedure will save you the effort of having to copy/paste the value of hundreds of Geo 2 cells.

Distribute Block, Block Group, County and State Data from a Single Cell into Multiple Cells in Different Columns

Look for the Microsoft Help document, Distribute the contents of a cell into adjacent column.

Preserving Leading Zeros and Keeping them Formatted as Text

Most STFID or GEOID numbers are stored as Text in the Tiger Line Shapefiles you use to visualize the boundaries of states, counties, and cenusus tracts.  In the files you download from the Census Bureau, these numbers are automatically converted into numbers by Excel.  This means that, when you try to join your data to the shapefile for analysis, you won't be able to properly join according to the geographic identifier attribute.  There is a quick solution to this located here:

http://www.facebook.com/topic.php?uid=98595894086&topic=17703

Once you follow the steps to get the leading zeros to remain, you must then create a new column and format it as text.  Then copy the data from the calculated field (=TEXT (A2, "00000") and use the "paste special" command to paste the values into the new, text-formatted column you created.