Do you ever get tired of the mundane Excel charts?

Do you feel you can make your Excel charts more intuitive by adding custom infographic charts?

There may be times when the default chart formats just aren’t what you need. If you want to make your visuals more user-friendly, I suggest you tag along with me and discover the depth of the untapped beauty in Microsoft Excel.

In this article, you will learn how to add custom icons to Excel charts to make your visuals more intuitive and user-friendly. This graphical visual representation of data is known as Infographics.

We will be making use of a fictitious dataset on an on-demand rides platform for the entirety of this tutorial. We will be querying this dataset to know the percentage of the total number of completed and uncompleted rides.

At the end of this article, you should have learned how to create a custom chart like this in Excel:

Also whenever the values are updated, the icons also get automatically updated as seen in the example below:

How cool is that?…Alright, let’s get started on the good stuff.

Note: The most suitable type of Microsoft Excel chart for the application of infographics is the “column or bar chart”.

Follow these step by step guide on how to create custom infographic charts in Excel.

Step 1: Data preparation

Replicate the data table below by following the steps listed below. You need to create columns showing the percentage total of the data you want to display using infographics in Excel.

For example: In the image below, the “Complete” column shows the percentage for completed rides and the “Incomplete” column also shows the percentage for uncompleted rides.

This was ascertained via the calculation “=1-B2”. The B2 is simply a reference to the 50% on the “Complete” column.

The “Full” cells are the total amount of what the “Filled” calculation is over. i.e 50% simply means 50/100. This is needed in order to display a proper comparison of the data.

Step 2: Chart insertion in Excel

How to insert a column chart.

• Highlight the entire cells
• Click “Insert” command from the ribbon
• Select the chart option
• Select “the Column chart”

You should get something like this.

From the above image, you can see the “Complete” category and the “Incomplete” category displaying the “Full” and the “Filled” amount.

Step 3: Inserting and formatting the icons

Before we continue with the formatting, we have to select the icons.

• Click on an empty cell
• Go to “Insert”
• Click on “Illustrations”
• Click on the “Icons” menu
• Select your preferred icon choice and click on “insert”

Note – You should have the Office 365 version of Excel which allows you to connect to the internet to download extra resources such as icons from Microsoft.

Note : An internet connection is needed to display the icons

If you do not have the Office 365 version of Excel, you can manually download icons in SVG format (Scalable Vector Graphics) from flaticon.com. They have a wonderful library of free icons that you can download in SVG. It’s important to use a SVG image to create this infographic in Excel.

If you’ve downloaded the icon from an external source, go to the Insert menu > Pictures > This Device. Locate the SVG image that was downloaded.

After selecting the icon of your choice, you should get something like this:

The size of the icon looks smaller than the initial output that was shown earlier. In order to increase the height of the icon :

• Click on the icon
• Click on “Format” from the Excel ribbon
• Go to “Height” and increase it to 2

Get into the groove…

In order to get two different icons to display the“Complete” and “Incomplete” columns, we have to duplicate the icon. To duplicate, simply copy and paste the icon using Ctrl + C to copy, and Ctrl + V to paste.

Then:

• Click on any of the icons
• Go to the “Format” options, click on the “Graphics Outline” option, click on the drop-down menu and select “No Outline”
• Go to the “Graphics Fill” option, click on the drop-down menu, and select green and red colour for both icons from the displayed colour palette.

Your result should look like this:

The icons in the above image represent the “Filled” series for the complete and incomplete rides. We need another set of icons to make comparisons. This new set of Icons will be known as the “Full” series.

Duplicate the Icons again. At this point, your spreadsheet should consist of four icons. Format the duplicates in this order:

• Click on any of the duplicated icons
• Select “Format” options
• Select the “Graphics Outline” tab
• Click on the drop-down menu and select the black colour as shown in the image below
• Go to “Graphics Fill” tab, click on the drop-down menu and select “No Fill”

You should have an output looking like the image below.

Not bad, we are almost at the climax, hang in there..

Step 4: Customizing Excel charts

Now, let’s format the chart to display the data as icons.

• Select one of the transparent icons and copy it (use Ctrl+C)
• Click on any of the “Full” bars: it automatically selects both bars
• Paste (use Ctrl + V) the selected icon into the selected bars

Your chart should be displayed like this:

Now, to bring the series together by decreasing the gap width:

• Double click on any of the “Filled” bars to bring out the “Format Data Series” panel
• Go to series options, then adjust the “Gap Width” to zero (this makes the bars fatter)

Your result should be like this:

The next step is to copy and paste the “Filled” series icons into its bars by repeating the same steps we applied for the “Full” bars.

We will be using the green filled icon for complete and the red filled icon for incomplete.

Begin by copying the filled icon. To add the icons to their respective bars, double click on each bar to select only one of the bars (this is done to avoid pasting the same icon into both bars), then paste each icon respectively.

It shrunk the icon, right? In order to remedy that effect, we need to use the “Stack and Scale with” option. To apply this option :

• Double click on one of the “Filled” bars
• Right-click on it and select “Format Data Point” from the option, a side panel automatically comes up.
• Select the “Fill and Line” option, and select the “Stack and Scale with” option (you can define the units per picture), as it is shown under the “Stack and Scale with” option, in this tutorial, we will leave the unit as 1
• Repeat the above steps with the second “Filled” bar

Your result should look like this:

It can be seen in the output that it automatically halves the icon. This is because the percentage total for both Complete and Incomplete rides are 50% from the dataset.

Before we overlap the icons, we need to adjust the y-axis limit to 100%. To fix this:

• Select the y-axis label or double-click on it
• Go to Axis options on the chart elements
• Adjust both the “Minimum and Maximum” options to 0 and 1 respectively and press the enter key.

The applied changes can be seen below on the y-axis label.

For a clearer and more presentable chart, you can remove the gridlines by double-clicking on the horizontal lines to highlight it, and press the “delete” key on your keyboard to remove it.

The next step is to overlap the icons. To do this:

• Click on any of the series to select the bars
• Go to options on the Format panel
• Select “Series Overlap” and increase it to 100%
• After overlapping the series, to remove the outline of the bars, if any, click on any of the bars with an outline, right-click, select “Outline” from the option and select ‘No Outline”.
• Remove the legend and the y-axis label by clicking on each and press the “delete” key to remove it.

Your output after following the steps carefully should look like this:

It would be nice to add data labels above each icon.

From the dataset, it can be seen that the data label is associated with the “Filled” series. To activate the data label for the “Filled” series :

• Select any of the icons
• Go to the Format menu and select the drop-down menu in front of the ‘Series Options”,
• Select the Series “Filled” from the option

After selecting the Series “Filled” option, click on the plus sign(+) icon at the top-right corner of the chart and check the box next to “Data Label”.

The labels are correct, however, the positioning can be improved. We want both labels to be at the top of the chart. To do this, let’s activate the “Full” series.

• Remove the current data labels
• Click on the “Full” series, click on the (+) sign, and check the box next to “Data Labels” just as it is shown in the above image

You should get something like this:

Notice that the positioning is right, however, the labelling is wrong. It is imperative to note that the labels are not as important as the position because the labels can easily be adjusted. To adjust the labels :

• Double-click on the labels
• Go to “Label Options”
• Select “Value From Cells”
• From the pop-up prompt, select the “Data Label Range.”
• Select the “Filled” cells and click OK.

By default, it will show the new added values and the original values, i.e something like this

To remove the the other values not needed which are the 100% :

• Double-click on the labels
• Go to the Format panel
• In the “Label Options”, uncheck the “Value” box

You should have something like this:

• After that, adjust the labels by increasing the font size and boldness of the fonts.
• Remove the border around the charts
• Remove the outline from the x-axis labels
• You can make the chart area a bit smaller or bigger by adjusting the size
• You can as well adjust your plot area by selecting the plot area, go to the side panel, select the drop-down menu in front of the “Series Options” and select the “Plot Area” from the options listed
• Finally, you can add a title to your chart by clicking on the (+) sign by the chart and check the “Chart Title” box

Here comes the final output of our infographic chart in Excel.

You can play around with the icons specific to the type of infographic in Excel that you want to create. For instance, by changing the icon and formatting the chart background, we can come up with another output like this:

We do hope that you found this blog exciting and insightful, for more access to such quality content, kindly sign up to the Resa platform by clicking here.

Thank you for learning with Us!