The left below shows a red-green diverging color palette for values from -858 to 72,986.Use cell conditions in Numbers on Mac. We have given it General.How to Build a Population Pyramid in Excel: Step-by-Step GuideWhen there are both negative and positive values for the field. The fourth and the last section denotes the formatting of the text (Characters/words). The third part denotes the formatting of zero (0) in the cell. It should in the bracket and in red color. In the present case, we have given formatting to a negative number as Red (,) i.e.This guide is based on using Microsoft Excel 2016. To do this, select the cell or range of cells to be formatted, then if using Microsoft Excel 2003 or earlier, click Format Cells and ensure the Number tab is uppermost. To change the look of a cell based on its cell value, create a conditional highlighting rule.Making a population pyramid, or age-sex distribution graph, in Excel has never been easier than with this step-by-step guide that takes you all the way from gathering the needed demographic data to the finished product – a graphically-accurate (and beautiful!) population pyramid.Rather than having negative numbers with a minus sign in front of them, some people prefer to put negative numbers in brackets. For example, you can make cells turn red if they contain a negative number.
This is what the selection column looks like:5. Under ‘Report’ click ‘Population by Age’ and then choose ‘Five Year Age Groups.’4. In the column on the left, select the country of your choice under ‘Select by Country/Area.’ I chose Guatemala for this example.3. Census Bureau’s International Database.2. Get the Population Data for your Country1. The data will be entered into a new excel document.7. Click the icon and select Excel as your Download Format. In the upper left you’ll see a download icon (circled in red above). For the cells with ages ‘5-9′ and ’10-14’, you may need to change the cell category to Text. Delete the word ‘years’ from all of the age range cells in column A (cells A3 through A23). Type in new name.) Add a new row 1 above the headings and in cell A1 write the country name.2. (Rename by right clicking on ‘Sheet1’ and selecting Rename so the text ‘Sheet1’ is highlighted. Rename the excel tab Data. Organize and Clean your Population Data1. You’ll be left with four columns: Age Group Both Sexes Population Male Population Female Population.4. Delete columns B, C, and D. You may need to re-type the ages 5-9 and 10-14.3. In the ‘Number’ tab, select ‘Text’ from the list on the left and click OK. To calculate the total for the Male Population, select cell C3 and type the equation: =SUM(C4:C24) Hit enter on your keyboard. To calculate the total for Both Sexes, select cell B3 and type the following equation: =SUM(B4:B24) Hit enter on your keyboard. Calculate the total for all three population groups. Write ‘Total’ in cell A3.5. Under the Number tab, select ‘Percentage’ and change the Decimal Places to 1.8. Highlight column D, right click, and select Format Cells.Ii. For both of these new columns, format the cells to represent data as a percentage.I. Insert a new column between Male Population and Female Population and label it ‘Male Percentage (%).’ Label the column to the right of Female Population column as ‘Female Percentage (%).’7. For every new age/sex cohort, you need to enter the appropriate data as the numerator, but always the total population of both sexes as the denominator. You cannot simply click outside the cell.Iii. What you’ve done, is divided the males ages O-4 (cell C4) by the total population (cell B3).Note: After you’ve entered the equation, you must hit enter for the calculation to happen. Click on cell D4 and enter the equation =C4/B3 and hit Enter. We’ll start with finding the percentage of Males ages 0-4 (so we’re filling in cell D4).Ii. Be careful of copying and pasting the equation that the denominator does not change, but always remains B3 (the total population of both sexes).9. Repeat this process for every Female age cohort: cells F4 through F24.Vi. Enter the equation: =E4/B3. Then do the same for the Female age cohorts starting with females ages 0-4 in cell F4. Repeat this process for every Male age cohort: cells D4 through D24. Manually type (do not copy and paste) the number from column D “Male Percentage (%) into the corresponding rows in Column H.Iv. Under the Number tab, change the Category selection to ‘Number.’ In the ‘Decimal places’ field, enter 2. Highlight the column, right click and select Format Cells. But do NOT make the numbers negative.Now your data is ready! (Don’t forget to save your hard work!)1. In column I, retype the Female Percentage data as numbers using the same steps that you used for the Male Percentage. You cannot simply click outside of the cell, you must hit Enter.10. (Ex: 5.9% in column D would become -5.90 in column H.) To do this, add the negative symbol in front of the number and click Enter. In the Chart area, click on the Column/Bar Chart dropdown and select Clustered Bar under the heading 2-D Bar.3. Click Insert on the main toolbar. This is where you will actually build your population pyramid.2. And in the Series values field, delete all text so the field is empty.6. In the Edit Series pop-up box, fill in the Series name with MALES. In the Select Data Source pop-up box, click on Add.5. Delete the text out of the Series value field.Iv. Write FEMALES in the Series name field.Iii. Now back in the Select Data Source pop-up box, repeat the process for Females by:Ii. The graph should look like this:9. Click OK in the Edit Series pop-up box. Highlight the data in the Male % column (H4 through H24).8. ![]() Double click on any one of the data bars on the graph (blue or orange) to bring up the Format Data Series control column. And your graph should look like this:15. Click on Labels and change the Label Position to ‘Low.’This will move the y-axis labels from the center of the graph to the left side. Double click on the center axis line (between the blue and orange bars) to bring up the Format Axis control column.14. Best antivirus for mac trend microDouble click on the other side of your graph that doesn’t have the outline, change the Border to ‘Solid Line’ and the Color to black.19. While still in the Format Data Series control column, click on the Fill & Line icon and change to Border to ‘Solid Line.’ Also change the Color to black to add an outline to one half of your graph.18. Change the Series Overlap to 100% and the Gap Width to 0%.And now your graph should look like this:17. The default is -8.0/8.0 which works fine for Guatemala. Bounds – This is the maximum number your x-axis will go up to. Click on the far right icon for Axis Options.I. Click anywhere within the chart area to bring up three control buttons to the upper right of the graph. Add a title to you pyramid and labels for the x- and y-axis. Still in the Format Axis control column and in the Axis Options section, now click on Number and make the following adjustments:22. Vertical axis crosses – select Axis value and leave as 0.021. Label the x-axis Percentage of Population. Double click on each of those to highlight and relabel.Iv. Check the box for Axis Title and the text “Axis Title” will appear as labels for the x- and y-axis. Click on Fill and choose a new color option. Click on the far left icon for Fill & Border. If you’d like to change the colors of your graph, double click on either side to pull up the Format Data Series control column. Type in the name of the country and the data’s year.24. Double click on “Chart Title” to highlight and retitle.Iii. Click on the top + button, and check the box for Chart Title so the text “Chart Title” appears at the top of your graph.Ii.
0 Comments
Leave a Reply. |
AuthorNicole ArchivesCategories |