Insert Pictures in Excel Automatically Size to Fit Cells

Inserting pictures in Excel is easy but it is hard to fit the pictures into a cell. In this article, I am going to show you a couple of ways to insert pictures in Excel automatically size to fit cells.

To make it clearer, I’m going to use a sheet that represents information about flowers for a shop. There are 3 columns these are Name, Price, and Image.

Sample Datasheet

Workbook to Practice

Ways to Insert Pictures in Excel Automatically Size to Fit Cells

1. How to Insert a Picture in Excel Cell

First, select the cell where you want to insert the image. Now, open Insert tab >> Go to Illustrations >> select Pictures >> then select This Device (if pictures are stored in the same device) 

Insert Pictures

Now from the device where the images are stored select the picture you want to Insert. Finally, click Insert.

Insert Pictures

The picture is inserted but not fitted in the cell. To fit the picture into a cell you must resize the picture.

Picture Inserted

Read More: Excel VBA: Insert Picture from Folder (3 Methods)

2. Resizing a Picture

I. Using Keyboard Shortcut to Resize

Now select the picture you want to resize.

Picture Inserted

Hold the ALT key and drag the picture until it fits into the cell. ALT key fits the picture into the entire cell.

Or you can use the SHIFT key to resize the picture the just place it into a cell.

Fitted into a cell using ALT

II. Using Ribbon to Resize

Select the picture you want to resize then go to Picture Format.

Using Ribbon to resize picture

Now decrease or increase the Height and Width from the Picture Format.

I decreased both Height and Width to fit it into a cell.

Resized the picture using ribbon

3. Automatically Fit Pictures in Cell with VBA

First, open the Developer tab >> then select Visual Basic.

Using VBA to AutoFit pictures

Now, a new window of Microsoft Visual Basic for Applications will appear.

Then, open Insert tab >> then select Module.

Visual Basic Application

Here, the Module is open.

Visual Basic Module

Now, write the code to AutoFit pictures in the module.

Public Sub AutoFitPic()

On Error GoTo NOT_SHAPE

Dim PicWtoHRatio As Single

Dim CellWtoHRatio As Single

With Selection

PicWtoHRatio = .Width / .Height

End With

With Selection.TopLeftCell

CellWtoHRatio = .Width / .RowHeight

End With

Select Case PicWtoHRatio / CellWtoHRatio

Case Is > 1

With Selection

.Width = .TopLeftCell.Width

.Height = .Width / PicWtoHRatio

End With

Case Else

With Selection

.Height = .TopLeftCell.RowHeight

.Width = .Height * PicWtoHRatio

End With

End Select

With Selection

.Top = .TopLeftCell.Top

.Left = .TopLeftCell.Left

End With

Exit Sub

NOT_SHAPE:

MsgBox "Select a picture before running this macro."

End Sub

Writing VB code to AutoFit pictures

After that, Save the code and go back to the worksheet. Then, open Insert tab >> Go to Illustrations >> select Pictures >> then select This Device

Inserting images to apply VBA

Now from the device where the images are stored select the picture you want to Insert. Finally, click Insert.

Inserting Pictures

The picture will be inserted into the selected cell.

Picture Inserted

Now open View tab >> then go to Macros >> select View Macros.

Using Macros to run VBA

Now, a dialog box will pop up. It shows the saved Macro name. Now select the AutoFitPic and the worksheet where I want to apply Macros. Finally, click Run.

Macros to run

The inserted picture will AutoFit into the cell.

Picture is AutoFitted

After inserting the rest of the pictures and by running the AutoFitPic Macro fitted those pictures into their respective cells.

Applied VBA to the rest of the pictures

Lock the Picture with the Cell in Excel

Picture locking is needed when we want to perform any functions with a sheet where pictures are inserted.

To lock a picture, select the picture then right-click on the right side of the mouse. Then select Format Picture

Locking Pictures in Excel

From Format Picture >> select Size & Properties >> then select Properties. Mark on the option Move and size with cells.

Locking Pictures in Excel using Format picture

Followed the Format Picture for the rest of the pictures to perform Filter.

Locked Pictures

First, open Data tab >> then select Filter.

Using Filter in locked pictures

After applying Filter now select any column header then right-click on the mouse to see the Sorting options.

I selected Cherry Blossom and Rose.

Performing Sort options

Now it will show the selected rows without overlapping images as I already locked the pictures to Move and size with cells.

Performed Sort without overlapping pictures

Conclusion

In this article, I explained several ways to insert pictures in excel automatically size to fit cells. I hope these different approaches will help you to automatically fit pictures into a cell. For any kind of suggestions, ideas, the feedback you are most welcome. Feel free to comment down below.


Related Articles

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo