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.


Download Practice Workbook

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


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.

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


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.

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

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

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

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

Chart Elements to Create Age and Gender Chart in Excel

  • As a result, we will have labels on the data. Now, we need to reposition it.

Creating Age and Gender Chart in Excel

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

Age and Gender Chart in Excel

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.

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

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

Format Data Series Dialog Box

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

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

Read More: How to Make Age Pyramid in Excel (2 Suitable Methods)


Similar Readings


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.

Using Conditional Formatting to Create Age and Gender Chart in Excel

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

Age and Gender Chart in Excel by Using Conditional Formatting

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.

For more information like this, visit Exceldemy.com.


Related Articles

Mehedi Hasan

Mehedi Hasan

Hi, I am Mehedi. I have completed my B.Sc. from Bangladesh University of Engineering and Technology. I have a strong interest in innovation and research in the field of Data Science and Machine Learning. Gradually, I now understand the value of Data Analysis and I am trying to learn everyday.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo