Create Population Pyramids Using LibreOffice Calc
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):
- Under "Detailed Indicators" select "Population by five-year age group and sex",
- Under "Select country/region" select "World" ,
- Under "Select variant" select "medium"
- Under "Select: Start Year" select "2010"
- Under "Select: End Year" select "2010"
- Click "Download as CSV file"
- In the popup window select "Open With" and select "LibreOffice Calc". (You can also download the file as a CSV file and load it later into LibreOffice Calc.)
- You will be asked to select the parameters for opening the file. Select "Separated by" and check the "Comma" and "Space" boxes and select quotation marks " in the "Text Delimiter" option. You can also check the "Quoted fields as text" in the "Other options" then click "OK".
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:
- Click on the top left hand cell of the data--this should be the column title "Sex". Without releasing the click drag the cursor until all the data is included then release the click. (The selected text should have a light blue overlay.)
- Click on "Data" in the main menu and select "Sort".
- For "Sort Key 1" select "Sex" and "Ascending"
- For "Sort Key 2" select "Index" and "Ascending". Then click "OK"
The next step is to put the data into a format that makes it easier to create the chart.
- At the top of the page (Start in Column F) create three column titles, "Age","Male", and "Female""
- Select the data on age groups from the of the data in Column B. (Click on cell B2 then drag the cursor down to B22). click on "Edit" and "Select Copy" (the selected data should be surrounded by a box with a dash-line border). Click on the cell just below the Age label then click on "Edit" and select "Paste".
- From the "Values" column copy and paste the data for Males and Females to their respective columns under the new headings.
- Make the data in each of the cells in the Male column negative by inserting a "-" before it (making the number negative creates the pyramid shape on the graph).
The data in its new table should look like the image below.
Creating the Pyramid
We will create a bar chart for the pyramid.
- Click on "Insert" in the menu, then click on "Object", click on "Chart" in the drop down menu, then select "Bar", then select "Finish".
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).
- Select "Format" from the main menu,then select "Axis", "XAxis" (vertical axis),select "Positioning", select "Labels", within the selection box select "Place Labels" and choose "Outside Start" from the drop down list then click "OK". The age group labels a should have moved to the left of the graph.
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.
- Make sure the graph is selected (double click).
- Select "Format", then "Axis", then "YAxis". Select "Numbers"". If "Source Format" is checked, un-check it. Select "User Defined", enter "#,##0;#,##0" into "Format Code". Click "OK"
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.
- Select "Format", then "Format Selection", set "Spacing" to "0%", set "Overlap" to "100%", Click "OK" >
- To change the color of data click on the side of the pyramid you want to change.
- Click on "Format", "Format Selection" and then "Area". Select the color you want then select "OK". Repeat for the other data series if you wish.
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).