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

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. 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%`

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

## 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

<< 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.
Mehedi Hasan

Mehedi Hasan, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a profound passion for research and innovation, he actively engages with Excel. In his capacity, Mehedi not only adeptly tackles intricate challenges but also showcases enthusiasm and expertise in navigating tough situations with finesse, underscoring his unwavering dedication to consistently delivering exceptional and high-quality content. He... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF