Creative infographics in Excel
(Male, Female icons)
Using fillable icons on charts is a good way to visualize data.
In this example, the male and female icons will be used.
To do this:
- Set up the data table
- Insert a clustered column chart
- Insert icons into the chart
- Format the chart axis
- Overlap the series
- Format the chart area
Inserting icons into the chart
Go to the Insert tab and select the icons to be used in the chart.
I suggest using a vector icon so that the fill color and outline color can be defined separately.
Format the icons as preferred through the Format tab
- Icon size
- Graphics outline
- Graphics fill – this color will dictate the color of the Filled series
Duplicate the icons and define the formatting for the Full data series.
An empty fill can be used with a thick outline.
Transfer full icons to the chart
Transfer the Full icons to the chart by clicking on the icon and pressing CTRL + C (or right-click and select Copy).
Click on the corresponding series on the chart.
Select a single series by double-clicking and then pressing CTRL + V to replace the column with the icon.
Make sure that a single series has been selected, or the entire series will be replaced with the icon.
Do the same for the remaining Full series.
To widen the icons, change the gap width.
Select the chart and go to the series properties.
There are two options to get there:
- Press CTRL + 1
- Right click on the series and select Format Data Series
Under Series Options, set Gap Width to 0%.
Transfer filled icons to the chart
Do the same to transfer the filled icons to the chart.
However, you will notice that the icon shrinks instead of retaining its full size.
To change this into a cut-off version depending on the value, go to the Format Series Options and under Fill Color, select the Stack and Scale with option.
Repeat this for the Male Filled series.
Formatting the chart axis
To maximize the chart area, set the maximum value of the vertical axis to 100%. Go to Format Axis > Axis Options > Maximum = 1.0
Overlapping the series
Formatting the chart area
Further formatting can be done on the chart to give it a more polished look:
- Remove vertical axis labels
- Add or remove horizontal axis labels
- Remove gridlines
- Add or remove chart title
- Add data labels
To add the data labels, click on the + sign on the chart and tick the Data Labels option.
By default, this will display the value of the Full series instead of the Filled series.
To change this, go to Formal Data Labels properties and under Label Options, tick Value from cells.
On the prompt asking for a Data Label Range, highlight the values of the Filled series.
It would show both the value from the Filled series along with the original values.
To remove the latter, untick the Value option on the Format Data Labels options.
For older versions of Excel
Double-click on the data label to select a single data label and go to the formula bar.
Do a cell reference to the value to be displayed.
Video and Workbook
Feel free to Download the Workbook HERE.
The new Excel Dashboards course is here!
Visually Effective Excel Dashboards for your reports.
Redeem your coupon for 75% off below.