Creating a Population Pyramid with an Excel Spreadsheet

Once you have the necessary data, these instructions will guide you through the steps of creating a population pyramid using Excel. Open an Excel Spreadsheet. Follow the instructions EXACTLY as they are given. DO NOTskip a step.

  1. In cell A1 type the name of your country for your Pop. Pyramid (ex. Passeland…)
  2. In cell B1 type “%Male pop.”
  3. In cell C1 type “% Female pop.”
  4. In cell D1 type “Male pop.”
  5. In cell E1 type “Female pop.”

(You may need to resize the width of your columns to accommodate your headings. Just click and drag the sides of the columns to do so.)

  1. If you would like to dress up your spreadsheet, you can put a line across the bottom of the first row of cells by highlighting the top row (cells A1-E1), then click the button towards the top that looks like a grid: You can also click the arrow next to the picture to add different borders in other spaces as well.

Starting in cell A-2, type in the age ranges listed under each country’s name. (In cell A2, type 0-4. In cell A3, type 5-9. Continueto do this down the column, typing the next age range until you get to the last one.)

  1. You will notice that Excel automatically changes the cells with “5-9” and “10-14” age ranges into a date instead. (9-May and 14-October) You need to fix this. To do so, highlight the two cells with this problem. Right click the highlighted cells, and select the Format Cells option. This will pop up a small window. On the left side you will see a list that says Categories. Click on the category that says Text, then click OK. Now when you go back to the sheet, there are some weird numbers in those cells. Just delete, and re-type the age ranges that belong there, and now they won’t change into dates.

SAVE!

  1. In cell D2, enter the data from your country for the male population of the 0-4 age range. Continue down the column entering the appropriate data. IF the sheet given to you has a Total Male Population number, DO NOTenter it. We will have the computer calculate it for you, so you can check to make sure you entered all of the correct numbers.
  1. Beginning in cell E2, do the same, but for the female population data instead.

SAVE!

  1. In cell D19, we want to create a formula that will tell Excel to add up the numbers in the above column. In the top right corner, there is a small button called the autosum button. It looks like this: Click inside cell D19, and then press this button once. There will be some flashing lines around the column of numbers above D19. That means that all of those numbers will be added up, and displayed in cell D19. After you pressed the autosum button, just press enter.
  1. Do the exact same for cell E19. Just click inside the cell, press the autosum button, make sure the flashing lines are around the correct column, and press enter. A lot easier than using a calculator isn’t it? 
  1. If the sheet with the data for Passeland has totals for each gender, use them to check your Excel document to see if they are correct. There might not be totals on the Passeland data sheet, but if they are there, use them! If your numbers don’t match, it means that you entered a wrong number in a cell. Just go back and double-check each cell to correct the error.

SAVE!

  1. Now you need to create a formula that calculates the total population. In cell E20, type =D19+E19. This is telling Excel to add those two cells, and display the total in cell E20.
  2. Again, if you would like to dress up your spreadsheet a bit, you can add borders as you did before. One suggestion is to put a border all the way around the grand total in cell E20. Borders can help to organize the data on your spreadsheet.

Next, we need to create formulas that calculate percentages. These will be used to create your graph. We will start with the female population.

  1. In cell C2, type =E2/E20. This is telling Excel to use division, and divide the number in cell E2 by the number in E20. The number that you get should be a decimal number. (ex. 0.02235145)
  1. We can have the spreadsheet automatically express this number as a percentage instead of just a decimal number, which is what you need. Just click on cell C2 to highlight it, and then press the percentage button. It’s located in the center towards the top in the toolbar. Now the number in C2 should be a percentage. (Ex. 4%) To express the number to the nearest tenth of a percent, click on the increase decimal button. It’s near the percentage button that you previously used. If done correctly you percentage should have a decimal point and a number in the tenths spot. (Ex. 6.2%)

