How do I create a population pyramid in excel



How do I create a population pyramid in excel 2010?

1. Go to the following website -

2. Under ‘Select Report’ select ‘Population by 5 year age groups’

3. Under ‘Select Year’ select the year you desire (ie: 2014)

4. Under ‘Select Country(ies)’ select the country you desire (ie: Canada)

5. Under ‘Aggregate Options’ select ‘Show individual Region/Country data only’

6. Click on the submit button

7. Click on “Download all tables as: Excel”, you want this to open in Microsoft Excel or any other spreadsheet program that you are working with (click on enable editing if working with Excel 2010)

8. Copy the contents of cells D4-D24 and paste them to M4-M24

9. On the spreadsheet click on an empty cell ( ie: N4)

10. In the formula bar type in the following: =((F4/$E$3)*100) – this will take the contents of cell F4 and divide it into the contents of cell E3 – the $ are important!

11. Right click on the contents of N4 and click copy

12. Left click on N5 and drag all the way down to N24 and then paste the above formula into those cells by right clicking and selecting paste

13. Now left click on O4 and enter the following formula: =-((G4/$E$3)*100) Note the negative sign at the beginning. It is important.

14. Copy that formula and paste it into O5-24 just like in step 9.

15. Highlight the new data (M4-O24)

16. Go to the pull down menu at the top labelled insert and select bar chart and select the first of three options under 2D

17. Select Chart Tools – Layout – Chart Title – Above Chart and name your chart (ie: Canada 2014)

18. Label the vertical and horizontal axis by using the Axis Titles menu beside the Chart Title menu

19. On the new chart right click on the numbers in the middle of the chart and select format axis. Change the axis labels to low.

20. Right click on the bars on the graph and Select Data and rename series 1 to male and Series 2 to female

21. Left click on the numbers on the bottom of the horizontal axis and changes the numbers to 0 decimal places and select the second option which will eliminate the negative sign

22. Right click on one of the bars on the chart and select format data series. Select options and set the overlap to 100 and the gap width to 0.

23. Right click on the graph anywhere outside the bars and move chart to a new sheet and rename it (ie: Canada 2014)

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

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

Google Online Preview   Download