Shamima
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.
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)Â
Now from the device where the images are stored select the picture you want to Insert. Finally, click Insert.
The picture is inserted but not fitted in the cell. To fit the picture into a cell you must resize the picture.
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.
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.
II. Using Ribbon to Resize
Select the picture you want to resize then go to Picture Format.
Now decrease or increase the Height and Width from the Picture Format.
I decreased both Height and Width to fit it into a cell.
3. Automatically Fit Pictures in Cell with VBA
First, open the Developer tab >> then select Visual Basic.
Now, a new window of Microsoft Visual Basic for Applications will appear.
Then, open Insert tab >> then select Module.
Here, the Module is open.
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
After that, Save the code and go back to the worksheet. Then, open Insert tab >> Go to Illustrations >> select Pictures >> then select This Device
Now from the device where the images are stored select the picture you want to Insert. Finally, click Insert.
The picture will be inserted into the selected cell.
Now open View tab >> then go to Macros >> select View Macros.
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.
The inserted picture will AutoFit into the cell.
After inserting the rest of the pictures and by running the AutoFitPic Macro fitted those pictures into their respective cells.
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
From Format Picture >> select Size & Properties >> then select Properties. Mark on the option Move and size with cells.
Followed the Format Picture for the rest of the pictures to perform Filter.
First, open Data tab >> then select Filter.
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.
Now it will show the selected rows without overlapping images as I already locked the pictures to Move and size with cells.
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.
