Batch Auto-resize pictures to fit cells in Excel – 2 useful ways

When working with images in Excel, aligning them with cells is crucial for a structured and organized presentation. If you frequently insert pictures into Excel and want them to automatically resize to fit cell dimensions. This guide will walk you through the best methods to resize pictures to fit cells efficiently.

Batch Auto-resize pictures to fit cells with VBA code

Batch Auto-resize pictures to fit cells with Kutools for Excel

Resize a selected picture to fit a single cell with VBA code

For users who need to insert multiple images and automatically resize them to fit within specific cells, a VBA macro provides a highly efficient solution. By leveraging VBA, you can streamline the process of importing and adjusting images, ensuring they align perfectly with cell boundaries. This method is particularly useful when handling bulk image imports, as it eliminates the need for manual resizing and positioning.

1. Adjust the cell size that you want to put the picture, and 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: 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.

5. And then click "Open" button, and all the selected pictures have been inserted into your selection based on the cell size. See screenshot:

Batch Auto-resize pictures to fit cells with Kutools for Excel

If you're not familiar with VBA code, don't worry! Kutools for Excel offers a simple and efficient way to batch auto-resize pictures to fit their corresponding cells. This feature is especially useful for users who frequently insert images, such as product lists, reports, or visual data presentations.

In this section, we will walk you through the steps to automatically resize multiple images to fit their respective cells using Kutools for Excel, saving you time and ensuring a neat and organized worksheet.

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

1. Click the "Kutools Plus" > "Import/Export" > "Import Pictures".
   
2. In the "Import Pictures" dialog box, configure the following options:
   • Click "Add" button to select the image files or folder to add the pictures into the Picture list;
   • In the "Import size" drop down, select "Matching cell size";
   • 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";
   • Finally, click "Import" button.
     
3. 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:
   

Conclusion

Both VB Macro and Kutools for Excel offer effective ways to resize pictures to fit cells, but they cater to different user needs and skill levels.

• VBA code is ideal for users who need high customization and are comfortable with programming. It is a cost-effective solution but requires some technical expertise.
• Kutools for Excel is perfect for users who prefer a user-friendly, no-code approach and are willing to invest in a third-party tool for additional features and ease of use.

Ultimately, the choice between the two methods depends on the user's specific requirements, technical skills, and budget. For those who frequently work with pictures in Excel, exploring both options and understanding their strengths and limitations can lead to more efficient and streamlined workflows. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.

Related Article:

How to resize multiple cells to fit pictures above them quickly in Excel?