Excel images: Insert, resize, save, delete multiple images or pictures in Excel

It is easy for most of us to insert images stored on computer into the worksheet. Please do as this:

1. In the worksheet, click where you want to insert images.

2. Then, click Insert > Pictures > This Device, see screenshot:

3. In the popped out Insert Picture window, open the folder that contains the pictures, and then hold Ctrl key to select the images that you want to insert, see screenshot:

4. Then, click Insert button, the selected pictures have been inserted into the sheet. Now, you can adjust or resize the images to the cells you need, see screenshot:

If you have Excel 2013 or later versions, you can insert the images from web or One Drive, please do as this:

1. In the worksheet, click where you want to insert images.

2. Then, click Insert > Pictures > Online Pictures, see screenshot:

3. A following window will be displayed. In the Online Pictures section, please type what you are looking for into the textbox, and then press Enter key. All corresponding pictures are searched at once, see screenshot:

4. Then, select the pictures you want, and then click Insert button. After inserting the pictures, please resize or adjust the pictures to the position you need.

The following VBA code can help you to insert multiple images into cells based on the cell size. Please do as this:

1. Adjust the cell size that you want to put the picture, and then select the cells.

2. Then, hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and paste the following macro in the Module window.

VBA code: Insert multiple pictures based on the cell size

Sub InsertPictures()
'Updateby Extendoffice
Dim PicList() As Variant
Dim PicFormat As String
Dim Rng As Range
Dim sShape As Shape
On Error Resume Next
PicList = Application.GetOpenFilename(PicFormat, MultiSelect:=True)
xColIndex = Application.ActiveCell.Column
If IsArray(PicList) Then
    xRowIndex = Application.ActiveCell.Row
    For lLoop = LBound(PicList) To UBound(PicList)
        Set Rng = Cells(xRowIndex, xColIndex)
        Set sShape = ActiveSheet.Shapes.AddPicture(PicList(lLoop), msoFalse, msoCTrue, Rng.Left, Rng.Top, Rng.Width, Rng.Height)
        xRowIndex = xRowIndex + 1
    Next
End If
End Sub

4. Then press the F5 key to run this code. In the Open window, please choose the folder which contains the pictures, then select the images that you want to insert, and then click Open button, and all the selected pictures have been inserted into your selection based on the cell size. See screenshot:

If you are not familiar with the VBA code, here, you can use a power feature – Import Pictures of Kutools for Excel. With this useful feature, you can import multiple images into the cells quickly and easily.

1. Click Kutools Plus > Import & Export > Import Pictures, see screenshot:

2. In the popped out Import Pictures dialog, please do the following operations:

• Specify the picture order from the Import order drop down list, you can choose Fill vertically cell after cell or Fill Horizontally cell after cell;
• Then, click Add button to select the image files or folder to add the pictures into the Picture list;
• Click the Import size button, in the Import Picture Size dialog box, select the Matching cell size option.
• 

3. After finishing the settings, please click Import button, and now, another Import Pictures is popped out, specify the destination cells you will import picture into, see screenshot:

4. Then, click OK button, all the selected images have been inserted to match the size of specific cells as below screenshot shown:

The following VBA code can help you to insert multiple images matched their names quickly, please do as this:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Module window.

VBA code: Insert multiple pictures matching their names

Sub InserPictureByName()
'Updateby Extendoffice
Dim xFDObject As FileDialog
Dim xStrPath, xStrPicPath As String
Dim xRgName, xRgInser, xRg, xRgI As Range
Dim xFNum As Integer
Set xFDObject = Application.FileDialog(msoFileDialogFolderPicker)
With xFDObject
    .Title = "Please select the folder:"
    .InitialFileName = Application.ActiveWorkbook.Path
    .Show
    .AllowMultiSelect = False
End With
On Error Resume Next
xStrPath = ""
xStrPath = xFDObject.SelectedItems.Item(1)
If xStrPath = "" Then
Exit Sub
End If
Set xRgName = Application.InputBox("Please select the cells contain the image name:", "Kutools for Excel", , , , , , 8)
If xRgName Is Nothing Then
    MsgBox "No cells are select, exit operation! ", vbInformation, "Kutools for Excel"
    Exit Sub
End If
Set xRgInser = Application.InputBox("Please select the cells to output the images", "Kutools for Excel", , , , , , 8)
If xRgInser Is Nothing Then
    MsgBox " No cells are select, exit operation.! ", vbInformation, "Kutools for Excel"
    Exit Sub
End If
For xFNum = 1 To xRgName.Count
    Set xRg = xRgName.Item(xFNum)
    Set xRgI = xRgInser.Item(xFNum)
    xStrPicPath = xStrPath & "\" & xRg.Text & ".png"
    If Not Dir(xStrPicPath, vbDirectory) = vbNullString Then
        With xRgI.Parent.Pictures.Insert(xStrPicPath)
            .Left = xRgI.Left
            .Top = xRgI.Top
            .ShapeRange.LockAspectRatio = msoFalse
            .ShapeRange.Height = 60
            .ShapeRange.Width = 60
        End With
    End If
Next
End Sub

Note: In the above code, you can change the image height and width within the scripts “.ShapeRange.Height = 60”, “.ShapeRange.Width = 60” to your need.

3. Then, press F5 key to run this code, and in the opened window, please select the folder that contains the images you want to insert, see screenshot:

4. And then, click OK button, a prompt box is popped out, please select the cells that contain the image names, see screenshot:

5. Go on clicking OK button, and another prompt box is followed, please select the cells that you want to output the images, see screenshot:

6. At last, click OK, and the pictures have been inserted into the cells based on their names at once, see screenshot:

If you have Kutools for Excel, with its Match Import Pictures feature, you can insert the images based on the image names quickly and easily.

1. Click Kutools Plus > Import & Export > Match Import Pictures, see screenshot:

2. In the popped out Match Import Picture dialog box, please do the following operations:

• Specify the picture order from the Import order drop down list, you can choose Fill vertically cell after cell or Fill Horizontally cell after cell;
• Then, from the Match range textbox, select the picture names that you want to insert images based on;
• And then, click Add button to select the image files or folder to add the pictures into the Picture list;
• Next, you should specify the picture size. Please click Import size button, in the Import Picture Size dialog box, select one picture size as you need.
• 

3. After finishing the settings, please click Import button, choose one cell or a list of cells to locate the images from the popped out Match Import Pictures dialog, see screenshot:

4. Then, click OK, and all pictures which the names matched with the cell values have been inserted, see screenshot:

Please apply the following VBA code to insert the images based on a list of URLs.

1. First, adjust the adjacent cell size that you want to locate the images.

2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and paste the following macro in the Module window.

VBA code: Insert pictures from a list of URLs

Sub URLPictureInsert()
'Updateby Extendoffice
Dim Pshp As Shape
Dim xRg As Range
Dim xCol As Long
On Error Resume Next
Application.ScreenUpdating = False
Set Rng = ActiveSheet.Range("A2:A5")
For Each cell In Rng
filenam = cell
ActiveSheet.Pictures.Insert(filenam).Select
Set Pshp = Selection.ShapeRange.Item(1)
If Pshp Is Nothing Then GoTo lab
xCol = cell.Column + 1
Set xRg = Cells(cell.Row, xCol)
With Pshp
.LockAspectRatio = msoFalse
If .Width > xRg.Width Then .Width = xRg.Width * 2 / 3
If .Height > xRg.Height Then .Height = xRg.Height * 2 / 3
.Top = xRg.Top + (xRg.Height - .Height) / 2
.Left = xRg.Left + (xRg.Width - .Width) / 2
End With
lab:
Set Pshp = Nothing
Range("A2").Select
Next
Application.ScreenUpdating = True
End Sub

Notes: In the above code, A2:A5 is the range of cells which contains the URL addresses you want to extract the images, you should change the cell references to your need.

4. Then press F5 key to run this code, the matching pictures will be extracted from the URLs to the adjacent column and placed at the center of cells, see screenshot:

Kutools for Excel has an amazing feature – Insert Pictures from Path (URL). With this utility, the corresponding pictures will be extracted from the URLs fast and easily.

1. Click Kutools > Insert > Insert Pictures from Path(URL), see screenshot:

2. In the popped out Insert Pictures from Path(URL) dialog box, please set the following operations:

• Select the list of URL cells and a range of cells where you want to put the images;
• Specify the size of the images as you need from the Picture Size section.
• 

3. And then, click Ok button, and all the pictures have been extracted from the URLs, see screenshot:

You can apply the following VBA code for inserting the images based on the image file paths.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and then paste the following code in the Module Window.

VBA code: Insert images from a range of file paths

Sub InsertPicFromFilePath()
'Updateby Extendoffice
    Dim xRg As Range
    Dim xCell As Range
    Dim xVal As String
    On Error Resume Next
    Set xRg = Application.InputBox("Please select file path cells:", "KuTools for Excel", Selection.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    For Each xCell In xRg
        xVal = xCell.Value
        If xVal <> "" Then
            ActiveSheet.Shapes.AddPicture xCell.Value, msoFalse, msoTrue, _
            xCell.Offset(0, 1).Left, xCell.Top, xCell.Height, _
            xCell.Height
        End If
    Next
    Application.ScreenUpdating = True
End Sub

3. After inserting the above code, press F5 key to run this code, then a prompt box is popped out, please select the cells of the file path, see screenshot:

4. Then click OK button, all images based on the file paths have been displayed into next column as following screenshot shown:

Kutools for Excel’s Insert Pictures from Path(URL) feature also can help you to display the relative images from the file path.

1. Click Kutools > Insert > Insert Pictures from Path(URL), in the popped out dialog box, please specify the file path range, range for outputting images, image size as you need, see screenshots:

2. After setting the options, click Ok button, the images will be inserted into the selected cells at once, see screenshot:

Tips: Sometimes, you may find that the image overlap with the data area of the worksheet as below screenshot shown. This occurs because the image is too large and it will not be resized to fit the header or footer section box automatically.

To resolve this problem, you just need to resize the image in the header or footer, please do with the following steps:

1. Click in the header or footer section where the image is located, and then click Format Picture under the Design tab, see screenshot:

2. In the Format Picture dialog box, under the Size tab, please adjust the size of the image to fit the header or footer section box, see screenshot:

3. And then, click OK, you will get the result as you need.

1. Open the worksheet where you want to insert a background image.

2. Then, click Page Layout > Background, see screenshot:

3. In the popped out Insert Pictures window, please choose one picture from your computer, website or OneDrive as you need, see screenshot:

4. Now, the image has been inserted into the worksheet as background as below screenshot shown:

With above method, you can not change the transparent of the inserted background image, so, if the color of the background image is heavy, it will not convenient for you to read the data of the worksheet. This section, I will talk about how to insert a transparent picture behind the cells.

1. Click Insert > Shapes, and select a rectangle shape, see screenshot:

2. Then, draw a shape as large as you need in active worksheet.

3. After drawing the shape, a Drawing Tools tab is displayed in the ribbon, and then click Format > Shape Outline > No Outline, see screenshot:

4. And then, right click the shape, and choose Format Shape from the context menu, see screenshot:

5. In the expanded Format Shape pane, under the Fill & Line tab, do the following operations:

• Under the Fill section, select Picture or texture fill option;
• Click Insert button from the Picture source, and choose one picture you want to insert;
• Then, adjust the transparency of the picture to your need from the Transparency section.
• 

6. And then, you will see the shape filled with pictures looks like a background image as above screenshot shown.

Note: After inserting this type of background image, you will not be allowed entering any text within the picture range.

Both above methods have their own limitations, the first method can’t change the image transparency, the second method does not allow entering text into the cells inside the picture area. To solve these problems, Kutools for Excel’s Insert Watermark feature can do you a favor.

1. Click Kutools > Insert > Insert Watermark, see screenshot:

2. In the popped out Insert Watermark dialog box, select Picture Watermark, and then click Select Picture button to choose one image you want to insert, and then specify the image scale and check Washout option, see screenshot:

3. Then, click Ok button, the picture has been inserted into the worksheet as background image.

In Excel, you can use the Copy as Picture function to copy and insert a range of cell as image.

1. Select the range of cells that you want to copy and insert as image.

2. Then, click Home > Copy > Copy as Picture, see screenshot:

3. In the popped out Copy Picture dialog, select Picture from the Format section, see screenshot:

4. Then, click OK button, now, you just need to press Ctrl + V to paste the image to anywhere of the workbook.

In Excel, there is a Camera tool which can hep you to take a photo for your selected data, and then paste it as image. When the original data changes, the copied image will be changed dynamically.

First, you should display the Camera on the ribbon, and then apply it.

1. Click Customize Quick Access Toolbar icon, and then click More Commands option, see screenshot:

2. In the opened Excel Options dialog box, please do the following operations:

• Select All Commands from the Choose commands from drop down;
• Then, scroll to select Camera from the list box;
• And then, click Add button to add this tool to the Customize Quick Access Toolbar;
• At last, click OK to close this dialog box.
• 

3. And the Camera tool has been displayed on the ribbon, see screenshot:

4. Then, select the range of cells that you want an image.

5. And then, click the Camera tool, then go to the worksheet and click anywhere, the data range will be inserted as an image as below demo shown:

If you have Kutools for Excel, with its Resource Library feature, you can save the selected data into the Resource Library, and then use it as image or text for future as you like.

1. Select the data range that you want to insert as image.

2. Then, click Kutools > Navigation, see screenshot:

3. In the expanded Navigation pane, click Resource Library icon, and then click Add selected content to Resource Library icon, see screenshot:

4. In the New Resource Library entry dialog box, specify a name for this item, and then select a group you want to add to. See screenshot:

5. Then, click Add button to add it to Resource Library pane. If you want to insert this range as picture, just need to click a cell to locate the picture, and then find the saved item from the Resource Library pane, and click Insert as > Picture (EMF) from the drop down list of the item. And the data range will be inserted into the sheet as an image, see screenshots:

Normally, in Excel, you can create a dynamic named range, and then use the linked picture for solving this task in Excel.

1. First, you should create two columns which contain the product names and relative images, see screenshot:

2. Then, please create a drop down list for the cell values. Click a cell to output the drop down list, then click Data > Data Validation > Data Validation, see screenshot:

3. In the popped out Data Validation dialog box, under the Settings tab, choose List from the Allow drop down, and then select the cells that you want to create the drop down list from the Source textbox, see screenshot:

4. Click OK, and the drop down list with the cell values has been created, select one item from the drop down list, see screenshot:

5. Then, click Formulas > Define Name, see screenshot:

6. In the New Name dialog box, please do the following operations:

• Enter a name called myimage into the Name text box;
• Then copy and paste this formula: =INDEX(Sheet11!$A$2:$B$6,MATCH(Sheet11!$E$2,Sheet11!$A$2:$A$6,0),2) into the Refers to text box.

  

  Note: In the above formula:

  • Sheet11!$A$2:$B$6: is the worksheet and range which contains the values and pictures you want to use;
  • Sheet11!$E$2: is the cell within the worksheet where you have created the drop down list;
  • Sheet11!$A$2:$A$6: is the list of cells that you are created the drop down list based on;
  • The variable number 2 is the column number containing the images. If your images are in column C, you should enter 3.

7. And then, click OK button, then please copy and paste the corresponding picture based on the cell value in E2 from the original range to the cell F2, see screenshot:

8. Next, please select the picture in cell F2, and then enter this formula =myimage (myimage is the range name that you have created in step 6) into the formula bar, see screenshot:

9. After entering the formula, press Enter key, and now, when you choose one item from the drop down list, its relative picture will be displayed immediately. See screenshot:

Above method may be difficult for most of us, here, I will introduce a handy feature – Picture Drop-down List of Kutools for Excel, with this useful tool, you can create multiple drop down lists with their relative images with only several clicks.

1. First, you should create two columns which contain the product names and relative images in your worksheet.

2. And then, click Kutools > Drop-down List > Picture Drop-down List, see screenshot:

3. In the Picture Drop-down List dialog box, if you have created the columns of cell values and images, please ignore step1, then, select the original data and image range and the output range, see screenshot:

4. And then, click Ok button, the drop down lists with images have been created into the selected cells, and when selecting an item from the drop down cell, its corresponding picture will be displayed at once, see below demo:

Normally, you can use the Save As feature to save all images from the workbook, please do as this:

1. Click File > Save As, in the Save As window, specify a folder to put the exported files, and then choose Web Page(*.htm;*.html) from the Save as type drop down list, see screenshot:

2. And then click Save button, go to the specific folder, you will see two files, one is the HTML format file, and another is a folder which contains the pictures, charts or other objects from the workbook. See screenshot:

3. Then you can drag only the images to another folder as you need.

Note: By using this Save As feature, you will get each picture twice, one is smaller, another is larger, so you can save the higher resolution images as you need.

To save all images from Excel file, you can also change the file extension to RAR. Please do as this:

1. Change the file extension xlsx to rar as below screenshot shown:

2. Then, unzip the file, and you will see 3 folders and 1 file in the folder, see screenshot:

3. Then, click to open the xl folder, and then open the media folder, you will see all the images are stored here without duplicates, see screenshot:

If you have Kutools for Excel, with its Export Graphics feature, you can quickly export some specific objects, such as pictures, charts, shapes to image format as you need.

1. Click Kutools Plus > Import & Export > Export Graphics, see screenshot:

2. In the Export Graphics dialog box, do the following operations:

• Choose Pictures option from the Types drop down list;
• Specify a folder to save your exporting pictures;
• Then click the drop down list from the Export format to choose one format for your pictures.
• 

3. At last, click Ok button, all the images in the current workbook have been saved into the folder you specified, see screenshot:

To delete the pictures from current worksheet, the below VBA code can help you, please do as this:

1. Activate the worksheet that contains the images you want to delete.

2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and paste the following macro in the Module Window.

VBA code: Delete all pictures from current worksheet

Sub DeleteAllPics()
'Updateby Extendoffice
Dim Pic As Object
For Each Pic In ActiveSheet.Pictures
Pic.Delete
Next Pic
End Sub

4. Then, press F5 key to run this code, and all of the pictures in active worksheet are deleted at once.

If you need to delete the images from only a range of cells, here is another simple code can help you.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Module Window.

VBA code: Delete the pictures from a range of cells

Sub DeletePic()
'Updateby Extendoffice
    Dim xPicRg As Range
    Dim xPic As Picture
    Dim xRg As Range
    Application.ScreenUpdating = False
    Set xRg = Range("B4:C6")
    For Each xPic In ActiveSheet.Pictures
        Set xPicRg = Range(xPic.TopLeftCell.Address & ":" & xPic.BottomRightCell.Address)
        If Not Intersect(xRg, xPicRg) Is Nothing Then xPic.Delete
    Next
    Application.ScreenUpdating = True
End Sub

Note: In the above code, B4:C6 is the range that you want to delete the pictures from.

3. Then, press F5 key to run this code, and the pictures in the selected range will be deleted at once, see screenshots:

If you have Kutools for Excel, with its Delete Illustrations & Objects feature, you can quickly delete the images from a worksheet, some selected sheets or whole workbook.

1. Click Kutools > Delete > Delete Illustrations & Objects, see screenshot:

2. In the opened Delete Illustrations & Objects dialog box, check Pictures from the Delete section, and then specify the scope which you want to delete the pictures from under the look in section, see screenshot:

3. Then click Ok button, and all pictures will be deleted from the worksheet, selected worksheets or whole workbook as you specified.