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
- In the first column, put the result for man and woman
- 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
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.
- Select the first icon (the Graphics Format menu appears)
- Apply a transparent background, certainly not the white color, from the menu Graphics Format > Graphics Fill > No Fill
- Add a gray or black border of 1pt from the menu Graphics Format > Graphics Outline > Weight
For the second icon
- Apply the fill color.
- 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
- Selecting your data, including the total row
- 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.
- Select the chart
- Right-click, Change chart type
- Choose histogram with multiple bar colors
Step 7: Change the maximum axis
By default, the maximum value of the axis is always the highest value, plus a margi. That's why, the axis shows a max value of 120%. We must change this value to display 100% for the maximum
- Double-click on the axis where there are percentages
- Change the maximum value to 1 (i.e. 100%)
Step 8: Replace the bars with the icons
Now let's replace the bars with the icons
- Select the icon with the transparent background
- Then you copy it (Ctrl + C)
- Select the maximum bar of your chart (a first click selects all the bars, a second click selects a single bar)
- Paste icon (Ctrl + V)
Proceed in the same way for the second icon (the one that is colored) 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
- Double-click the colored icon to select the series option
- Choose the Fill & Line options (the paint can)
- Select the Stack and Scale option with the value 1
Step 10: Overlap the icons
- Select one of the icons
- Go to the bar options menu (the icon that looks like a chart)
- Change the overlap value to 100%
- Set the width of the Gap Width 0%
Step 11: Add the percentage as Chart labels
Now, we will add the percentages for men and women above the icons
But for the moment, we have the result for the taller icon (the transparent one).
To change this,
- Double-click on the label
- Select the option Value from Cells
- Click on the button Select Range
- Select the cells with the percentage to display
Step 12: Format the chart
The last step, you can add a border to present your chart as a card in your worksheet