UPDATE

I created this page yesterday but then came across another population pyramid entirely by accident and have amended this file as a result. What I have done is described in the Base Year with Starting Year section, below.

As I surf the web or browse a book or generally just do what I do, I often come across something that fires my imagination. This morning I came across a web site that carries a lot of population data. Let’s cut a long story short and say that I have spent the last two hours preparing a dynamic/interactive population pyramid for the UK population for the period 1922 to 2014. There are many other data sets available on the web site I refer to and I chose the UK because that is where I was born and brought up.

In the file you can download for this page you will see that I have downloaded other population data for you that you can experiment with: Australia, Germany, Luxembourg and Russia. I chose these additional countries almost at random and they don’t cover the same period a the UK’s statistics … I think that’s a good thing for you so that you can learn a few things about using someone else templates.

This new page is an update of a page I wrote originally for Excel 2007 and you will see that Microsoft have made the preparation of pyramid charts significantly easier over time. Take a look at the older page, here, because it’s about a pyramid style graph but not a population pyramid!

The Pyramid

Here is my population pyramid, on the pyramid tab, for 1922 and again for 2014, From the screenshots you should be able to see the scroll bar I have used that allows you to move from one year to the next: this goes year by year but you are free to change the control to make it go year by year, five years by five years or as you wish.

uk_pyramid_1922

uk_pyramid_2014

On the right of the same worksheet as the population pyramid I have carried out some analysis of the female: male split and the female to male ratio, including a graph. You can see if the UK is the same as of different from the other countries in respect of the pyramid and population proportions. This graphic relates only to 2014 and only to the UK.

uk_proportions_2014

Base Year with Starting Year

For this enhancement I programmed the population pyramid to have a base year pyramid AND another Starting year Pyramid in the same graph. It looks like this:

uk_pyramid_base_starting

Notice I have changed the title of the graph so that it tells the reader exactly which two years are being compared. There are TWO controls for this version and you can learn about them on the pyramid_base_start tab of the file, link at the bottom of this page.

As you change the base year and/or the starting year, you will see how things change. Here is a four period comparison with the same base year, 1922, in each case and different starting years: apart from graph one in which they are both the same.

uk_pyramid_base_starting_multi

Follow the Cohort

Something that has always interested me but something that I have never done is to follow a cohort through its life and that is what I have done here. I have set up a table that allows me to chose a starting year, 1922 or 1923 or 1952 or any year. I can also choose an age to start with, such as 0 years or 1 year or n years old. The table then reveals as much data as there is in the database to allow me to present this:

uk_cohort_1922_0

This is the 1922 cohort starting at age 0. The next data point is 1923 at age 1 and that’s followed by 1924 at year 2 … you can see what I am doing here can’t you. This is what demographers do and public health professionals … they want to know from cradle to grave what is happening to its people. No surprise, the number of people in this cohort falls over time as they die and are not replaced! Do notice the devastating impact that world war two had on the male population of the UK and to a limited extent the female population too.

For 1952, starting at age 0:

uk_cohort_1952_0

A completely different profile from the 1922 cohort. Can you see how there are relatively many more boys in this cohort in the early years. After about 1988 that changes and there are now more women than men: what happened from 1975 to 1988 to cause the number of men in this cohort to start to decline so rapidly?

Excel Technicalities

What you might be interested in here are the technicalities of the Excel aspects of this file. To that end, take a look at how I constructed the Scroll Bar.

Look at this formula =VLOOKUP($A6,population.xlsx!data,4,0)in cell C6 of the pyramid tab and this formula in cell D6 =VLOOKUP($A6,population.xlsx!data,5,0) of the same tab.

The formulas I have created for the cohort analysis include this one in cell K3 =IFERROR(ABS(VLOOKUP(I3&” “&J3,$D$1:$F$10324,2,0)),NA()). What does it do and how does it work? In the 1952 cohort table you will see that there is the error message #N/A that starts in row 66. I am happy with that even though my formula contains the IFERROR() function. So … what is the function of the #N/A error message in any cell in this table that contains it?

Tab UK_population (4) contains calculations and a graph to show the total UK population for the years 1922 to 2014 and I used the formula =SUMIF($B$2:$B$10324,”=”&H3,$F$2:$F$10324) starting in cell I3 of that worksheet . What is that formula doing and why? Could I have sorted that out in a different way?

Look at the other features I have built into that workbook and learn what I did and why. One more example: I have got a tab labelled UK_population and that is the original data download I did. There is a problem with the data but I didn’t correct it: why do you think that is?

Conclusions

There you are: not only some interesting data but a series of Excel and data analysis ideas to help you with your own data analysis work. I have not worked on any of the additional countries’data but if you do, I would love for you to share it with me and I will upload it here.

UPDATED Excel file … from here … population

Duncan Williamson

 

 

%d bloggers like this: