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.
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.
Best Office Productivity Tools
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...
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!