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

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.

How to Create Age and Gender Chart in Excel: 3 Suitable Examples

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. 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,

=SUM(C5:C10)

Then, press Enter.

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

=SUM(D5:D10)

After that, press Enter.

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

=-(C5/$C$11)

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 Home tab.
From there, select the % sign under the Number group.

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

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

=D5/$D$11

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

Consequently, we will have our chart for our data.

Then First, 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.

Double-click 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.

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.

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

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 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 Insert tab.
Thirdly, click on the Insert Column or Bar Chart option.
Finally, from the available options, select the Stacked Bar chart.

Consequently, we will have a graph with a gap 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.

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.

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, 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.

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 Styles group.
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 Edit section.
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 Appearance section.

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 Appearance section.
Finally, we will have an age and gender chart on top of the dataset using Conditional Formatting 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.

Download Practice Workbook

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

Age and Gender Chart.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!

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

How to Create Age and Gender Chart in Excel: 3 Suitable Examples

1. Using Stacked Bar Chart Without Gap in Between

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

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

💬 Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply