October 5

Manipulating PUMS data in Excel

After processing PUMS records in Stata, we wrote out a spreadsheet in .csv format. In today's lesson we used that spreadsheet to create interactive graphs that illustrate the PUMS summaries.


Array Formula : describing a formula for a rectangle of cells rather than for a single cell
* set of values for entire rectangle of cells( Indirect, Offset, Index, +controls)


Indirect ( tells excel to get the code from a particular cell)
Randbetween ( stretch across cells to create a random number scheme across cells)
Offset ( as you start to type it will tell you the information needed, then hit CTRL+SHIFT+ENTER)
*example: (grid,4,1,2,2)
INDEX ( in a single cell or the cell the information is in that you want)
*example: using index as an array ( grid, cell #, cell #) <—— this changes the chunk of data you are grabbing

Example Done in Class:

grid of data to grab from, make a black for each state

-select all of Arizona data cells
- right click
- name a range
- highlight entire block you want data to show up in

Insert a Graph
- select cells
- as you change the number in cell block, the graph will change as well
- right click format access

Add Developer Toolbar
- add scroll
- insert
- scroll bar
- format scroll bar to be able to sort through charts