How to resize pictures to fit cells in Excel?
Normally inserted pictures floats over cells, and most of time one pictures covers a lot of cells. If a worksheet has a large number of pictures, you may want to put each picture into a single cell. But how to deal with it? Actually, there are several tricky ways to help us resize pictures to fit cells' size quickly.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
We can apply VB macros to resize a selected picture to fit a single cell, but not manually resize a picture's width and height. You can do it with following steps:
Step 1: Insert the pictures in to a worksheet, and select a picture that you will resize it to fit a single cell.
Step 2: Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
Step 3: Click Insert > Module, and paste the following macro in the Module Window.
VBA: Resize a selected picture to fit a cell.
Public Sub FitPic() 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
Step 4: Press the F5 key to run this macro.
Step 5: Repeat the steps above to resize other pictures to fit single cell.
Now all inserted pictures are resized and fits into cells. See the following screenshot:
Note: You can resize only one picture one time. If you select more than one picture one time, a warning dialog box will pop up.
If you have Kutools for Excel installed, you can apply its Import Pictures tool to resize all pictures to fit cells or with fixed height during inserting pictures.
Step 1: Select the range cells you want to insert pictures and click the Enterprise >Import/Export > Import Pictures. See the following screenshot:
Step 2: In the Import Pictures dialog box, select the inserting order in the drop-down list, and click Add button to add pictures you need from file or folder, then click Size button. See screenshot:
Step 3: Check Matching cell size option. Then click Ok > Import to close dialogs. See screenshot:
Now you can see the pictures are inserted to fit the cells.
Note: You also can specify the cell height and width in step 3 to resize the pictures.
If you want to know more about this Import Picture feature, please visit here.
You are guest
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 2 months agoHi, Need this code disabling the lock aspect ratio
To post as a guest, your comment is unpublished.· 2 years agoHi..
perfact solution. But i want that function to work with command button. i need to make a command button to insert and auto resize to cell size. please help.
To post as a guest, your comment is unpublished.· 4 years agoBrilliant! Works great! Just what I was looking for! Thank you!
To post as a guest, your comment is unpublished.· 4 years agoit works if the cells are not merged. unfortunately, I merged some cells and it fits only in the upper left cell which makes the photo very tiny.
To post as a guest, your comment is unpublished.· 4 years agoHi, I really love this macro that makes images fit cells, it really helped me with some work stuff. But does anyone know how to write this code for applescript? I have to use macs and the apple version of excel, Numbers, only uses applescript.
To post as a guest, your comment is unpublished.· 4 years agoThanks it did work but I have about 500 pictures so I don't want to do them one at a time - how do I do them all at the same time please?
To post as a guest, your comment is unpublished.· 5 years agoWorks!!
But i have one question, i want to fit a img in a range of cells merged, how can i do it?
To post as a guest, your comment is unpublished.· 3 years agoPublic Sub FitPic()
'Slight modification to resize into merged cells
On Error GoTo NOT_SHAPE
Dim PicWtoHRatio As Single
Dim CellWtoHRatio As Single
PicWtoHRatio = .Width / .Height
CellWtoHRatio = .MergeArea.Width / .MergeArea.Height
Select Case PicWtoHRatio / CellWtoHRatio
Case Is > 1
.Width = .TopLeftCell.MergeArea.Width
.Height = .Width / PicWtoHRatio
.Height = .TopLeftCell.MergeArea.Height
.Width = .Height * PicWtoHRatio
.Top = .TopLeftCell.MergeArea.Top
.Left = .TopLeftCell.MergeArea.Left
.Placement = xlMoveAndSize
MsgBox "Select a picture before running this macro."
To post as a guest, your comment is unpublished.· 5 years ago[quote name="Marcelo"]Works!!
But i have one question, i want to fit a img in a range of cells merged, how can i do it?[/quote]
Marcelo did you get the updated marco for the merged cell? I am trying to do the same thing.
To post as a guest, your comment is unpublished.· 8 months agoI tried this macro last night and it adjust only the height. The width it doesn't incorrectly. Any Help please?
To post as a guest, your comment is unpublished.· 6 years agoThank you this was perfect!!
To post as a guest, your comment is unpublished.· 6 years agoInstructions for Excel
To post as a guest, your comment is unpublished.· 7 years agoFitPic macro noted above is Sweeeeeet!
I took a bunch of pictures of garage sale items that eventually went to Salvation Army. I documented pictures and values in Excel tax purposes. This macro worked first time! :lol: