Office hacks: without formulas and VBA tables to display batch images

When the need to display a large number of images in Excel tables, we often use functions to construct formulas or write VBA code to deal with. For most users who are not experienced in using Microsoft Office, it is obviously difficult for them to use functions or write VBA code. In fact, just a clever use of good file and field sorting, as well as object properties to adjust and arrange the skills, between the mouse clicks can quickly make a batch of pictures displayed in the form.

First, check the picture file name in the folder and the need to display the picture in the table with the worksheet which field is consistent (such as in this case, the picture file is named after the ID number in the worksheet), in the worksheet to the field as a keyword to sort the data (ascending or descending order can be); switch to the “Insert” tab, click “Picture → This Device”, open the folder where the picture is stored in the pop-up window, right-click in the blank space of this folder, click “Sort by”, select “Name “, “incremental” (sorting method to be consistent with the way the keywords are sorted in the worksheet) (Figure 1). Select all the images, inserted into the Excel worksheet.


Next, select the first picture located at the bottom of the worksheet, drag and drop it into the first cell where the picture needs to be inserted, adjust its size as needed; select the first picture, switch to the “Picture Tools → Format” tab, write down the height and width of this picture, and then press Ctrl + A to select all Pictures, right click on the picture selection, select “Set Object Format”, in the pop-up “Set Picture Format” panel, select “Size and Properties”, remove the “Lock aspect ratio” in front of the check box, in the “height”, “width” to enter the size of the first picture data. This way the size of all the pictures will be adjusted in line (Figure 2).


Subsequently, select the top of a picture, drag and drop it to the last need to insert a picture of the corresponding cell, press Ctrl + A to select all pictures, in the “Picture Tools ¡ú Format” tab, click “Align ¡ú Left Alignment, Vertical Distribution”. . Thus, the picture will be inserted into the corresponding cell (Figure 3).


Finally, we then restore the data table to the original normal order. Select the “serial number” field for ascending sorting, so that the picture will also follow the corresponding record changes (Figure 4).


Leave a Comment