ACS Data Users Group

The 2012 American Community Survey 1-year estimates are now available at http://factfinder2.cesus.gov.

I think that was factfinder2.census.gov. My browser finds it almost automatically, but of course it's useless now.

I have many complaints about AFF2, but the biggest one is the difficulty of working with data downloaded into an Excel table, with all its extra rows and columns and the fact that many numbers aren't really numbers, they're text. Comments, anyone?

I have found that to use AFF2 in excel you have to download it in the csv format, not real convenient but at least they will be numbers and not text and be sure to title your table after you have imported the data so you can go back and know which one it is!

Agree with Barbara, and agree with Patty in one respect. If you go to the table on the website, and modify the table to select only the rows or columns you want, then download, it seems to download all the rows and columns anyway. Maybe I'm missing something though. Not sure about the "many numbers aren't really numbers" part.

In Excel 2010 (and possibly in 2007 and earlier versions) you can select the entire range of values in the table, and in the upper left-hand corner of the selected range a context box appears that allows you to convert text to numeric values. Admittedly, it's an extra step in the process, but it works to resolve the values-as-text problem.

For many reasons I use AFF only when I have to. For many of my projects I use Data Ferrett or PUMS data, both of which have a steep learning curve, but has allowed me to dig much deeper into the data than I could otherwise. Honestly, I usually just do a copy and past straight from the browser into Excel and that works better for me than downloading data sets(at least when it is a small data set).

Beth, I tried what you suggested, and in my copy of Excel 2010, I don't get that context box to which you're referring. All the cells have little triangles in the upper left corner, and I've never understood why.

Gene, when I say "numbers aren't really numbers," I mean that they are "general" in Excel's formatting, and most important, you can't reliably do arithmetic with them! You can't add them up, or "sum" a group.

The triangles in the upper left means that excel recognizes a problem, most likely that the cells are numbers, but that excel is reading them as text. So if you use the arrow keys to move the cursor over the cell (not mouse over the cell), you should see a diamond with exclamation point to the left. If you click on that, excel should offer you options like change the text to numbers. If you highlight all the cells, you can change them all at once.

OK, I see the diamond with the exclamation point. One problem is that you can't use the arrow key on a group of highlighted cells; it will only move to one cell at a time. Another is that, even after I click the one cell to change it to a number, it still acts funny. It's faster to highlight all of them and change them to numbers, which also requires changing 2 decimals to 0. I tried accounting mode, which has a default of 0 decimals, but apparently you can't change a whole group of cells to accounting, only one cell at a time. All of this could be avoided if the AFF staff would just fix the problem!!!

The "numbers stored as text" issue in Excel is easily fixed by typing 1 in a different cell (say J1). Copy J1, highlight the entire range with "numbers stored as text" and right click -> Paste Special -> Multiply. This coerces the text into numbers whereas Excel formatting often still leaves the original values stored in a non-number format.

I just tried this and it indeed does work. However, it doesn't fix the problem of multiple little columns - the useless C and D columns in Excel - and if you try this trick before fixing that, you lose the data in the first geographic area you downloaded the data for.

Why, oh why, did the designers of AFF stick us with these problems?

I am curious about the data loss. What are the tables and geography level where the problem occurs? Generally, all identifier fields should be left as text because of the use of leading zeroes. And full disclosure: I don't use AFF very much. I usually download the entire set of text files (for the area I'm working), load the tables of interest into the Excel table sequence shells, import and run all of my queries in Access and export back to Excel.