How to Use MS Excel to Make an Age

1. Create six column headers in row one, starting in column A. From left to right, the column headers should be “Male,” “Female,” “Total,” “Age Range,” “% Male” and “% Female.”
2. Input the age ranges that you have the information for under the “Age Range” column. If you are using a hyphen to denote the age range, like “11-14,” right-click the cells and choose “Change Format.” Then change the format to “Text.” Otherwise, Excel may automatically change your ranges to dates.
3. Place the population numbers for males and females that correspond to the age range in the row, under the “Male” and “Female” columns.
4. Input this formula: “=SUM(A2:B2)” into the first cell under the “Total” column. Move your mouse over the bottom right corner of the cell; it should turn into a plus sign. Click and drag the mouse down the column to the last row. Release the mouse button to copy the formula to every cell in the “Total” column. Select cell G1 and type in this formula: “=SUM(C:C)”. This will give you the total population for the entire graph.
5. Highlight all of the cells in columns E and F. Click on the “Home” table at the top of the screen and find the “Number” section. There will be a drop-down box that should read “General.” Change “General to “Percentage.”
6. Input the following formula into cell E2: “=0-(A2/$G$1)”. This will give you a percentage of the overall population that are males in that age group. It will be represented by a negative number for the sake of the graph you are making. Input this formula into cell F2: “=B2/$G$1”. This formula will give you the percentage for females, only as a positive number. Highlight both E2 and F2, then click on the bottom right corner and drag the formula down to fill every row in the table.
7. Highlight rows D, E and F. Click on the “Insert” tab and find the “Charts” area. Click on “Bar” and, under the “2D Bar” section of the drop-down menu, choose “Clustered Bar.” It should be the first choice on the left. A crude graph will now display on the screen.
8. Move your mouse over the age ranges listed in the middle of the graph. The words “Vertical (Category) Axis” will appear under the mouse when you are in the right place. Right-click and choose “Format Axis.” Click on the second radial button in the new window, labeled “Specify Interval Unit” but leave this set to “1.” Click on the drop-down menu next to “Axis Labels” further down the window and choose “Low.” This will move the age ranges to the left side of the graph.
9. Right-click on any of the colored graph lines and choose “Format Data Series.” Move the “Series Overlap” slider all the way to the right so it reads “100 %.” Move the “Gap Width” slider all the way to the left so it reads “0 %.” Now click 'Close' and your age-sex graph will be complete. You can modify the style of the graph, as far as headers and labels go, just like any other Excel graph.