Buzz
When adding a few images to Excel, say 1 or 2, you can do it manually through the Insert menu. However, when dealing with extensive data sheets requiring more than 10 images, manual insertion becomes time-consuming and laborious. The practical solution is to batch insert images into Excel, adding multiple images at once for a more efficient approach.
Learn how to insert multiple images simultaneously in Excel with this guide, applicable to various Excel versions including 2019, 2016, 2013, and 2010.
Guidelines for Batch Image Insertion in Excel
In this article, we'll demonstrate how to insert multiple images in Excel 2019, with similar steps applicable to older versions like Excel 2016, 2013, and 2010.
- Explore further on updating and installing Office 2019 here.
Step 1: Start by placing all the images you want to insert into Excel in a single folder. Then, copy the folder's path and make a note of it to avoid forgetting. You can use Notepad to save and keep track of this copied path.
Note: It is recommended to display the format of images for ease of execution and standardization. If you're unsure how to show image file extensions, check the guide on How to Show/Hide Folders and Files in Windows 7/8/10 here.
Step 2: Switch to the Excel file where you need to insert multiple images. Create an additional column corresponding to the image names. Ensure that the image name column matches exactly with the names of the images you want to insert into Excel, including the image format, as illustrated below.
Next, paste the copied folder path into any cell in the Excel file. In the example below, we pasted it into cell H1.
Step 3: In the column between the two tables, labeled as the image link, enter the formula
= image path&'\'&VLOOKUP(product name; corresponding image name values;2;FALSE)
Afterward, press Enter to get the result. Please note that for image path values and corresponding product names, you need to use the F4 key (if your laptop uses the function key, press Fn + F4) to lock the formula.
In the data table below, the formula you enter will be =$H$1&'\'&VLOOKUP(B2,$F$2:$G$4,2,FALSE)
Step 4: The computed result of the function will be the path to the image folder along with the name and extension of the image.
Next, execute the formula copy and drag it down to the cells below to get similar results.
Step 5: Download the Macros file InsertImageAutomatically.xlsm on Mytour HERE and open the file.
Step 6: Go back to the Excel file where you want to insert multiple images at once, batch insert images -> click on the Developer tab and select Macros as shown in the image below.
A Macros dialog box will appear as shown below. Click on the file name InsertImageAutomatically.xlsm!InsertImage -> then click the Run button to execute the file.
Step 7: Immediately, the Input window will appear prompting you to enter the range containing image links - meaning you'll enter the path to scan all cells with image paths along with their names -> select the range -> click OK.
After clicking OK, you will see another window. Here, you'll enter the location where the batch-inserted images in Excel will be placed. In the example below, we will insert images into column C (image column) -> after entering, click OK again.
A prompt will appear asking if you want to delete old images -> click Yes to confirm inserting images into cells.
The result is a series of images will be inserted into Excel cells simultaneously, as illustrated below.
Step 8: At this stage, you simply need to delete columns containing unnecessary information or hide them.
The final complete result is an Excel file with images automatically inserted, and the image sizes adjusted to fit the cells as well as automatically centered within the cells.
So, after this article, you should now know how to batch insert images into Excel, adding multiple images at once. If your Excel file involves inserting a small number of images, you can use the normal Insert method. However, for inserting numerous images, the batch insertion method introduced by Mytour above is the optimal solution and one you should choose and use.
Additionally, if you need to insert images into a comment box in Excel or a comment row but don't know how, check out this method on Mytour! In this trick, we provide detailed steps for you to follow. Review this article for more useful knowledge when working with Excel.
