Creating a Population Pyramid



Creating an Age-Sex Population Pyramid

Using Microsoft Excel v 5.0 Software

1. Obtain data on age distribution of the population, by sex.

2. Open Microsoft Excel (version 5.0); enter the raw data (number of individuals in each age group) in columns ‘A’ for males and ‘B’ for females, respectively; and enter the age groupings (e.g., 15 to19 years) in the appropriate rows in column ‘E’.

For purposes of consistency with the examples below, put the youngest male age group in cell A5 and the youngest female age group in cell B5.

3. For the data on males, put a minus sign before each cell entry (e.g., change 105960 to -105960).

4. If it is not totalled already, in the cell immediately below the last data entry (say, cell A24) for males, total the male column using the formula:

=sum(A5:A23)

where: A5 is the cell in which the data starts for that sex

: represents the phrase “through to the end of”

A23is the cell in which the data ends for that sex

NOTE THAT THERE ARE NO SPACES IN THIS FORMULA.

5. If it is not totalled already, in the cell immediately below the last data entry (say, cell B24) for females, total the female column using the corresponding cells for females in the formula in step 4 above.

6 a. In order to permit comparability with the pyramids for other populations of quite different sizes, use columns C and D to convert each cell entry to a percentage of the total population (NOT the total for only that sex).

For example: to have the computer calculate the percent which the persons (males) in cell ‘A5’ constitute of the total population, go to cell C5 and key in

=A5/((A$24*-1)+B$24)*100

where: = tells the computer that the cell entry is a formula

A5 is the cell for which the percentage is requested

/ represents mathematical division

A24 is the cell in which the total number of males is located

$ tells the computer to NOT automatically increase the number that follows it

* represents mathematical multiplication

-1 is a multiplier used to eliminate the negative sign for male calculations

B24 is the cell in which the total number of females is located.

Then, highlight cell C5 down through C24 by clicking on cell C5 and dragging the mouse down to cell C24 and releasing. Key in CONTROL D (that is, hold down the “CTRL key (or, on Mac, the apple key) while hitting the letter ‘d’) to fill in the cells from C6 to C24, inclusive.

b. Do the corresponding operations to convert column B (Females) data to percentages in column D.

7. Capture the data for the graphing program in Excel by:

highlighting the percentage data for all the male and female cells except the totals, by clicking the mouse, holding the clicker down, and dragging the mouse over the stipulated cells.

8. Drop Excel’s ‘INSERT’ menu by clicking on it. Drag the mouse down to ‘Chart’ and click on it.

9. Select features as follows:

(i) Click on the chart type called ‘Bar’ and then, keeping the default form of bar graph, click on the ‘Next’ button.

(ii) If the cells shown in the prompt beside “Data Range” are correct (Ignore dollar signs), click on the ‘Next’ button. If they are not correct, correct them before clicking on ‘Next’.

(iii) Insert a title for your population pyramid in the box under ‘Chart Title’ and under “Value (Y) Axis” key in the word “Percent” (without quotation marks).

(iv) Click on the ‘Axes’ tab and de-select ‘Categories (X) Axis’. If the ‘Values (Y) Axis’ is not selected, select it.

(v) Click on the ‘Gridlines’ tab and de-select ‘Value (Y) Axis: Major Gridlines’. No options should be selected for gridlines on either axis.

(vi) Click on the ‘Legend’ tab and de-select ‘Show Legend’.

(vii) Click the ‘Next’ button.

(viii) Select “As New Sheet” and then click the ‘Finish’ button.

10. Save and give a new file name by pulling down the file menu and releasing on Save As. Then key in a file name for the pyramid (It is helpful to end the filename with “_Graph”) and click the ‘Okay’ button.

Save periodically during the steps below, by holding down the ‘CTRL’ key and hitting the ‘s’ key.

11. Edit the graph by double clicking on it.

(i) Remove the vertical spaces between bars by clicking on some part of the male side of the pyramid. Then go to the ‘ Format’ menu, pull it down, and release on ‘Selected Data Series’. On the top right of the box that pops up, click on the ‘Options’ tab. Then set ‘Overlap’ to100 and ‘Gap Width’ to 0. Then click the ‘Okay’ button.

(ii) Insert sex labels by clicking in graph to right of female bars, and then going to the ‘fx’ formula box near the top of the Excel screen and keying in the word “Female”. The word will appear in a box somewhere in the pyramid. Click on the box and drag it to near the top right corner. Repeat for males. Note that size of box is adjustable by clicking on its border or corner, size of typeface is adjustable, and location of box is movable by clicking on it and dragging it.

(iii) Change the negative values for males along the horizontal axis at bottom to positive values by removing all labels on that axis and replacing them with positive values. To do this, go to the ‘Chart’ toolbar that is floating somewhere in your graphing area. Pull down the menu on the left side of that toolbar and select and release on ‘Value Axis’. Slide the mouse arrow just to the right of that menu and leave it there on that icon until the label “Format Axis” appears right under the mouse arrow. Click on that icon. Click on the ‘Number’ tab and choose 2 in the menu to the right of ‘Decimal Places’. Then click on the ‘Patterns’ tab and under ‘Tick Mark Labels’, click on ‘None’. Now click the ‘Okay’ button

Then in the ‘fx’ text bar near the top of the Excel screen, key in an appropriate label to place on the horizontal axis under the first hache mark to the left of the zero point on that axis. Let’s say that label should be 1.00. After keying that number into the text bar near the top of the screen, click the little green check mark to the left of the ‘fx’ text bar and the number that you typed will appear in a small box somewhere near the middle of the graph. Click on a corner of that box and with the mouse still depressed, drag that corner out to enlarge the box slightly. If the number showing in that box is 1, instead of the 1.00 that you entered, highlight the numeral 1 and key in 1.00 again. Then click on an edge (not a corner) of the box containing the 1.00 and drag that box to a location under the appropriate hache mark. Release the mouse and reposition as necessary. Click anywhere in the background area right beside one of the layers of the pyramid, but not in the pyramid.

Repeat the steps in this paragraph to label the other points on that horizontal axis.

Note that the label “Percent” at the bottom of the population pyramid can be dragged lower to make room for the value labels.

(v) Using the method shown in the second paragraph of 11(iii) above, label at least the bars. Ordinarily, the bars should represent age groupings of identical size (five years). If this is not the case in your pyramid, label any non-conforming age grouping.

12. For purposes of this assignment, select black and white colours. Do this by clicking once on a female age grouping bar in the pyramid and then pulling down the ‘Format’ menu and releasing on ‘Selected Data Series’. Under ‘Area’, click on the white colour in the colour pallet, and then click the ‘Okay’ button.

Repeat for men.

13. Your pyramid can now be copied into a Microsoft Word document. Do this by clicking outside the hache marks around the pyramid in Excel to make the hache marks disappear. Then click once on the border of the remaining pyramid box. Then pull down the ‘Edit’ menu and release on ‘Copy’. Next, open Microsoft Word, pull down the ‘Edit’ menu, and release on ‘Paste’.

file ref.: 405CreatePopPyrInExcel

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download