This article will explain step-by-step how to create a chart man & woman chart in Excel.

Step 1: Percentage and Total

The first thing to do is to build a table with the value. It's compulsory to have exactly this presentation

1. In the first column, put the result for man and woman
2. In the second column, write the value 100%, for both row

Note also that in this table, the percentage for men is deduced from the total and the percentage of women

=C2-B2

Step 2: Insert icons

• From the menu Insert > Icon

• Select the icons man and woman. To facilitate the search, you can directly type the name in the search bar.

Step 3. Duplicate icons.

Now, we need to duplicate each icon (one for the full size and one for the color). You can use the shortcut Ctrl + D to duplicate each icon.

Step 4. Change the color of the icons.

Next, we need to change the color of the 2 identical icons.

1. Select the first icon (the Graphics Format menu appears)
2. Apply a transparent background, certainly not the white color, from the menu Graphics Format > Graphics Fill > No Fill
3. Add a gray or black border of 1pt from the menu Graphics Format > Graphics Outline > Weight

For the second icon

1. Apply the fill color.
2. And also, 1pt for the border of the icon

And, of course, proceed in the same way for the other 2 icons. Now, you have something like this.

Step 5: Build the grouped chart

1. Selecting your data, including the total row
2. Menu Insert > Chart > Bar Chart > Clustered column

Step 6: Change the chart type

The next trick isn't easy to find. The default histogram use the same color for all the bars. In our case, we want a different color for each bar.

1. Select the chart
2. Right-click, Change chart type

3. Choose a histogram with multiple bar colors

Step 7: Change the maximum axis

By default, the axis's maximum value is always the highest value, plus a margin. That's why the axis shows a maximum value of 120%. We must change this value to display 100% for the maximum.

1. Double-click on the axis where there are percentages
2. Change the maximum value to 1 (i.e., 100%)

Step 8: Replace the bars with the icons

Now, let's replace the bars with icons

1. Select the icon with the transparent background
2. Then you copy it (Ctrl + C)
3. Select the maximum bar of your chart (a first click selects all the bars, a second click selects a single bar)
4. Paste icon (Ctrl + V)

Proceed in the same way for the second icon (the colored one) and replace the smaller bar.

Step 9: Change the behavior of the colored icon

We will now proceed to a transformation only for the colored bar

1. Double-click the colored icon to select the series option
2. Choose the Fill & Line options (the paint can)
3. Select the Stack and Scale option with the value 1

Step 10: Overlap the icons

1. Select one of the icons
2. Go to the bar options menu (the icon that looks like a chart)
3. Change the overlap value to 100%
4. Set the width of the Gap Width 0%

Step 11: Add the percentage as Chart labels

Now, we will add the percentages for the pictograms representing a man and a woman

But for the moment, we have the result for the taller icon (the transparent one). To change this,

1. Double-click on the label
2. Select the option Value from Cells
3. Click on the button Select Range
4. Select the cells with the percentage to display

Step 12: Format the chart

In the final step of the man and woman chart, you can add a border to display your chart as a card in your worksheet.

Related Articles

• Population Pyramid with Excel
• How to create a Gantt Chart with Excel?
• Sunburst Chart with Excel
• Radar chart in Excel