Pictures in Excel 5: Pictures in Data Types

How to Insert Images into Excel Cells with Data Types and Use Them in PivotTables

Welcome to the final post in the Pictures in Excel series! In this tutorial, we will insert images into Excel cells using data types and then incorporate these images into PivotTables. By the end of this post, you’ll learn how to add images by using data types, and then use the resulting data to create PivotTables. So let’s dive right in!

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

Video

Basic Data Table

Let’s take a look at our employee data table:

We would ultimately like to display the number of employees by State, but, we’d like to add some visual appeal. So, we decide to add a graphic of the state flag for each state.

One option would be to locate the state flag images online, download each of them, and manually insert them into the data table. But, since we don’t want to spend that much time, we’ll use the built-in Geography data type to have Excel do all of the work for us … instantly.

Geography Data Type

The first step is to select the entire State column.

With the State column selected, we click the Data > Data Types > Geography command.

We notice that the State column now includes a little geography icon and the state name is fully spelled out. Plus, we have a new Add column button:

These changes indicate that Excel has successfully converted the state column into a Geography data type. This is one of several linked data types that include rich information. We can easily add the additional information by using the Add Column button. For example, we can click it and select Abbreviation to add an abbreviation column:

We can also select Image which inserts the state flags:

Now that the state flag images are stored in cells, we can use them in a PivotTable.

Creating a Pivot Table with Images

To create the PivotTable, begin by selecting any cell in the data table. Then …

1. Click on the “Insert” tab in the Excel ribbon.
2. Under the “Tables” section, click on the “PivotTable” button.
3. In the dialog box that appears, select the option to create the PivotTable in a new or existing worksheet.
4. Choose the cell where you want the PivotTable to be placed.
5. Click “OK.”

Excel creates an empty PivotTable that we can then customize.

On the PivotTable Fields pane, you’ll see a field for each column in your data, including the Image field:

We can insert the Image field into the Rows or Columns layout area.

For example, we could show the count of employees by state by inserting the Image field into the Rows layout area:

Or, we could add department columns and include the state abbreviation:

Feel free to explore different ways to organize and format your PivotTable as desired.

Conclusion

Congratulations on successfully incorporating images into Excel cells and PivotTables. By utilizing the power of data types in Excel, you can easily make your PivotTables more engaging and easier to read. This feature opens up a world of possibilities for visualizing and analyzing data. Don’t hesitate to experiment further with images and explore other data types available in Excel.

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

Sample file