Pictures in Excel 2: Pictures in Formulas

Using Pictures as Cell Values in Excel Formulas

Overview

Welcome to the second post in the Pictures in Excel series! In this tutorial, we will learn how to insert pictures as cell values in Microsoft Excel and utilize these pictures in formulas. By the end of this tutorial, you will be able to store images as cell values and retrieve them using Excel formulas. Let’s get started!

Note: the Insert > Pictures > Place in Cells command is not available in all Excel versions.

Video

Inserting Pictures as Cell Values

In the previous post, we learned how to insert pictures directly into cells. Let’s start by using that knowledge to create a lookup table that contains a year and the related badge image.

To insert the badge images into each cell, follow these steps:

1. Click on the “Insert” tab in the Excel ribbon.
2. Go to “Illustrations” and select “Pictures > Place in Cells > This Device.”
3. Browse to the location of the image you want to insert and double-click on it.
4. The image will be inserted as a cell value.

Note: the Insert > Pictures > Place in Cells command is not available in all Excel versions.

With the lookup table created, we can now retrieve the badge pictures with a formula.

Retrieving Pictures Based on Cell Values

Now that we have inserted images as cell values, let’s learn how to retrieve these pictures based on specific criteria, such as the entered year.

We can use Excel’s lookup functions like VLOOKUP, INDEX/MATCH, or XLOOKUP. In this example, we will use XLOOKUP.

Note: XLOOKUP is not available in all Excel versions.

Follow these steps:

1. Type “=XLOOKUP(” in the cell where you want the picture to be displayed.
2. Specify the cell containing the criteria you want to match, followed by a comma.
3. Enter the range of cells containing the values you want to search for, followed by a comma.
4. Specify the range of cells containing the pictures, followed by a closing parenthesis.
5. Press Enter to complete the formula.

For example, if our year was stored in C9, our years column in Table1[Year End], and our badge column in Table1[Badge], then we could use the following formula to retrieve the corresponding picture:

=XLOOKUP(C9,Table1[Year End],Table1[Badge])

Bam! We now have a formula that retrieves the correct image based on the entered cell value.

Enter 2020 and bam:

Enter 2023 and bam:

Adjusting Picture Visibility

To better visualize the retrieved picture, you have a couple of options.

You can simply increase the cell dimensions. If you want to keep the picture within the cell boundaries, you can adjust the cell dimensions (column width, row height) to accommodate the image size.

Alternatively, you can create a picture reference. To do so, right-click the picture cell and select “Picture in Cell > Create a Reference.” This will create a copy of the image in the drawing layer (above the grid) that can float freely. It will still remain linked to the original cell value and change accordingly. You can also apply standard picture formats to this floating reference image.

Conclusion

Congratulations! You have learned how to insert pictures as cell values in Excel and use them in formulas. By employing functions like XLOOKUP, you can retrieve pictures based on specific criteria. Furthermore, you can adjust the visibility of pictures by changing cell dimensions or creating floating picture references. With this newfound knowledge, you can enhance the visual appeal of your Excel worksheets while making them dynamic and interactive.

In the next post in the series, we’ll talk about how to insert online pictures into your workbooks based on their URL. If I can provide any additional information about this post, please post a comment below … thanks!

Sample file