Locking images in a cell is important. Generally, we need to work with multiple worksheets in In Excel. But we face the complication when it subjects to moving the data from one sheet to another or sometimes from one workbook to another. Images don’t remain the same size as the previous sheet. In this case, we need to lock the image into a cell in Excel. By locking the image into a cell, one can easily avoid size complexation. In this article, we will learn to lock an image into a cell in Excel.
📁 Download Excel File
To practice please download the excel file from here:
Learn to Lock Image in Excel Cell with These 3 Methods
To learn to lock an image in a cell in Excel, we considered a sample dataset titled Game of Thornes Houses containing two headings named House and Logo. The dataset has 3 columns as well as 11 rows. To insert and lock the logo of House Targaryen, House Stark, House Lannister, House Baratheon, House Arryn, House Grejoy, and House Mormont; we find out 3 catchy approaches. In this article use of Format Picture, Size and Properties features as well as VBA Macro is described. So let’s get started.
1. Insert and Format Picture Feature to Lock Image into Excel Cell
Navigation of the Format Picture feature from the Context Menu is the easiest way to lock an image in a cell of a worksheet in Excel. But to do that, the first thing we need to do is to upload the image to insert in a cell. Please check out the steps to insert and format images.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, select a blank cell C5.
- Secondarily, click to open the drop-down list of Illustrations from the Insert menu of Top Ribbon.
- Thirdly, select This Device to insert pictures from a specific directory.
- Thus, a box containing Insert Picture appears.
- Then, pick up the image to insert into the worksheet of the workbook.
- Next, hit the Insert button.
- Therefore, the logo of Targaryen as an image inserted. Keep in mind that, we need to resize the logo to fit into the cell.
- Similarly, we can insert all the logos in their respective cells for House Targaryen, House Stark, House Lannister, House Baratheon, House Arryn, House Grejoy, and House Mormont.
- Now we need to format the pictures.
- Then, to do that, we first need to select the picture and Right-Click on the mouse to get a context menu list.
- Next, click on the Format Picture option from the context menu list.
- Thus, a side ribbon containing Format Picture appears.
- Further, click on the dropdown of the Properties option and click on the Move and Size with Cells option.
- However, we need to select every picture to format it accordingly.
- Finally, we get the logo of each house that moves and sizes with the cells.
2. Use Size and Properties Feature to Lock Image in Excel Cell
Again, From the Properties feature of the Context Menu list, we will be able to lock an image in a cell. Please follow the necessary procedure below.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, we have to insert images as we showed you earlier.
- Then, select the image and Right-Click on the mouse to get a Context Menu list.
- Next, select Size and Properties from the Context Menu list.
- After that, a side ribbon named Formate Picture appears.
- Further, select “Move and size with the cells” from the Properties option.
- Again, do the similar task one by one to lock images within the cells.
- Therefore, the Image is considered as a logo of the houses fit in a cell. If we move the cells copying or any other manner, they will remain the same and fit into the cell.
3. Implement VBA Macro to Insert and Lock Picture in Excel Cell
Using VBA code we can easily remove apostrophes before numbers in Excel. With the help of the Developer option, you can generate a program on your own. Please follow the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Check first if your Developer option is available or not.
- Secondly, to enable the Developer option, Right-Click on the mouse at the Top Ribbon and select Customize the Ribbon.
- Thirdly, click on Developer and click on the OK button.
- Now Developer mode is on.
- Then, click on the Visual Basic option.
- Next, to insert the VBA code, click on Module from the Insert menu.
- After that, insert the following VBA code in the module.
Code
Sub LockImage()
Range("C5").Select
ActiveSheet.Pictures.Insert("D:\Tanvir\Softeko\Article\262_How to Lock Image in Excel Cell\logo\Targaryen.png").Select
Selection.ShapeRange.Height = 32.4
Selection.Placement = xlMoveAndSize
End Sub
🔨 Code breakdown
👉 Range(“C5”).Select; dictates to select the C5 cell.
👉 ActiveSheet.Pictures.Insert(“D:\Tanvir\Softeko\Article\262_How to Lock Image in Excel Cell\logo\Targaryen.png”).Select; finds the picture from the directory.
👉 Selection.ShapeRange.Height = 32.4; delineates the height of the picture.
👉 Selection.Placement = xlMoveAndSize; signals to select the Move and size with cells option.
- Therefore, the logo of the house Targaryen is inserted at the C5 cell.
- By doing the same approaches you will be able to insert images in every cell.
How to Lock Image in Excel When Filtering
Inserting the images, suppose you need to filter the data of the dataset. There you may lose data. But by applying the 1st and 2nd methods and locking images, we will be able to get respective data accurately.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, we need to lock the images following 1st and 2nd method of this article.
- Secondly, Select the entire dataset B4:B11 range.
- Thirdly, click on the dropdown icon of the Sort and Filter feature from the Data Menu of the Top Ribbon.
- Then, click on the filter option.
- Thus, a dropdown box appears beside each heading.
- Now, click on the drop-down box and get a relevant Context Menu list.
- Say, we only want to get the logo of Stark and Lannister. To do so, just click on the Stark and Targaryen options and hit on the OK button.
- Finally, only House Targaryen and House Stark appear in the filtered dataset.
Insert Pictures Automatically and Size Them to Fit into Cells in Excel
Following the 1st, 2nd, and 3rd approaches one can easily insert and fit the images into a cell in Excel. Though we mentioned the methods earlier, check out the below steps concisely.
- First, Insert pictures from the Insert Menu of the Top Ribbon.
- Then Resize the image to fit into a cell. But resizing is not enough to lock.
- Next, to lock one needs to Format pictures from the Context menu list.
- Further, by selecting Move and size with cells from the Properties option, finally one can fit into cells automatically in Excel.
- Following 1st and 2nd methods of this article, lock the rest images into the respective cells.
📄 Important Notes
🖊️ Enable the Developer menu first to execute VBA code.
🖊️ Remember to select the Move and size with cells option from the Format Picture feature.
📝 Takeaway from This Article
📌 VBA Macro to insert and resize images.
📌 Format Picture feature to lock image in a Cell in Excel.
📌 Properties feature to lock, move and resize automatically.
Conclusion
We demonstrated every possible way to lock an image in a cell in Excel. I hope you enjoyed your learning. Any suggestions, as well as queries, are appreciated. Don’t forget to leave your thoughts in the comment section. For better understanding and new knowledge, don’t