Skip to main content

How to resize pictures to fit cells in Excel?

Author: Xiaoyang Last Modified: 2014-09-02

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.

Resize a selected picture to fit a single cell with VB Macro

Resize inserted pictures to fit cells with Kutools for Excel


arrow blue right bubble Resize a selected picture to fit a single cell with VB Macro

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.

doc-resize-pictures-to-fit-cells1

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:

doc-resize-pictures-to-fit-cells2

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.


arrow blue right bubble Resize inserted pictures to fit cells with Kutools for Excel

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.

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.

Step 1: Select the range cells you want to insert pictures and click the Enterprise >Import/Export > Import Pictures. See the following screenshot:

doc-resize-picture-to-fit-cell-1

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:

doc-resize-picture-to-fit-cell-2

Step 3: Check Matching cell size option. Then click Ok > Import to close dialogs. See screenshot:

doc-resize-picture-to-fit-cell-3

Now you can see the pictures are inserted to fit the cells.

doc-resize-picture-to-fit-cell-4

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.


Related Article:

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

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • 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!
Comments (15)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
La macro posiziona l'immagine "Immagine 2" al centro della cella ("D5"), anche se questa è unita ad altre celle. Spero possa esservi utile

Sub ImpFoto()
Dim hcell As Single ' altezza cella
Dim Lcell As Single ' larghezza cella
Dim hfoto As Single ' altezza foto
Dim Lfoto As Single ' larghezza foto
Dim Rfoto As Single ' rapporto foto h/L

Range("D5").Select

hcell = Selection.Height
Lcell = Selection.Width
hfoto = ActiveSheet.Shapes("Immagine 2").Height
Lfoto = ActiveSheet.Shapes("Immagine 2").Width
Rfoto = hfoto / Lfoto

If hfoto < Lfoto Then
Lfoto = Lcell - 10
hfoto = Lfoto * Rfoto

If hfoto > hcell Then
hfoto = hcell - 3
Lfoto = hfoto / Rfoto
End If

ActiveSheet.Shapes("Immagine 2").Height = hfoto
ActiveSheet.Shapes("Immagine 2").Width = Lfoto
Else
hfoto = hcell - 10
Lfoto = hfoto / Rfoto
ActiveSheet.Shapes("Immagine 2").Height = hfoto
ActiveSheet.Shapes("Immagine 2").Width = Lfoto
End If

Range("D5").Select

With ActiveSheet.Shapes("Immagine 2")
.Top = Selection.Top + (Selection.Height - .Height) / 2
.Left = Selection.Left + (Selection.Width - .Width) / 2
End With
End Sub
This comment was minimized by the moderator on the site
Thank you, it really works!
This comment was minimized by the moderator on the site
Hi, Need this code disabling the lock aspect ratio
This comment was minimized by the moderator on the site
Hi..
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.
This comment was minimized by the moderator on the site
Brilliant! Works great! Just what I was looking for! Thank you!
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations