Insert a Picture Into a Cell Automatically in Excel & Google Sheets

This tutorial will demonstrate how to insert a picture into a cell automatically in Excel and Google Sheets.

 

 

There may be an occasion when you want to automatically change a picture in a cell depending on what a user selects from a drop-down list. This can be done using a change event in VBA, or by using the methods shown below.

Create Data List

The first step in creating the example shown above is to create a list of animal names and images in a dedicated area of your workbook. It can be in a separate workbook from the drop-down list you’ll create, or a location in the same workbook.

 

 

Create Drop-Down List

1. Next, select the cell where you want the drop-down list to go.

 

 

2. In the Ribbon, select Data > Data Tools > Data Validation.

 

 

3. In the Settings tab, select List under Allow, and ensure that Ignore blank and In-cell dropdown are checked. Select the range required as the Source for the drop-down list.

 

 

4. Click OK to add the data validation rule to the selected cell. This creates the first drop down showing the list of animals.

 

 

Create Range Name

The third step in creating a dynamic picture lookup is to create a range name.

1. In the Ribbon, select Formulas > Defined Names > Name Manager, then click New.

 

 

2. Call the Range name AnimalsLookup, then type the following formula in the Refers To box:

=INDEX('Animal List'!$B$2:$B$6,MATCH('Select Animal'!$C$2,'Animal List'!$A$2:$A$6,0))

• • The INDEX part of the formula must refer to the list of pictures that are in the animal list (e.g., B2:B6 in the Animal List sheet).
  • The MATCH part of the formula must match the cell where the drop-down list is located (e.g., C2 in the Select Animal sheet) to the list of animal names in the animal list (e.g., A2:A6 in the Animal List sheet).

3. Click OK to add the Range Name to the workbook.

 

 

Create Linked Image

Now, link the image to the Range Name.

1. In the worksheet or location where the list of pictures is located, click on the cell behind one of the animals and click Copy.

 

 

2. Select the cell where you want the picture to appear, next to the drop-down list. Then in the Ribbon, click Paste > Other Paste Options > Paste Linked Picture.

 

 

The image appears in the cell that has been selected. In the formula bar, you can see a formula linking the picture to the cell in the list of pictures, where it was originally copied from.

 

 

3. To amend the formula and link to the Range Name you created, type the following formula:

=ANIMALSLOOKUP

where AnimalsLookup is the range name we created for the list of animals and pictures in the Animals List sheet.

 

 

The picture will automatically change to whatever you select in the drop-down list. Change the selection to see a new picture appear!

 

 

Insert a Picture Automatically in Google Sheets

The process of linking a picture to a drop-down list is a lot easier in Google Sheets than it is in Excel. This is due to the fact that, unlike Excel, Google Sheets actually stores each image within the individual cell. This means you can simply do a lookup on the range where the names and pictures of the animals are stored.

1. As with Excel, first create a list of animals and insert pictures into the adjacent cells.
2. To insert an image into Google Sheets, position the cursor in the cell where you wish the image to go, and in the Menu, select Insert > Image > Image in Cell.

 

 

3. Browse to the URL of the image required or upload it from a previously saved image. This inserts the image into the selected cell. Repeat the process to create a list of animal names and images.

 

 

4. Next, select the cell where the drop-down list of animal names needs to appear, and in the Menu, select Data validation.

 

 

5. Ensure that List from a Range is selected, then select the range where the animal names are stored e.g., ‘Animal List’! A2: A6.

 

 

6. Click Save to save the drop-down list to the required cell (e.g., C2).

 

 

7. Select cell D2 and type the following VLOOKUP formula:

=VLOOKUP(C2,'Animal List'!A2:B6,2,false)

Select an animal from the list to show the corresponding animal image.

 

 

8. Change the size of the row to expand the image.