How to Create Age Distribution Graph in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Age distribution graphs are very powerful tools for visualizing the ages of different genders, ethnicities, and groups of different ages. It not only gives clarity in representing the ages but also makes it look appealing to the viewers. Excel has some amazing features to make a graph to represent age distribution. In this article, we will show how to create an age distribution graph in Excel.


How to Create Age Distribution Graph in Excel: 2 Easy Ways

In this article, we will show 2 ways to create and represent age distribution graphs in Excel. Firstly, we will create a graph of age distribution that has no space between the two existing groups and the Y-axis is to the left. In the second method, we will create a graph that has its Y-axis value in the middle.


1. Graph Without Gap in Between

In this instance, we will have the Y-axis value to the left of the graph. To do that, first, we will sum all the people of all ages of a particular group using the SUM function. Then, we will calculate the percentage of each group contributing to the total. Finally, we will plot the graph. Follow the steps below to do so.

Steps:

  • Firstly, select the C11 cell and write the following formula,
=SUM(C5:C10)
  • Then, hit Enter.

summing number of people to create an age distribution graph in excel

  • Consequently, we will have the summation of males of different ages.
  • Do the same for the Female group.

  • Then, select the E5 cell and type the following formula,
=-(C5/$C$11)
  • Hit Enter.

calculating percentages to create an age distribution graph in excel

The formula has a minus sign in front of it. It is because we want to plot this data to the left of the chart.

  • As a result, we will have the percentage of males belonging to that age group.
  • Lower the cursor down to the last data cell to Autofill.

  • After that, go to the Home tab.
  • From there, select the % sign under the Number option.

converting proportions to percentages to create an age distribution graph in excel

  • As a result, the data will be presented in a percentage format.
  • Meanwhile, click on the F5 cell and write the formula below,
=D5/$D$11
  • Then, hit Enter.

  • As a result, we will have the proportion of females contributing to the total from that age group.
  • Move the cursor down to autofill the rest of the cells.

  • Convert the proportion of females into percentages in the same way as males.
  • Afterward, select the Age Group, Male%, and Female% columns.
  • After that, select the Insert tab.
  • Then, choose the Insert Column or Bar Chart option.
  • Finally, from the drop-down, select the Stacked Bar Chart.

inserting stacked column chart to create an age distribution graph in excel

  • Consequently, we will have our chart for our data.

  • Firstly, select the labels on the graph.
  • Secondly, choose the Series Options tab.
  • Thirdly, choose Labels.
  • Finally, from the drop-down of the Label Position option select Low.
  • As a result, the data labels will be to the left.

moving the y axis value to the left to create an age distribution graph in excel

  • Select the X-axis label.
  • Then, go to the Axis Options option.
  • Click the Number option.
  • Then, in the box under the Format Code option write the following,
#,##0%;[Black]#,##0%
  • Finally, click Add.
  • Consequently, all the negative values in the X-axis will be gone.

removing negative x axis values to create an age distribution graph in excel

  • Now, click on the bars of the Female group.
  • Then, select the plus sign to the right of the graph.
  • Finally, check the Data Labels box.
  • As a result, we will have labels on the data. Now, we need to reposition it.

  • So, select the data labels.
  • Go to the Label Options tab.
  • Then, mark the oval beside Inside End under the Label Position option.
  • As a result, the labels will be repositioned to the far right of the graph.

repositioning data labels to create an age distribution graph in excel

  • Now, repeat the process for the Male% column.
  • The only difference is, in this case, check the Inside Base oval instead of the Inside End oval.

  • Finally, we can present our Age Distribution graph.

creating an age distribution graph in excel


2. Graph with Gap in Between

In the previous method, we positioned our Y-axis value on the left. In this method, we will set it in the center. Also, in this method too we will have to sum the number of people in a particular group and then calculate the proportion of people in each age group. Those steps will be the same as the previous method. So, here will start with inserting a graph. Another thing is, in this method, we will plot another column of data and the name of the column is Gap. This will allow us to have space between the two groups in the plot and insert the Y-axis value in the center.

Steps:

  • Firstly, select the entire data.
  • Secondly, choose the Insert tab.
  • Thirdly, click on the Insert Column or Bar Chart option.
  • Finally, from the available options, select the Stacked Bar chart.

adding stacked column chart to create an age distribution graph in excel

  • Consequently, we will have a graph with a Gap data in between the main data.

  • Select the Gap data.
  • Then, go to Series Options.
  • From the drop-down of Fill, select the No Fill command.
  • Finally, select No Line under the options of Border.

removing gap values to create an age distribution graph in excel

  • Then, select the plus sign on the right upon selecting the data in the gap.
  • Check the Data Labels option.
  • As a result, we will have 0.1 in each level which is the value of each cell in the Gap column.

  • To change that, go to the Label Options tab.
  • Then, first, uncheck the Value checkbox.
  • After that, check the Category Name box.
  • Consequently, we will have the Y-axis value in the middle.

positioning y axis value in the center to create an age distribution graph in excel

  • Now, select the data labels on the Male% data and delete them.

  • Then, select the X-axis values.
  • Go to the Axis Options.
  • Select the Number option.
  • Finally, under the box of the Format Code option write the following,
#,##0%;[Black]#,##0%
  • Click on the Add command.
  • As a result, the X-axis will have no negative value.

  • Then, select the Gap axis label and delete it.

  • Then, follow the same steps as the previous method to add labels to the data.
  • Finally, we will have our Age Distribution graph with Y-axis values in the middle.


Download Practice Workbook

You can download the practice book here.


Conclusion

In this article, we have discussed 2 easy ways to create an age distribution graph in Excel. In these examples, we did not format the graph in simple ways. Readers could decorate the graph according to their tastes. However, the basic steps will be the same as in this article.


Related Articles


<< Go Back to Excel Demographic Data | Excel for Statistics | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Adnan Masruf
Adnan Masruf

Adnan Masruf, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. His deep passion for research and innovation seamlessly aligns with his dedication to Excel. In this capacity, Masruf not only skillfully addresses challenging issues but also exhibits enthusiasm and expertise in gracefully navigating intricate situations, underscoring his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo