Dropdown list showing images

Necessary foreword

If you have Excel 2010 or older, then you can read this article, which described how to implement such a drop-down list using the OFFSET function (OFFSET).

If you have Excel 2013, 2016, 2019 or newer, then the described technique in these versions, unfortunately, no longer works and you need a different approach, which will be discussed later in this article.

Step 1. Preparing the Image Catalog

First we need to create a lookup table that will store information about our objects (goods, people, etc.) and their images. It should look something like this:

When creating, it makes sense to keep a few points in mind:

• If in the future it is planned to add new lines (products), then it is better to immediately arrange such a catalog in the form of a “smart” dynamic table using a keyboard shortcut Ctrl+T or buttons Format as a table tab Home (Home — Format as Table). The name of the created “smart” table can be set on the tab Constructor (Design)– I called it in this example Catalog.
• Pictures must fit completely into the corresponding cells and not go beyond their borders (otherwise they will be cut off). To arrange images after selecting them, it is convenient to use the alignment and distribution commands on the tab Framework (Format). There you can also set the dimensions (height and width) of images to quickly fit them into cells:

  

• In a separate column next to the images (in our example it is called ID) you need to come up with and enter the names of the ranges by which we will subsequently address the pictures. These names must be unique, contain no spaces, and not be similar to cell addresses (A1, B3, etc.)

Step 2: Create Named Ranges for Image Cells

To bulk and quickly assign to cells with images in a column Picture made up names from the column ID use the following trick:

1. Highlight columns ID и Picture (range C2:D7 in our example)
2. Open the tab formula (Formulas)

3. Choosing a team Create from Selection (Create from selection).
4. Check the box In the column on the left (Left column), turn off the rest of the checkboxes and click OK.

You can check the results in Name Manager right there on the tab. formula (Formulas — Name manager).

Step 3. Create a drop-down list of products

Now we need to create a drop-down list with the names of the products, from where the user will later select them. It can be on the same sheet or on a different sheet (but in the same book!).

The technique here is simple:

1. Select a cell for the dropdown list.
2. Select on the tab Data Command Data validation (Data — Data Validation).
3. From drop down list A type (Allow) choose an option List (List) and in the field Source (Source) enter the formula:

=INDIRECT(“Catalog[Name]”)

=INDIRECT(“Directory[Name]”)

Here “Catalog[Name]” is a text (in quotes!) link to the contents of the column with the names of products in our “smart” catalog table, and the INDIRECT function (INDIRECT) needed to turn this text link into a real one.

You have to do this because in the field Source, unfortunately, you cannot write direct links to rows or columns of “smart” tables. However, with the help of the “crutch” in the form of INDIRECT, we successfully bypass this limitation.

Step 4. Pull out the name of the range for the selected product

Here, let’s get the range name from the ID column for the product selected in the dropdown list. The easiest way to do this is with the classical VLOOKUP function (VLOOKUP):

In the English version it will be:

=VLOOKUP(C2;Directory;3;FALSE)

Here:

• C2 – the address of the cell with a drop-down list, the contents of which we are looking for
• Directory – the name of our “smart” table
• 3 – serial number of the ID column in the “smart” table
• FALSE (or 0) – exact search mode

Step 5 Create a Named Range with INDIRECT

As you probably guessed, we will use the contents of cell E2 as a link to the image we need. However, this cannot be done directly, because for Excel, its contents are formally text. To revive a text link and turn it into a real one, we use the already familiar INDIRECT function (INDIRECT). For this:

1. Open Name Manager tab formula and press the button Create (Formulas — Name Manager — Create).
2. Enter any suitable name for the created range, for example Photo.
3. In the Reference enter the formula: =INDIRECT(Sheet2!$E$2)
   

Step 6. Adding an image and linking it to the list

The last step remains:

1. Let’s select any cell with a picture in our “smart” table (for example, D2).
2. On the Advanced tab Home expand dropdown list Copy and choose the option Copy as picture (Home — Copy — Copy as Picture). In the window that then appears, you can select the option Like on screen (As on screen) и Raster (Bitmap):

   

   It is necessary to do it in such a way that not the cell and the picture are copied separately, but the entire contents of the cell together with the picture as a single whole image.

3. Paste the copied in the usual way (for example, through Ctrl+V) next to our dropdown list.
4. For the inserted (and selected) picture, write in the formula bar a link to the previously created named range (= Photo) and click on Enter:

   
   

That, in fact, is all 🙂 You can enjoy the result:

Notes

• Cell C2, where we obtained the name of the image we need with the help of VLOOKUP, can, of course, be hidden.
• In order not to see the ugly frames around the picture, you need to turn off all the borders and remove the fill on the sheet with the catalog. For “smart” tables, this is done on the tab Constructor (Design). You can also turn off the catalog sheet and thin gray grid lines through View – Grid (View — Gridlines).
• If in the future you will add new products and add new images to the catalog, do not forget to give the new cells names through the command Formulas – Create from Selection.