How to Insert Picture in Excel Using Formula (2 Examples)

Inserting images in Excel is not that tough, but it will be a whole lot more convenient if users have the ability to add the images into Excel automatically using different formulas. If you are curious to know how you can insert picture automatically using formulas, then this article may come in handy for you. In this article, we discuss how you can Insert picture in Excel using formula with an elaborate explanation.

How to Insert Picture in Excel Using Formula: 2 Suitable Examples

We presented 2 separate examples in which you can insert picture in Excel using the formulas. Both of these methods are actually about linking named ranges with the images. And in both cases, the user needs to upload images in a separate sheet beforehand.

1. Combining INDEX and MATCH Functions

With the help of INDEX and MATCH functions, we will create a new named range. Then we will link this named range with the required images with a dropdown menu. Switching between those names in the dropdown menu will allow us to choose which image to Insert.

Steps

• In the beginning, we need to add the Logo name and the Logo themselves as part of the dataset, We will use this dataset to demonstrate the process of inserting Pictures in Excel using various formulas.
• We can Insert images usually in the dataset using the Insert tab.
• From the Insert tab, go to the Illustration group. From there, click on the Pictures > This device.

• Then choose your image file and then click OK.
• Repeat this process to add more images to the sheet.

• Now we added all the necessary information to the sheet, we can add a dropdown menu to the sheet.
• For this, we need to open a new sheet where we are going to add the dropdown list.
• To add a dropdown list, go to Data > Data Tools.
• Then click on Data Validation.

• After clicking the Data Validation, we got a new dialog box. In that dialog box, select List on the Allow dropdown list. And select the range of cell B5:B7 from the image Source sheet as the range of data for the Source dropdown option.
• Click OK after this.

• After clicking OK, we will see that there is a dropdown menu in the newly created sheet.
• You can switch between the image names and change the image name entry in cell C4.

• Next, we need to define the Name.
• From the Formulas tab, click on the Define Name dropdown menu.
• Then click on the Define Name.

• In the Edit Name dialog box, set the Name to Logo.
• Then in the Refers to range box, enter the following formula.

• Click OK after this.

• Next, select cell C5 in the Image Source and then press Ctrl+C.

• Then go back to the Logo Selection sheet where we going to put the Images.
• Then select cell C5 and then from the Home tab, click on the Paste drop-down menu.
• And then from the drop-down menu, click on the Linked Picture.
• Doing this will paste the copied images into cell C5.

• Now we have the first image from the image source sheet in the Logo Selection sheet. You can choose any image and copy and paste it to the Logo Selection sheet. It doesn’t have to be the first image.
• You can resize the image according to your requirements.

• Select the image and then in the formula bar, enter

• This logo is the one that was created and used in the Edit Name dialog box.

• Now as you cycle through the dropdown menu, you can observe that the images are also changing accordingly.

• And this is how we insert picture in excel using formula.

Read More: How to Insert Picture in Excel Cell with Text

2. Creating Dashboard Using Images

We will use functions like VLOOKUP, INDEX, and MATCH function to organize and create named ranges in Excel. And then compile the images and the other information all together in a Dashboard.

Steps

• In the beginning, we need to have the necessary information about the person’s name, age, hometown, and country.
• We will compile them all together with the image.

• At the same time, we added the images that we are going to compile with the information.
• And these images have to be added to a separate sheet.

• Now we added all the necessary information to the sheet, we can add a dropdown menu to the sheet.
• For this, we need to open a new sheet where we are going to add the dropdown list.
• To add a dropdown list, go to Data > Data Tools.
• Then click on Data Validation.

• After clicking the Data Validation, we got a new dialog box. In that dialog box, select List on the Allow dropdown option. And select the range of cell B5:B7 from the image sheet as the range of data for the Source dropdown option.
• Click OK after this.

• After clicking OK, we will see that there is a dropdown menu in the newly created sheet.
• You can switch between the Person Name and change the Image Name entry in cell C4.
• Next, we will add some formulas in the cells D7, D8, and D9.
• Select cell D6 and enter the following formula:

• Select cell D7 and enter the following formula:

• Select cell D6 and enter the following formula:

• Next, we need to define the name.
• From the Formulas tab, click on the Define Name.

• In the Edit Name dialog box, set the Name to Pictures.
• Then in the Refers to range box, enter the following formula:

• Click OK after this.

• Now return to the sheet where all the image files are already inserted.
• Next, select cell C5 in the Image sheet and then press Ctrl+C to copy the first image on the sheet.

• Then go back to the Dashboard sheet where we actually going to put the Images.
• Then select cell C5 and then from the Home tab, click on the Paste drop-down menu in the Home tab.
• And then from the drop-down menu, click on the Linked Picture.
• Doing this will paste the copied image into cell C5.

• Now we have the first image from the Image sheet in the Dashboard sheet. You can choose any image and copy and paste it to the Dashboard sheet. It doesn’t have to be the first image.
• You can resize the image according to your requirements.
• Select the image and then in the formula bar, enter

• These Pictures is the one that was created in the Edit Name dialog box in the earlier stage.

• After that, as you change the Person Name from the drop-down menu, the Image, Age, Hometown, and Country everything will be changed with the Person Name.

In this way, you can insert any picture using formula in Excel. Try these methods on your worksheet.

Read More: How to Insert Picture in Excel Cell Automatically

How to Insert Picture Using VBA Code in Excel

Using a simple Macro can drastically reduce the time required for inserting images into Excel automatically.

Steps

• We need to add images on the sheet automatically using the VBA code. The VBA code will add images individually to the sheet. For every image, we need to customize our code a bit.
• The set-up for the VBA here is such that, every time Yes is typed into a cell, the image will be uploaded into the specified cell and from the specified folder mentioned in the code.

• Right-click on the sheet name and then from the context menu, click on the View Code.

• Then in the module window, paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Address <> "$E$5" Then Exit Sub
If Target.Value = "Yes" Then
Application.ScreenUpdating = False
Range("C6").Select
ActiveSheet.Pictures.Insert( _
"E:\Onedrive - Softeko\OneDrive\Softeko\5025_62-0071_Rubayed Razib_how to insert picture in excel using formula\Exceldemy-logo-small.jpg").Select
Selection.Name = "PictureName"
Range("A2").Select
Application.ScreenUpdating = True
Else
On Error Resume Next
ActiveSheet.Shapes("PictureName").Delete
End If
End Sub

• In the code module, we can see the link to the file saved on the computer.
• Users need to modify the link each time they intend to add a new file.
• Then save the module and then close it.
• Type Yes again in cell E5 and press enter.
• You will see the image file is now added to the sheet.
• After some modifications, the image file will look like this.

• Repeat the same process for the other logos and for each case, modify the code accordingly.
• The final result with all the images altogether will look like the below image.

• And this is how we insert picture in excel using VBA Macro.

Download Practice Workbook

Download this practice workbook below.

Conclusion

Here, we presented 2 possible and reliable ways to insert picture in Excel using formula with detailed explanations. We also added a VBA Macro, using which can help you to add images in Excel more efficiently.

For this problem, a workbook is available for download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section.

Related Articles

• How to Link Picture to Cell Value in Excel
• How to Insert Pictures Automatically Size to Fit Cells in Excel
• How to Insert Image in Excel Cell as Attachment
• How to Lock Image in Excel Cell
• How to Insert a Picture in Excel Header
• How to Insert Multiple Pictures at Once in Excel
• How to Insert Clipart in Excel
  

<< Go Back to Excel Insert Pictures | Learn Excel