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:

  1. Set up the data table
  2. Insert a clustered column chart
  3. Insert icons into the chart
  4. Format the chart axis
  5. Overlap the series
  6. Format the chart area
infographic template excel male female

Setting up the data table

An additional series is needed in order to visualize the value as part of a total amount.

To do this, add a new series as shown below:

Inserting a Clustered chart

Highlight data to be plotted on the chart and go to Insert > 2D column > Clustered chart

For now, this will show separate grouped columns for the Male and Female data.

Once the icons are in place, the series will be layered on top of each other to achieve a fill effect.

Inserting icons into the chart

Insert icons

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.

Widen icons

To widen the icons, change the gap width.

Select the chart and go to the series properties.

There are two options to get there:

  1. Press CTRL + 1
  2. 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

Under Series Options, set Series Overlap to 100%.

Note that by default, the series that does the final overlap (or is seen on top) is the last series on the data table.

To switch this, right-click on the series and go to Select Data.

Select the series to be moved and click on the up or down arrows.

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.

Free Excel Download

The new Excel Dashboards course is here!

Visually Effective Excel Dashboards for your reports.
Redeem your coupon for 75% off below.

GET 75% OFF THE COURSE
Free Ebook

4 Comments

  1. Tariq Abdul Qadir August 12, 2018 at 10:50 am - Reply

    Hi Leila,
    I am using Excel 2016. How could i get male/female icon used for this purpose.
    Thanks

    • Leila Gharani August 18, 2018 at 6:34 pm - Reply

      Hi Tariq, If you don’t have the icons feature, you can get the icons from my workbook. Once you download it, copy and paste them in your version. You can also use any other free graphic on the web.

  2. Ashkan Ahmadi September 3, 2018 at 2:44 pm - Reply

    Hi Leila
    Im From Iran and Its not possible for me to buy your courses cause of sanctions !
    how we can purchased them ?

    • Leila Gharani September 3, 2018 at 4:06 pm - Reply

      Thank you for your interest in my courses. Currently the payment methods are only paypal or credit card. This might change in the near future. I’ll let you know.

Leave A Comment

Share This