Pictures in Excel 4: Pictures in PivotTables

How to Use Pictures and PivotTables in Excel

Introduction

Welcome to the next post in our Pictures in Excel series. In this tutorial, we’ll explore how to use pictures as cell values and incorporate them into PivotTables. So, let’s jump right in!

Note: the capabilities presented in this post are not available in all Excel versions.

Video

Inserting Pictures into Data Cells

To create a PivotTable with pictures, our data source needs to include the desired images. For example, consider this data table which we’ll use as the PivotTable data source:

The cells in the Icon column contain pictures. These will be able to be placed in the row or columns layout area within the PivotTable.

One way to get the pictures into the data column is by using the Insert > Pictures > Place in Cell command.

Note: the Place in Cell command is not available in all Excel versions.

Another option is to include the pictures in a lookup table, and then retrieve them with your favorite lookup function. For example, here is a lookup table that could be used:

Then, we could retrieve the corresponding icon picture into the PivotTable data source table with your favorite lookup function such as XLOOKUP or VLOOKUP.

In any event, once the data table contains the pictures you’d like displayed in the PivotTable, we are ready to create the PT.

Building a Pivot Table with Pictures

We’ll create a PivotTable and explore how to incorporate the icon column into it. Let’s get our basic PivotTable inserted:

1. Select any cell within the data range table.
2. Navigate to the “Insert” tab and choose “PivotTable.”
3. In the “Create PivotTable” dialog, choose whether you want to add the PivotTable to an existing worksheet or create a new one.
4. Click OK.

You’ll see the “PivotTable Fields” pane on the right side of the Excel window. It contains the fields from your data table, including Date, City, Hi/Lo, Weather, and Icon!

Now, we can insert the Icon column into the PivotTable rows or columns layout area.

For example, if we wanted to count the number of days for each weather condition, we could:

1. Drag and drop the “Icon” field into the “Rows” layout area.
2. Drag and drop the “Date” field into the “Values” layout area.

The resulting PivotTable includes the pictures as row labels:

We can also experiment with the layout further. For example, let’s add the “City” field to the columns layout area … bam:

Or, maybe we want cities in rows and icons in columns:

Since this is a PivotTable, you can arrange fields and set report layouts as desired.

For example, maybe we want to show both the icon and the description in rows:

To show both the Icon and Weather description as row labels:

1. Select the PivotTable.
2. Go to the “Design” tab under “PivotTable Tools” in the ribbon.
3. From here, you can change the layout to “Tabular” format.
4. To remove subtotals, click the drop-down arrow in the “Subtotals” button and select “Do Not Show Subtotals.”
5. To remove the row column headers, uncheck the “Headers” box in the “Options” section.
6. You can also disable the “+” and “-” buttons by unchecking the “Field Buttons” box.

Conclusion

By inserting pictures as cell values and incorporating them into PivotTables, you can enhance the appearance of your PivotTables and make them easier to read quickly. Using pictures provides a visually appealing way to present information.

If I can provide any additional details, or of you have suggestions or alternatives, please share by posting a comment below … thanks!

Sample file