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.

**Table of Contents** hide

Download Practice Workbook

3 Suitable Examples to Create Age and Gender Chart in Excel

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

## Download Practice Workbook

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

Age and Gender Chart.xlsx

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

`=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 Firstly, 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**levels. - 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.

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

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

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