Excel IMAGE function - quick way to insert picture in cell with formula

How to make a product list with pictures in Excel

With the IMAGE function, creating a product list with pictures in Excel becomes incredibly easy. The steps are:

1. Make a new product list in your worksheet. Or import an existing one from an external database as a csv file. Or use a product inventory template available in Excel.
2. Upload the product images to some folder on your website.
3. Construct the IMAGE formula for the first item and enter it in the topmost cell. In the formula, only the first argument (source) needs to be defined. The second argument (alt_text) is optional.
4. Copy the formula across the below cells in the Image column.
5. In each IMAGE formula, change the file name and the alternative text if you've supplied it. As all the pictures were uploaded to the same folder, this is the only change that needs to be made.

In this example, the below formula goes to E3:

=IMAGE("https://cdn.ablebits.com/_img-blog/image-function/items/boots.jpg", "Wellington boots")

As a result, we've got the following product list with pictures in Excel:

How to return an image based on another cell value

For this example, we are going to create a drop-down list of items and extract a related image into a neighboring cell. When a new item is selected from the dropdown, the corresponding picture will appear next to it.

1. As we aim at a dynamic dropdown that expands automatically when new items are added, our first step is to convert the dataset to an Excel table. The fastest way is by using the Ctrl + T shortcut. Once the table is created, you can give any name you want to it. Ours is named Product_list.
2. Create two named ranges for the Item and Image columns, not including the column headers:
   • Items referring to =Product_list[ITEM]
   • Images referring to =Product_list[IMAGE]
   
3. With the cell for the dropdown selected, navigate to the Data tab > Date Tools group, click Data Validation, and configure the dropdown list based on an Excel name. In our case, =Items is used for Source.
4. In the cell designated for an image, enter the following XLOOKUP formula:

   =XLOOKUP(A2, Product_list[ITEM], Product_list[IMAGE])

   Where A2 (lookup_value) is the dropdown cell.

   As we look up in a table, the formula uses structured references such as:

   • Lookup_array - Product_list[ITEM] which says to search for the lookup value in the column named ITEM.
   • Return_array - Product_list[IMAGE]) which says to return a match from the column named IMAGE.

   The result will look something like this:

And here's our dropdown list with related pictures in action - as soon as an item is selected in A2, its image is immediately displayed in B2:

How to make a dropdown with pictures in Excel

In earlier Excel versions, there was no way to add pictures to a drop down list. The IMAGE function has changed this. Now, you can make a dropdown of pictures in 4 quick steps:

1. Start with defining the two names for your dataset. In our case, the names are:
   • Product_list - the source table (A10:E20 in the screenshot below).
   • Images - refers to the IMAGE column in the table, not including the header.

   For detailed instructions, please see How to define a name in Excel.

2. For each IMAGE formula, configure the alt_text argument exactly as you want the alternative text to appear in the drop down list.
3. In A2, make a drop down list with Source referring to =Images.
4. Additionally, you can retrieve more information about the selected item with the help of these formulas:

   Get the item name:

   =XLOOKUP($A$2, Product_list[IMAGE], Product_list[ITEM])

   Pull the quantity:

   =XLOOKUP($A$2, Product_list[IMAGE], Product_list[QTY])

   Extract the cost:

   =XLOOKUP($A$2, Product_list[IMAGE], Product_list[COST])

As the source data is in a table, the references use a combination of the table and column names. Learn more about table references.

The resulting drop down with images is shown in the screenshot: