Skip to main content

Excel images: Insert, change, delete multiple images or pictures in Excel

In this tutorial, we will talk about how to insert multiple images into cells and resize them to fit the cell size, how to insert image into comment, header or footer, how to insert images from URLs and so on. It also explains how to save, rename, delete multiple images as you need in Excel.

Table of contents:

1. Insert images or pictures into worksheet from computer, web or OneDrive

2. Lock images or pictures into Excel cell

3. Insert multiple images or pictures in Excel

4. Insert multiple images or pictures matching their names into cells

5. Insert or display images or pictures from URLs

6. Insert or display images or pictures from file paths

7. Insert image or picture into a comment

8. Insert image or picture into header or footer

9. Insert same image or picture into multiple worksheets

10. Insert image or picture behind cell contents

11. Insert a range of cells as image or picture format in Excel

12. Insert image or picture based on cell value dynamically

13. Export or save all images or pictures from an Excel file

14. Rename image names in a folder based on list of cells in Excel

15. Enlarge or shrink image when click on it

16. Always float a picture when scrolling the worksheet

17. Delete images or pictures from active sheet / a range of cells


Insert images or pictures into worksheet from computer, web or OneDrive

All Excel versions support to insert images or pictures from computer, but, if you want to insert the images from web or OneDrive, you should have Excel 2013 and later versions.

Insert images or pictures from computer

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:


Insert images or pictures from web or OneDrive

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.

Tips:

1. To search some specific images, you can filter the found images by size, type, layout or color as you need.

2. Besides inserting the images from Bing search, you can also insert pictures stored on your OneDrive. In the below window, click OneDrive at the left bottom of the window, and then select the images you want to insert.


Lock images or pictures into Excel cell

Normally, after inserting the images into cells, when you resize, filter or hide the cells containing the image, the image will not be resized or filtered along with that cell as below demo shown.

In this case, you should lock the picture into the cell, so that it can be resized, filtered or hidden along with the cell, please do with the following steps:

1. Click to select one picture and press Ctrl + A to select all the images in the worksheet.

2. Then, right click one picture, and select Size and Properties from the context menu, see screenshot:

3. In the expanded Format Picture pane, under the Properties section, select Move and size with cells option, see screenshot:

4. Then, close the Format Picture pane. Now, when resizing, filtering or hiding the cells, the pictures will also be moved, filtered or hidden.


Insert multiple images or pictures in Excel

Sometimes, you may need to insert multiple pictures into the cells and resize them to fit cell size automatically without resizing and dragging the pictures. Here, I will introduce two ways for solving this job in Excel.

Insert multiple images or pictures and resize them to fit cells with VBA code

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:


Insert multiple images or pictures and resize them to fit cells with a powerful feature

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.

After installing Kutools for Excel, please do with the following steps:

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:


Insert multiple images or pictures matching their names into cells

If you have a list of product names in one column, and now, you need to insert their corresponding pictures into another column as below screenshot shown. Inserting the images, and then drag them to each position one by one will waste lots of time, this section, I will talk about some quick methods.

Insert multiple images or pictures matching their names with VBA code

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:


Insert multiple images or pictures matching their names with a useful feature

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.

After installing Kutools for Excel, please do as this:

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:


Insert or display images or pictures from URLs

Have you ever tried to display or insert the images from a list of URLs? May be there are no other good method for inserting the images directly in Excel, but, you can apply a code or a third-party tool for dealing with it.

Insert or display images or pictures from URLs with VBA code

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:


Insert or display images or pictures from URLs with an amazing feature

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.

After installing Kutools for Excel, please do with the following steps:

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:


Insert or display images or pictures from file paths

If you need to insert the images from file path, here also provides the solutions for you.

Insert or display images or pictures from file paths with VBA code

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:


Insert or display images or pictures from file paths with an amazing feature

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

After installing Kutools for Excel, please do as this:

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:


Insert image or picture into a comment

May be it is easy to insert a picture into a cell, but, sometimes, you may want to insert a picture into a comment box, how do you deal with this task?

1. Select and right click the cell where you want to insert comment, and then choose Insert Comment, see screenshot:

Tips: If you have inserted a comment, please ignore this step.

2. Then hover the cursor on the edges of the comment box, until you see the cursor turns into a four-arrow icon. And then, right click and select Format Comment option, see screenshot:

3. In the popped out Format Comment dialog box, click Colors and Lines tab, and then select Fill Effects from the Color drop down list, see screenshot:

4. Then, in the following Fill Effects dialog box, click Picture tab, and click Select Picture button to choose one picture that you want to insert, see screenshot:

5. And then, click OK > OK, the picture has been inserted into the comment box, see screenshot:


Insert image or picture into header or footer

If you want to insert a picture into the header or footer of your Excel worksheet, the steps in this section can help you, please do as this:

1. Activate the worksheet where you want to insert the image into the header or footer.

2. Then, click Insert > Header & Footer, and you will go to the Header & Footer Tools tab. See screenshot:

3. To insert a picture in the header or footer, please click a left, right or center header or footer box as you need, and then, click Picture under the Design tab, see screenshot:

4. Then, an Insert Pictures window will pop up, please choose the picture you want to add and click Insert. Now, a &[Picture] placeholder will appear in the header or footer box. Click anywhere outside of the header or footer box, the inserted picture will be displayed as below screenshot shown:

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.


Insert same image or picture into multiple worksheets

Sometimes, you may want to insert a logo picture into all worksheets of your workbook, normally, you can apply the Insert function to insert the picture from one sheet to another one by one. Excepting this time-consuming feature, the following VBA code can help you to insert the same picture into the specific cell of all sheets at once. Please do with the below steps:

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

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

VBA code: Insert the same picture into all worksheets:

Sub InsertImagetoallsheets()
'Updateby Extendoffice
    Dim I As Long
    Dim xPath As String
    Dim xShape As Shape
    Dim xRg As Range
    xPath = "C:\Users\AddinTestWin10\Desktop\my images\logo.png"
    If Dir(xPath) = "" Then
        MsgBox "Picture file was not found in path!", vbInformation, "KuTools for Excel"
        Exit Sub
    End If
    For I = 1 To ActiveWorkbook.Sheets.Count
        Set xRg = Sheets(I).Range("A1")
        Set xShape = Sheets(I).Shapes.AddPicture(xPath, True, True, xRg.Left, xRg.Top, xRg.Width, xRg.Height)
    Next
End Sub

Note: In the above code, C:\Users\AddinTestWin10\Desktop\my images\logo.png is the path and name of the picture, and A1 in Range("A1") is the cell where you want to insert the picture, please change them to your need.

3. Then press F5 key to run this code, and the specific picture has been inserted into cell A1 of each worksheet immediately. See below demo:


Insert image or picture behind cell contents

To make the worksheet data more beautiful and attractive, you can use an image as a background. For inserting an image behind the cell contents, here are some methods which can do you a favor.

Insert image or picture behind cell contents with Background feature

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:

Tips: To delete background image, just click Page Layout > Delete Background. See screenshot:


Insert transparent image or picture behind cell contents

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.


Insert transparent image or picture behind cell contents as watermark

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.

After installing Kutools for Excel, please do as this:

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.


Insert a range of cells as image or picture format in Excel

Have you ever tried to copy a range of cells and insert it as image in Excel? This section, I will introduce some quick way for dealing with this job in Excel.

Insert a range of cells as image or picture format with Copy as Picture feature

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.


Insert a range of cells as image or picture format with Camera tool

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:


Insert a range of cells as image or picture format with a useful feature

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.

After installing Kutools for Excel, please do as this:

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:


Insert image or picture based on cell value dynamically

In this section, I will show you how to display a specific image based on a cell value. For example, I want to show the corresponding picture when I select a specific item from a drop down cell, and when I choose another, the picture will be changed dynamically as below demo shown:

Insert image or picture based on cell value dynamically with Named range feature

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:


Insert image or picture based on cell value dynamically with a handy feature

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.

After installing Kutools for Excel, please do with the following steps:

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:


Export or save all images or pictures from an Excel file

If there are multiple images in your workbook, and now, you want to extract and save all the images from the Excel file to a folder. How could you solve this task in Excel?

Export or save all images or pictures from Excel with Save As feature

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.


Export or save all images or pictures from Excel with changing the file extension

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:


Export or save all images or pictures from Excel with a powerful feature

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.

After installing Kutools for Excel, please do with the following steps:

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:


Rename image names in a folder based on list of cells in Excel

If you have a lot of pictures in a folder, and now, you want to rename all these image, how could you solve this job with an easy way in Excel?

In Excel, first, you should list all the old image names into a column, then type the new names, at last, apply a VBA code to replace the old names with the new names at once. Please do with the below steps:

1. Please list all image names into a list of cells, or you can apply the below code to list all image names in a specific folder without manually typing them one by one.

2. Hold down 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: list all image names from a folder

Sub MainList()
'Updateby Extendoffice
On Error Resume Next
Set xRg = Application.InputBox("Please select a cell to put the filenames:", "KuTools For Excel", Selection.Address, , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xRg = xRg(1)
Set Folder = Application.FileDialog(msoFileDialogFolderPicker)
If Folder.Show <> -1 Then Exit Sub
xDir = Folder.SelectedItems(1)
Call ListFilesInFolder(xRg, xDir, True)
End Sub
Sub ListFilesInFolder(ByVal xRg As Range, ByVal xFolderName As String, ByVal xIsSubfolders As Boolean)
Dim xFileSystemObject As Object
Dim xFolder As Object
Dim xSubFolder As Object
Dim xFile As Object
Dim rowIndex As Long
Set xFileSystemObject = CreateObject("Scripting.FileSystemObject")
Set xFolder = xFileSystemObject.GetFolder(xFolderName)
rowIndex = 1
For Each xFile In xFolder.Files
xRg.Formula = xFile.Name
Set xRg = xRg.Offset(rowIndex)
rowIndex = 1
Next xFile
If xIsSubfolders Then
For Each xSubFolder In xFolder.SubFolders
ListFilesInFolder xRg, xSubFolder.Path, True
Next xSubFolder
End If
Set xFile = Nothing
Set xFolder = Nothing
Set xFileSystemObject = Nothing
End Sub

4. Then, press F5 key to run this code, and a prompt box is popped out, please click a cell where to output the image names, see screenshot:

5. Then, click OK, another Browse dialog will pop out, please select the folder contains the pictures you want to list all the image names, see screenshot:

6. Click OK, all the image names in the specific folder have been listed into the cells, see screenshot:

7. Then, type the new names you need into another column as below screenshot shown:

8. And then, you can apply the below code to rename the images, go on pressing ALT + F11 keys to open the Microsoft Visual Basic for Applications Window.

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

VBA code: Rename multiple files in a folder

Sub RenameFiles()
'Updateby Extendoffice
Dim xDir As String
Dim xFile As String
Dim xRow As Long
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
If .Show = -1 Then
    xDir = .SelectedItems(1)
    xFile = Dir(xDir & Application.PathSeparator & "*")
    Do Until xFile = ""
        xRow = 0
        On Error Resume Next
        xRow = Application.Match(xFile, Range("A:A"), 0)
        If xRow > 0 Then
            Name xDir & Application.PathSeparator & xFile As _
            xDir & Application.PathSeparator & Cells(xRow, "B").Value
        End If
        xFile = Dir
    Loop
End If
End With
End Sub

Note: In the above code, A:A indicates the old image names list you want to rename, and B contains the new image names that you want to use, you can change them to your need.

10. Then, press F5 key to run this code, and in the popped out Browse window, select the folder contains the image you want to rename, see screenshot:

11. And then, click OK button, all the image names have been replaced with the new names as below screenshot shown:


Enlarge or shrink image when clicking on it

Supposing, you have inserted a small image into worksheet, now, you want to enlarge the image when clicking on it, and shrink it when clicking again as below demo shown. How could you solve this task in Excel?

To enlarge or shrink the image size when clicking on it, the following code can do you a favor. Please do with the below steps:

1. Right click the image, and choose Assign Macro, see screenshot:

2. Then, in the popped out Assign Macro dialog box, click the New button, see screenshot:

3. In the Microsoft Visual Basic for Applications window, please copy and paste the below code between the Sub and End Sub codes. See screenshot:

Dim shp As Shape
    Dim big As Single, small As Single
    Dim shpDouH As Double, shpDouOriH As Double
    big = 3  
    small = 1
    On Error Resume Next
    Set shp = ActiveSheet.Shapes(Application.Caller)
    With shp
        shpDouH = .Height
        .ScaleHeight 1, msoTrue, msoScaleFromTopLeft
        shpDouOriH = .Height
     
        If Round(shpDouH / shpDouOriH, 2) = big Then
            .ScaleHeight small, msoTrue, msoScaleFromTopLeft
            .ScaleWidth small, msoTrue, msoScaleFromTopLeft
            .ZOrder msoSendToBack
        Else
            .ScaleHeight big, msoTrue, msoScaleFromTopLeft
            .ScaleWidth big, msoTrue, msoScaleFromTopLeft
            .ZOrder msoBringToFront
        End If
    End With

4. Then press the Alt + Q keys to close the Microsoft Visual Basic for Applications window. Now, when you click on the picture, it will be enlarged to the size you specified, and click it again will shrink to the original size as below demo shown:


Always float a picture when scrolling the worksheet

Sometimes, you may want to float a picture on the worksheet screen even though when you scroll up or down the worksheet, so that you can see the picture all the time. In this section, I will introduce a quick trick for you to deal with this job in Excel. Please do with the following steps:

1. First, insert a picture that you want to use.

2. Then, right click the sheet tab which contains the picture you want to keep visible always, and then choose View Code from the context menu. In the opened Microsoft Visual Basic for applications window, copy and paste the below code into the blank Module:

VBA code: Always float a picture in a worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updateby Extendoffice
    Application.ScreenUpdating = False
    If Target.Cells.Count > 1 Then Exit Sub
        With ActiveSheet.Shapes("Picture1")
            .Top = ActiveWindow.VisibleRange.Top + 5
            .Left = ActiveWindow.VisibleRange.Left + ActiveWindow.VisibleRange.Width - .Width - 45
        End With
    Application.ScreenUpdating = False
End Sub

Note: In this code, the Picture 1 is the image name you have inserted, please change it to your need.

3. Then, save and close the code window, now, when scrolling the worksheet and click a cell, the picture will be always kept on the top right corner of the worksheet. See screenshot:


Delete images or pictures from active sheet / a range of cells

Do you have any quick ways for deleting all images from a worksheet, a range of cells or current workbook? This section, I will introduce some methods for solving this job in Excel.

Delete images or pictures from active sheet with VBA code

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.


Delete images or pictures from a range of cells with VBA code

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:


Delete images or pictures from active sheet, selected sheets, all sheets with a handy feature

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.

After installing Kutools for Excel, please do with the following steps:

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.


  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (5)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Geachte,

Zeer interesse pagina.

Wel stel ik me de vraag hoe we na input van de foto's, die foto's in het midden van de cel krijgen.

Bestaat er hier ook een macro voor.?
This comment was minimized by the moderator on the site
Hello,
There are several vba codes in this article, which VBA code do you need to put the pictures in center of the cells?
Thank you!
This comment was minimized by the moderator on the site
Good afternoon,

Very nice page.

Only I tried the VBA of "Insert Multiple Images Or Images That Match Their Names With VBA Code" but unfortunately it doesn't work.

I can finish everything but unfortunately I can't get with pictures in the excel.

Any idea what the problem could be here?
This comment was minimized by the moderator on the site
Hello, janick,The code works well in my workbook, do you select the correct folder contains the images? Please check it?
Thank you!
This comment was minimized by the moderator on the site
This is a great article! I really like how you presented it so easy to understand
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations