# How to Create an Age and Gender Chart in Excel – 3 Examples

This is the sample dataset. The Y-axis value is placed on the left.

### Example 1 – Using a Stacked Bar Chart Without Gaps in Between

Steps:

• Select C11 and enter the following formula,

`=SUM(C5:C10)`

• Press Enter.

• For the Female group. selecl D11 and enter the following formula.

`=SUM(D5:D10)`

• Press Enter.

• Select E5 and enter the following formula,

`=-(C5/\$C\$11)`

• Press Enter.

The formula has a minus sign to place the data to the left of the chart.

• The percentage of males belonging to that age group will be displayed.
• Drag down the Fill Handle to AutoFill the rest of the cells.

• Go to the HomeÂ tab.
• In Number, select the %.

• Data will be presented in percentage.

• Click F5 and enter the formula below.

`=D5/\$D\$11`

• Press Enter.

• The proportion of females in that age group will be displayed.
• Drag down the Fill Handle to AutoFill the rest of the cells.

• Convert the proportion of females into a percentage following the same steps indicated for males.
• Select the Age Group, Male%, and Female%Â columns.
• Go to the InsertÂ tab.
• Choose Insert Column or Bar Chart.
• Select Stacked Bar Chart.

• This is the output.

• Select the labels on the graph.
• Choose Series Options.
• Select Labels.
• In Label Position, select Low.

• Data labels will be placed on the left.

• Double-click the X-axisÂ label.
• Go to Axis Options.
• Click Number.
• Enter the following code.

`#,##0%;[Black]#,##0%`

• All negative values in the X-axis will be removed.

• Click the bars in the FemaleÂ group.
• Select the plus sign.
• Check Data Labels.

• Labels will be displayed.

• Select DataÂ labels.
• Go to the Label OptionsÂ tab.
• inÂ  Label Position, check Inside End.

• Labels will be repositioned on the right of the graph.

• Repeat the process for the Male%Â column.
• This is the final age and gender distribution chart.

### Example 2 – Using a Stacked Bar Chart with Gaps in Between to Create an Age and Gender Chart in Excel

The Y-axis value will be set in the center. A new column (Gap) is inserted to create a gap between the two groups.

Steps:

• Select the entire data.
• Go to the InsertÂ tab.
• ClickÂ  Insert Column or Bar Chart.
• Select Stacked Bar.

• A graph with a gap between the main data will be displayed.

• Select the GapÂ data.
• Go to Series Options.
• In Fill, selectÂ  No Fill.
• In Border, select No Line.

• Select the data in the gap and click the plus sign on the right.
• Check Data Labels.
• 0.1 will be displayed in each level (the value of each cell in the Gap column).

• To change it, go to the Label OptionsÂ tab.
• Uncheck Value.
• Check Category Name.
• The Y-axis value will be in the center.

• Select the Male% data labels and delete them.
• Select the X-axisÂ values.
• Go to Axis Options.
• Choose Number.
• Enter the following code.

`#,##0%;[Black]#,##0%`

• The X-axis will showcase no negative value.

• The age and gender chart with Y-axis values in the middle will be displayed.

### Example 3 – Using Conditional Formatting to Create an Age and Gender Chart in Excel

Steps:

• Select the male column (C5:C10).
• Go to the Home tab.
• In Styles, select Conditional Formatting.
• Choose Data Bars.
• Select More Rules.

• In the New Formatting Rule window, select Format all cells based on their values.
• In Edit, check Show Bar Only.
• Select the Bar Direction as Right-to-Left. Choose a color andÂ  a solid border.
• In Bar Appearance, choose a color.

• Click OK.

• Follow the same procedure for the Female column.
• The age and gender chart will be displayed.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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