The sections below provide step-by-step instructions for creating population pyramids using LibreOffice Calc. The methods follow the same steps as creating pyramids in Excel; but the LibreOffice Calc interface is sufficiently different to require detailed instructions. The example given will be straightforward and will deal with making a basic population pyramid-- once you are familiar with the standard steps you can make variations. The instructions have been tested in LibreOffice Calc 4.1 running on Windows7 and Mint15 as well as LibreOffice Calc 3.5 running on Ubuntu 12.04.
Getting the data:
The sample pyramid will be for world population in 2010. For the data you will need the population by sex in five-year age cohorts. The data has been downloaded in comma-separated-value from a UN Population Division online database at http://esa.un.org/unpd/wpp/unpp/panel_indicators.htm. On the page select the following data parameters (Note: the data to enter is in boldface and bracketed by quotation marks--do not enter the quotation marks with the exception of those used in specifying parameters for downloading text):
Formatting the Data
The file which opens will be read only, so you should first save it as an .ODS file so that it can be edited. If your version of LibreOffice Calc behaves like mine the first column of the spreadsheet will take up the whole screen. You can right click in the column label "A" and select "Column Width" from the drop down menu and set 5 cm as the column width. This will bring all of the columns onto the one page. (Important note: The population data is in thousands). For this exercise you can delete the "Country", "Variable", "Variant" and "Year" variables--these are important information if you are building the pyramid for analysis and the data will go into the title of the graph, but for this exercise they just clutter up the page. (Right click within the column, select "Delete" from the drop down menu and "Delete column" from the pop-up if one appears. This will leave you with three columns "Sex", "Age" and "Value". Because the age data is text format it will not sort properly and you will have to add an extra field "Index" and insert numbers starting with 1 until the last row (if you have experience in spreadsheets you will know shortcuts, but if you don't it is safer just to enter the numbers one at a time.) The top of the table will then look like the image below.
It is now necessary to reorganize the data so that it can be used to
generate a chart. First, it is necessary to separate the data into
Male, Female and Total-- the Total data is not necessary to build the
pyramid. It is also important that the data be properly sorted by age.
Follow these steps:
The next step is to put the data into a format that makes it easier to create the chart.
The data in its new table should look like the image below.
Creating the Pyramid
We will create a bar chart for the pyramid.
Beside your data you will see the population pyramid for your data
which should look like the image below. This is the core of the
population period. We will continue to modify the chart but all of the
modifications are cosmetic to make it easier to read or more
attractive.
Modifying the Appearance of the Pyramid
There are many things that we can do to make the pyramid easier to understand. We can change the format of the negative numbers to make them positive, we can move the vertical axis to the left side of the graph, we can make the pyramid solid, we can add or change labels, titles, etc. Note, that we can only modify the graph when it is selected (double click within the graph--gray border visible) and the graphics menu is available.
Moving the vertical axis:We want to move the border to the left hand side of the graph. Make sure the graph is selected (has a gray border).
Converting negative numbers in the horizontal axis: We want to change the negative numbers in the horizontal axis to positive numbers and insert commas as thousands separators.
Change color and appearance of pyramid: We want to close the gaps between the bars in the graph as well as change the colors for each of the data series.
The graph will now look like the image below:
Other changes: This provides the basic population pyramid. There are many other adjustments you can make with the graph tools (double click the graph to access). You can access the tools either through the main menu or by right clicking on the element you want to change. Possible changes include moving the Legend Key, inserting titles, sub titles, axis titles, etc. The two principle tools on the menu are the Insert and Format options.
Saving the file: You should save your data regularly. To save select "File" in the menu then either "Save" or "Save As". Save As allows you to change the name of the file, select the directory as well as to select whether you are saving the file as an .ODF (LibreOffice Calc) or .XLS (Excel) or other type of file. Note if the graph itself is selected the software will try to save only the graph. Click on the graph to deselect (get rid of the gray border).