How to Create Age and Gender Chart in Excel (3 Examples) (2023)

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. However, Excel has some fantastic features to make a chart to represent age and gender distribution. In this article, we will show you how to create an age and gender chart in Excel.

Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.

3 Suitable Examples to Create Age and Gender Chart in Excel

Now we will show a total of 3 suitable examples to create and represent an age and gender chart 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 example, we will create a graph that has its Y-axis value in the middle. And in the third one, we will use a cool conditional formatting trick to create the age and gender chart in Excel.

For the purpose of demonstration, we have used the following sample dataset.

1. Using Stacked Bar Chart 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,

• Then, press Enter.

• Now do the same for the Female group. select the cell D11 and type the following formula.

• After that, press Enter.

• Then, select the cell E5 and type the following formula,

• Now press Enter.

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 Hometab.
• From there, select the % sign under the Numbergroup.

• As a result, the data will be presented in a percentage format.

• Meanwhile, click on cell F5 and write the formula below,

• Then press 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 Inserttab.
• Then, choose the Insert Column or Bar Chartoption.
• Finally, from the drop-down, select the Stacked Bar Chart.

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

• Then Firstly, select the labels on the graph.
• Secondly, choose the Series Optionstab.
• 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.

• Double-click the X-axislabel.
• Then, go to the Axis Optionsoption.
• Click the Numberoption.
• Then, in the box under the Format Code option write the following,

• Finally, click Add.

• Consequently, all the negative values in the X-axis will be gone.

• Now, click on the bars of the Femalegroup.
• Then, select the plus sign to the right of the graph.
• Finally, check the Data Labelsbox.

• As a result, we will have labels on the data. Now, we need to reposition it.

• So, select the datalevels.
• Go to the Label Optionstab.
• Then, mark the oval beside Inside End under the Label Positionoption.

• As a result, the labels will be repositioned to the far right of the graph.

• Now, repeat the process for the Male%column.
• Finally, we can present our age and gender distribution chart.

Read More: How to Calculate Current Age in Excel (2 Easy Ways)

2. Using Stacked Bar Chart with Gap in Between to Make Age and Gender Chart in Excel

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.

Follow these steps for the illustration of how we can create a Stacked Bar chart for age and gender distribution with gaps in between in Excel.

📌 Steps:

• Firstly, select the entire data.
• Secondly, choose the Inserttab.
• Thirdly, click on the Insert Column or Bar Chartoption.
• Finally, from the available options, select the Stacked Barchart.

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

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

• Then, select the plus sign on the right upon selecting the data in the gap.
• Check the Data Labelsoption.
• 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 Optionstab.
• Then, first, uncheck the Valuecheckbox.
• After that, check the Category Namebox.
• Consequently, we will have the Y-axis value in the middle.

• Now, select the data labels on the Male% data and delete them.
• Then, select the X-axisvalues.
• Go to the Axis Options.
• Select the Numberoption.
• Finally, under the box of the Format Code option write the following,

• Click on the Addcommand.
• As a result, the X-axis will have no negative value.

• Then, follow the same steps as the previous method to add labels to the data.
• Finally, we will have our age and gender chart with Y-axis values in the middle.

Read More: How to Make Age Pyramid in Excel (2 Suitable Methods)

Similar Readings

• How to Calculate Age in Excel from ID Number (4 Quick Methods)
• How to Calculate Age from Birthday in Excel (8 Easy Methods)
• Calculate Age in Excel in Years and Months (5 Easy Ways)
• How to Calculate Retirement Age in Excel (4 Quick Methods)
• How to Group Age Range in Excel with VLOOKUP (With Quick Steps)

3. Use of Conditional Formatting to Create Age and Gender Chart in Excel

In the final method, we will discuss the Conditional Formatting trick. There is a special type of formatting from the drop-down of the feature called Data Bars which we will use to our advantage. However, follow these steps for more details of the process.

📌 Steps:

• First, select the male column (the range C5:C10).
• Then go to the Home tab of your ribbon.
• After that, select Conditional Formatting from the Stylesgroup.
• Then hover your mouse over the Data Bars option from the drop-down.
• Now select More Rules from the menu.

• As a result, the New Formatting Rule box will pop up. Select Format all cells based on their values if it isn’t selected already.
• Then check the Show Bar Only option under the Editsection.
• On the down-right of the section, select the Bar Direction as Right-to-Left. Also, select a solid border of your preferred color.
• Again, you can choose your preferred color on the left side of it under the Bar Appearancesection.

• Once you are done, click on OK.

• Next, do the same for the Female column and you can choose your preferred color on the left side of it under the Bar Appearancesection.
• Finally, we will have an age and gender chart on top of the dataset using Conditional Formatting in Excel.

Read More: How to Calculate Age on a Specific Date with Formula in Excel

💬 Things to Remember

• You can modify the charts according to your personal preferences.
• In the case of conditional formatting, you can choose various types of Bar Appearance.
• Moreover, the first and second examples are pretty similar in process. However, you can select any of them.

Conclusion

These are all the steps you can follow to create an age and gender chart in Excel. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

Related Articles

• How to Calculate Average Age in Excel (7 Easy Methods)
• How to Convert Date of Birth to Age in Excel (3 Easy Ways)
• Metabolic Age Calculator in Excel (3 Useful Examples)
• How to Calculate Age in Excel for Entire Column
• How to Calculate Age Between Two Dates in Excel (6 Useful Methods)
• Excel VBA: Calculate Age from Date of Birth