Installing an Autoformat for a Population Pyramid in MS Excel
Copy the files above "Pyramid Blank" and "New Pyramid" to a disk or to the desktop of your computer.
- Open the "Pyrblank" file in your own copy of Excel. Then select "Save As" from the File menu. Under "Save as Type," select "Template."
This will add the pyramid spreadsheet template to your copy of Excel.
- Open the saved "New Pyramid" in your own copy of Excel.
- select Chart type
- select "Custom types"
- select "User defined"
- select "Add" then type a "format name": population pyramid and click ok
This will add the custom format for a pyramid to the list of options in your program.
Constructing a Population Pyramid Using Excel
Follow the steps below carefully and accurately to convey age-sex data into a five-year cohort population pyramid.
Click on the "Start" button (lower left). From "Programs,"
- Open Excel. Under File, select "New." Open the template "Pyramid Blank."
- In Col. E, cells2-17*, type the population for each male cohort.
- In Col. F, cells 2-17*, type the population for each female cohort.
- In Col. B, cell 24, type the total population for the country.
- Select Col. B, cell 2. Then click on the Formula Bar just above the column letters and type: =E2/$B$24*-100
Press Enter. Select Col. B, cell 2 again; then point to the small black box in the lower right corner of cell B2; click and drag down through cell B19. Release. [Note: All percents are negative order to make the bars representing the male cohorts to appear to the left of the center axis).
- Repeat step 5 for Col. C, using the formula: =F2/$B$24*100
- Highlight Col. A, B, and C, cells 1-19.
- Under Insert, select "Chart." Select the tab "Custom Types" and then the option "User-defined." From the User-defined options, select "New Pyramid." At the on-screen prompt, click on "Next" until you reach "Step 4." Select the option "As a new sheet," then click "Finish."
- Use the "text box" tool (on the tool bar at the bottom of the page) to add an appropriate title, include "where, what, and when." Also use the text box tool to add a source note in the lower left corner below the pyramid. Save your work; then print.