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.

Sample Dataset for How to Create Age and Gender Chart in Excel


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

Steps:

  • Select C11 and enter the following formula,

=SUM(C5:C10)

  • Press Enter.

Using Stacked Bar Chart Without Gap in Between to Create Age and Gender Chart in Excel

  • For the Female group, select 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.

Format Axis Dialog Box to Create Age and Gender Chart in Excel

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%

  • Click Add.

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

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

Chart Elements to Create Age and Gender Chart in Excel

Labels will be displayed.

Creating Age and Gender Chart in Excel

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

Age and Gender Chart in Excel


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.

Using Stacked Bar Chart with Gap in Between to Create Age and Gender Chart in Excel

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.

Format Data Series Dialog Box

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

  • Click Add.

The X-axis will showcase no negative value.

  • Follow the steps in Example 1 to add labels.

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

Age and Gender Chart in Excel Using Stacked Bar Chart with Gap in Between


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.

Using Conditional Formatting to Create Age and Gender Chart in Excel

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

Age and Gender Chart in Excel by Using Conditional Formatting

 


Download Practice Workbook

Download the workbook here.


Related Articles


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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo