Creating pictogram chart with colorful filling

Excel 365
A pictogram (also known as pictorgamme, pictograph, icon chart, pictorial unit chart, or picto) is a chart or graph which uses icons, symbols, illustrations, or pictures to represent data. Pictograms have become quite popular in modern infographics to present simple facts, but it is not obvious how to create this chart in Excel.

In pictographs, the pictogram item, such as an icon, picture, or symbol, can represent one or multiple data units. Pictograms often include a key that indicates how much data is included in each item of the pictogram.

For example:

  • In the picture chart, the image represents 20 units, the key = 20 (see more about picture chart):
    Picture chart in Excel 365
  • In the pictogram below, the key = 2 units: one leaf represents two data units, and the half-filled leaf represents one data unit:
    Pictogram in Excel 365
  • The icon chart displays one picture per unit: two cups of coffee per day is normal - pay attention to the extra coffee you drink:
    Icon chart in Excel 365

To create a pictogram, you can use one of two different approaches:

  • Approach A: Each picture represents the exact value of the data units - there are no partially filled pictures on the chart. Before creating a chart, you need to round the data to the nearest key value. All data were rounded up to 20 units for the first example above.

    Note: You can add data labels to see the data without rounding.

  • Approach B: All pictures have the same size, but part of the icon can be used to display the corresponding part of this amount, as shown in the second example above.

Create a pictogram chart

To create a pictogram chart in Excel, do the following:

   1.   Create a column or bar chart: on the Insert tab, in the Charts group, click the Insert Bar or Column Chart button:

Column Charts in Excel 365

Choose the chart you prefer. See more about chart types for pictograms below.

   2.   Select the data series, then do one of the following:

  • Right-click on the data series and choose Format Data Series... in the popup menu:
    Format data series in popup menu Excel 365
  • On the Chart Format tab, in the Current Selection group, click the Format Selection icon:
    Format Selection in Excel 365
  • Double-click on the selection.

   3.   On the Format Data Series pane, on the Series Options tab, in the Fill & Line group, in the Fill section:

  • Choose the Picture or texture fill option,
  • Under Picture source, click the Insert... button:
    Fill Data Series by picture in Excel 365

    In the Insert Pictures dialog box, choose the picture location, select the picture file, and then click Open or Insert:

    Insert pictures in Excel 365
  • Select one of the following options:
    The picture Chart in Excel 365
    • Select the Stack option to format the picture to repeat the width of the data area. You will see as many pictures as Excel can put to the data area independently of the data series value.
    • Select the Stack and Scale with option adjusts the scale of the stacked pictures using the Units/Picture field. E.g.:
      • One picture per two units:
        Icon chart with two units per picture in Excel 365
      • One picture per one unit:
        Pictogram chart with one unit per icon in Excel 365

Chart types for the pictogram with more than one data series

You can use one of the following chart types to create a colorful effect in an Excel chart:

  • A stacked bar, 100% stacked bar, stacked column, or 100% stacked column chart.
  • A clustered bar or clustered column chart.

Stacked bar or column charts

These chart types can be used only with Approach A. Each data series has the width of one or multiple pictogram items, i.e., the data values rounded to the pictogram key. Thus, the second data series always starts from the whole picture.

Usually, in such charts:

  • The first data series represents the number of completed tasks, filled items, calculated units, counted marks, etc.,
  • The second data series displays the number of remaining items to complete, fill, calculate, count, etc.

See below for more about adding a colorful effect.


Clustered bar or column charts

The main idea behind using these types of charts is to overlay the first data series on top of the second one:

  • The first data series, like for stacked bar or column charts, represents the number of completed tasks, filled items, calculated units, counted marks, etc.,
  • The second data series contains the total number of items.

To create a clustered bar or column chart with an overlay effect, do the following:

   1.   Create a clustered bar or column chart as usual.

For example:

Column chart draft in Excel 365

   2.   Right-click any data series and choose Format Data Series... in the popup menu:

Format data series in popup menu Excel 365

   3.   On the Format Data Series pane, in the Series Options group, in the Series Options section:

  • In the Series Overlap field, type or select 100%:
    Series Overlap for Column chart in Excel 365
  • Optionally, choose the value to create columns or bars wider or narrower in the Gap Width field.

Add the colorful effect

Do one of the following to add a colorful effect:

  • Use the same picture, icon, image, or symbol for different data series but with different transparency.

    To change the transparency of the data series picture, do the following:

       1.   Open the Format Data Series pane (see point 2).

       2.   On the Format Data Series pane, on the Series Options tab, in the Fill & Line group, in the Fill section, choose the appropriate value in the Transparency field:

    Transparency for colorful effect in Excel 365

    For example:

    Colorful effect in pictorgam Excel 365
  • Use two different pictures with the same dimensions for the different data series:
    Colorful effect in pictorgamme Excel 365
  • Use two variants of the same picture, icon, image, or symbol for different data series but with different picture effects. To apply picture effects, do the following:

       1.   Paste the picture you used for the first data series into the Excel spreadsheet.

       2.   Right-click on the picture and select Format Picture... in the popup menu (or double-click the picture):

    Format Picture in popup menu Excel 365

       3.   On the Format Picture pane, in the Picture group:

    • In the Picture Color section, from the Presets list, choose Saturation: 0%:
      Picture Color in Excel 365
    • Optionally, in the Picture Transparency section, choose the appropriate value in the Transparency field or use a slicer:
      Picture Transparency in Excel 365

      For example:

      Colorful effect in picture chart Excel 365

See also how to remove the picture from the chart.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more