Today, we are going to discuss about advanced charting techniques in excel to create Male and Female infographics.
Let's take a look at our requirement and result,
Only Excel 2016 or above version has the access for the icons in Insert menu.
Let me start this from scratch,
We do have the data for male and female contribution in D3:D4 range as shown below,
For both Male and Female, we need the full value of 100% to get the outline of infographic, so we need to add 100% in E3 and E4 cell. If you want to hide this values, you can do this by changing the font color into white. I will show you that at the end.
Now, select the data from C3:E4 and press Alt+F1 or after selecting the data go to insert menu and pick clustered column chart as shown below,
Now, you can see that, chart design menu got added when you select the chart, this is the contextual menu which will vary based on the object you insert.
In the chart design menu, click on the 'change chart type' option at right side corner.
Dialog box will open, in that select 2nd type of column chart as shown below,
and then click OK. Click on the legends in the chart and press delete. Click on the chart title and press delete. Click on the grid lines and press delete. we can select the chart elements in multiple ways, I will show you 2 methods in below pics,
Method 1:- Click on the Element
Method 2:- Press Ctrl+1 to open format shape dialog box, click on the arrow to get the elements of chart.
In any one of above method select the vertical axis, in format axis dialog box, in axis options, set the maximum value as 1. Then delete vertical axis. Till now, the output will look like below, just to check the progress.
To insert the icons, Click insert on the menu bar, click the drop down in Illustrations select Icons option, A dialog box will open as shown below.
In search bar type male and select the icon and click insert and do the same for female icon.
Keep the copy of both male and female icon as shown below,
Now select one female icon, remove the fill and select outline color based on your preference, then select other female icon and change the fill color based on the first female icons, outline color. Then do the same for male icons.
Now the clustered column chart and the icons are ready, we have to replace the column charts with the icons. For that, Copy the outlined female icon and select the full clustered column for female. If we select any one of the full column, then both female and male full column will get selected, then again select the female column to only select our desired column, and then press Ctrl+V. You can see that Female column is replaced with our outlined female icon. Do the same for all 4 columns.
Select the icon within the chart and press Ctrl+1 to open the format object dialog box
Click on the filled male icon once and then click again to only select the filled male icon, in the format dialog box, click 'Fill bucket' option and drag all the way down to see 'Stack and scale with' and enter 1 in 'Units/Picture' to represent 100%. Do the same for Male Icon.
Click out of chart and click in the chart again, now click on the series option, then change the gap width to 100% and series Overlap to 100%.
You can resize the chart based on your requirement, you can hide the range E3:E4 by changing the color of font to white as shown below.
To add data labels, select any on icon in chart and press + button right side top corner in chart, and check the box for data labels. It will put 100% as the data label for both icons, to change that select any of the data label, in the label options section, Uncheck the box for 'value' and check the box for 'value from cells'. It will open the 'Data Label Range' dialog box, in that feed the range D3:D4 and click OK button. Now, Dynamic Male and Female infographic is ready to be placed in the dashboard.
Comments