The raw data from the census in Excel.

Following up on Population Pyramids I which shows the story-telling power of population pyramids, this post explains how to create them.

Negotiating the labrynth of the U.S. Census Beureau can be a challenge so this link will take you right to the 2008 ACS 1-year Data.  Under the 2008 tab in the blue area chose Subject Tables from the list at right.  Select the desired geographic type from the drop down menu.  (A city is called a “place.”) [note:  the 1-year data is more selective and focused on larger communities.  If your place or county is not listed go back a page and choose the 3-year data].  At the top of the next page, ignoring the drop down menu, under Age and Sex chose Age and Sex next to SO101.

Select everything from “Total Population” down to “85 years and over,” including all columns to the right.  Paste this into an Excel spreadsheet.  The age breakdown is done by a percentage of the total by sex, so you will want to replace those percentages with numbers.  The easiest thing is to put the equation in the Margin of Error column since you won’t be using that.  Let’s assume you know how to do that. 

To build the population pyramid you will need just three columns: age ranges, males, and females.  The pyramid is built on a zero axis.  In order to get males and females on opposite sides of the zero, one of the sets of numbers must be negative.  These examples all use females on the left of zero, so let’s continue doing so here.  You could add a minus sign to each of the values but that is time consuming, especially if you will be doing several as part of a peer comparison.  The easiest thing to do is to move the female data one column to the right and in the now blank column use an equation to multiply the female numbers by negative one (i.e. =D3*-1 where D3 is the 85 and over women.  Drag down). 

Multiply one sex by negative 1

Select your three columns (ages, positive male numbers, negative female numbers) and go to the graphing function.  Choose a bar chart (the first one under 2D in 2007).  Accept  all the defaults and finish.  In the 2007 version, select the bars on either side of zero  (click one bar and all should select) right click and select Format Data Series.  Set Series Overlap to 100% and Gap Width to 10%.  Then click on the age ranges in the chart and right click to select Format Axis.   Under Axis Options chose the Specify Interval Unit button but leave the default (1).   There are three drop down menus that should read from top to bottom None, None, and Low.

In earlier versions click on a bar, Format Data Series, Options overlap = 100 gap wideth =10.  Click on a number, under patterns tab on the righthand side chose none, none low.  Under scale  all ones.

Adjust the colors as you see fit and add a title.

Lastly, you may want to change the negatives in the horizontal axis label to positive.  Right click on it and choose “Format Axis.”  Go to Number and chose custom at the bottom of the menu.  In the “Format code” box type #,##0;#,##0.  Close.  Repeat.  Have fun, but be careful; building these pyramids can be addictive.