Hi ,

Images in Excel PivotTables

With Microsoft 365 we can now include images in Excel PivotTables, as well as built in data types like Stock and Geography, Power Query custom data types and Power BI Organisational Data Types.

This opens up the ability to create dynamic and visually rich PivotTables like never before. When combined with some slick formatting we can create PivotTable reports that look nothing like the PivotTables we’re accustomed to.

The animated image below demonstrates a PivotTable containing images being filtered by a Slicer.

NOTE: The image below is animated.  If you aren't seeing any movement in it, view the original image here.

Watch the Video

Download the Example Workbook

Inserting Images in Excel

PivotTables can work with images that have been placed in a cell using any of the following techniques:

• IMAGE function
• via a data type: either built in data types, Power Query custom data types or Power BI Organisational Data Types
• via the Insert tab of the ribbon > Insert > Pictures > Place in Cell*
• via the clipboard > Paste > Paste Picture in Cell or right-click > Paste Options > Paste in Cell*

*The ability to paste or insert an image in a cell is currently only available to some users on the Insider Channel. I haven’t won the insider lottery for this new feature, so I can’t demonstrate it yet. In these examples I’ll be using the IMAGE function.

Note: PivotTables can only work with images inserted using one of the 4 methods above. They cannot work with image objects.

Creating PivotTables with Images

Once you have images in your dataset, like the table I have below for the Flag and Jersey columns:

Simply insert a PivotTable and add the image fields to either the row or column labels:

The images will automatically resize to fit the available space in the cell and with some clever formatting, we can create interactive reports like this:

NOTE: The image below is animated.  If you aren't seeing any movement in it, view the original image here.

If you’d like to see how I created this report, watch the video above for a step by step tutorial.

Images in PivotTables Limitations

Unfortunately, images do not work in Slicers, yet. Fingers crossed they’re on the to-do list at Microsoft, but I won’t be holding my breath.

And as mentioned earlier, you cannot use image objects. The images must be inserted in a cell. This means users with Excel 2021 and earlier will not have this functionality.

Tables containing images cannot be loaded into Power Query.

Creating PivotTables with Data Types

Another new feature is the ability to create PivotTables from data types such as the built in data types like Stock and Geography, Power Query custom data types and Power BI Organisational Data Types.

Because data types can also contain images, we can insert the images into cells by referencing the data type, as shown in the formula bar of the image below:

I can then use this table as the source data for my PivotTable and the data type and image are available in the PivotTable:

I can click on the data type to see more information about the city:

I can’t add columns to the PivotTable from the card like you can with data types in Tables or cells. However, you can reference the PivotTable cells and extract data:

Please Share

If you liked this please share this tutorial with your friends and colleagues.

Have a great day,

Mynda Treacy

Co-founder My Online Training Hub