We want to use this same formula and format for the entire column. In order to do that, we need to lock the formula so it uses E20 every time.

  1. To lock that part of the formula, click on cell C2. When you do that, the area above the top of the C column (called the text line) will say: =E2/E20 You need to edit this formula so it looks like this: =E2/$E$20 Just insert a dollar sign ($) on both sides of the E in E20. The dollar sign tells the computer to lock onto certain parts of the formula. Once you have changed it to lock on, just hit enter.
  1. Now this is where those complicated formulas come in handy. Click on cell C2. On the bottom right corner of the cell, you will see a square. If you move your cursor onto it, your cursor becomes a small plus sign. It looks just like this: + Click and drag the corner down to the last row of data in the C column, (it should be down to C17), and then click anywhere on the screen. Notice that Excel just entered all of the percentages for the whole column. That just saved you 20 minutes of using a calculator!

SAVE!

  1. You need to do the same formula and formatting for the males as you did for the females. BUT there is a very important step that you need to pay close attention to. (Step 21)
  1. When we use this data to have the computer make a bar graph, it will show the male and female bars as different colors, which is fine. It will also have all of the bars for the data expressed as positive numbers, so the bars will come from the graph going the same direction.
  2. Since we are making a Population Pyramid, we need one set of bars to go one way, and the other set to go the opposite way. This sounds confusing, but it will make more sense when you actually create the chart. In order to make the bars for the males move in the opposite direction, the formula needs to make the percentages negative. To do this, click inside cell B2, and type =-(D2/$E$20) Then press enter. When you do that, the percentages will be calculated and come out negative.

Click the percentage button at the top of the screen, and the increase decimal buttons for cell B2 like you used them before. Next, click on cell B2 again, then click and drag as you did before, down to the last row of data in the column. You should now have all of the percentages for the males, but they will be expressed as negative just as you wanted. It’s like magic!

SAVE!

  1. Now it’s time to create the bar graph. First you need to highlight your age range and percentage columns with the cursor. (Just select cells A1 – C17)
  1. Now click Insert tab towards the top of the window. There will be pictures in the center of the new toolbar with different types of graphs. Click on bar, then under the 2-D category, select the graph on the left side. It should be called Clustered Bar.
  1. After you selected Clustered Bar, a graph will pop up automatically with your data. Right-click on the very outside edge of the graph box, and select Move Chart. The next window will ask where you want to move the chart to. Select the bubble next to New Sheet. Press OK.

SAVE!

  1. Now you are presented with a large graph on a brand new sheet. It’s time to edit it to make it look correct. First, select the Layout tab at the top of the window. Then on the top toolbar, press the Gridlines button. There will be a drop down menu. Go down to Primary Vertical Gridlines then to the right, select none.
  1. Now select the Legend button on the top toolbar. There will be a drop down menu. Select the none option.
  1. Next, move your cursor to the center of where your colored bars on the graph meet, and a small box will pop-up that says, “Vertical (Category) Axis”. Right click on that spot and select Format Axis. A new window will pop up, and there are three different things that you need to change. First, change the box next to Major Tick Mark Type to none. Second, change the box next to Minor Tick Mark Type to none. And last, change the box next to Axis Labels to low. Then press Close.
  1. Now Right-click on one of the colored bars on the graph. Select Format Data Series and a new window will pop up. In the Series Overlap box, drag the arrow to the right to set the overlap to 100%. Then, in the Gap Width box, drag the arrow to the left to set the gap width to 0. Notice how this changes your graph. It’s looking more like a pyramid every minute!
  1. Now for the last step. Move your cursor onto one of the numbers on the bottom of your graph. (One of the percentage numbers.) Now Right-click and select Format Axis. A new window will pop up. On the left side of the box, select Number. Now in the window, there is a list of categories. Select Custom (it’s at the bottom of the list). Now underneath the Category box, there’s a box called Format Code. Delete what’s in the box, and re-type it to look like this: 0.0%;0.0% Then press Add, and finally press Close.

SAVE!

At this point you can put some finishing touches on your graph, like adding a title, or labeling the Male and Female parts of the graph. To do this, use the Text Box button on the top toolbar. You can also experiment with changing colors or sizes of text. Experiment!

SAVE & PRINT! REPEAT FOR EACH OF THE COUNTRIES (Passeland, Bergsylvania 7 Viktoria)