Creating a Population Pyramid in Microsoft Excel



Creating a Population Pyramid in Microsoft Excel

Each instruction is followed by an image which shows the step visually. Look at the image to help you if you are not sure what the written instruction is asking you to do.

1. Open Excel. Right click on the column heading in the first row only and select Format Cells.

[pic]

2. Make sure the Number tab has been selected. Select Text and click on OK

[pic]

3. In the first column type the ages as shown below.

[pic]

4. Notice there are no comma separators included in any of the values you type. When you type column B (male numbers) you must type a negative before the actual number. However, do not type a negative value when typing the values for column C.

5. Highlight all the numbers and headings as shown, and click on Insert, select Bar and then the second 2-D Bar.

6. The population pyramid will appear like the one shown below. At this stage it doesn’t look quite right… but don’t worry!

[pic]

7. Make sure you are clicked on the Layout in the Chart Tools tab. Select Axes, then Primary Horizontal Axis, then More Primary Horizontal Axis Options...

[pic]

7. Click on the button to the left of Axis Values near the bottom of the window and type in the minimum value in the graph you are making into the axis value box (even if it is a number that doesn’t make sense). Remember it must be a negative number! Click on Close.

[pic]

8. Now right click on one of the coloured bars on the left and select Format Data (it might be shown as format Data Series). Slide the Gap Width to “No Gap” and click on Close.

[pic]

9. We are now going to add gridlines, as these make any population pyramid easier to read. Click on Gridlines, Primary Horizontal Gridlines, Major Gridlines.

[pic]

10. Select Axis Titles, Primary Horizontal Axis Title and Title Below Axis. Type Total Population into the box that appears. Press Enter.

[pic]

 

 

11. Select Axis Titles, Primary Vertical Axis Title and Rotated Title. Type Age Range into the box that appears. Press Enter.

[pic]

12. The title of the graph will be now be created. Select Chart Title and Above Chart. Type in the box that appears a complete and appropriate title (where and what year, but do not type population pyramid as part of the title). You may change the font and font size for the main title and the axis titles by right clicking on them and reselecting font and size. When you have finished click Close.

[pic]

13. Now you are going to make sure the source of the data is included as part of the graph. Click on Insert, then Text Box. Move the cursor to the bottom right hand corner of the graph and click and drag until you have a suitable size text box.

Type Source: Statistics Canada into the box. Change the font size to 8 and make sure the text box is moved to the bottom right corner of the chart area

[pic]

16. Click on your chart to select it and then click on Layout under Chart Tools. Select Axes, Primary Vertical Axis and More Primary Vertical Axis Options.

[pic]

17. Now select the Specify interval unit and make sure it is set to 1; this means all the age ranges will show as they should. Click on Close

[pic]

19. One last change will help people to read your graph. Right click on one of the blocks of colour on the left, and select Format Data Series. Now select the Fill option. Then click on the button to the left of solid fill. Select a light blue colour. Select the Border Color option and then select a slightly darker blue colour. Click on Close.

20. Repeat step 19 for the right side of the graph, but on this side make sure fill is red, and the border colour is a slightly darker shade of red. When you are finished click on Close.

[pic]

 20. Look at the numbers on the horizontal axis; the males are showing as negatives! Select Axes, Primary Horizontal Axis and More Primary Horizontal Axis Options.

[pic]

 21. Now select Number. Make sure the Decimal place is set to zero and highlight the red numbers 1234. Then in the format code box, change the word [Red] to [black], click Add and press Close.

[pic]

Adapted from:

Mark Whitworth. “Creating a Population Pyramid in Microsoft Excel”. 2012. Marque My Words. Accessed: 30/11/2012.

-----------------------

Right click here and select Format Cells…

Make sure you type a‘-‘ for each value in column B

85+

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

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

Google Online Preview   Download