The new Excel IMAGE function enables you to insert images into cells with a formula. From there you can resize them, sort, filter and more.
The IMAGE function supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP file types.
The IMAGE function is currently in the beta testing phase so the function arguments may change from what you see here as feedback from testing comes in.
It’s currently available to Microsoft 365 users on the Insider Channel for Windows, Mac, iOS and Android.
See the Excel IMAGE Function in Action
Enter your email address below to download the sample workbook.
Excel IMAGE Function Syntax
Syntax: =IMAGE( source, [alt_text], [sizing], [height], [width])
Source: The URL path entered in double quotes, or cell reference containing the URL, using an "https" protocol, of the image file. Supported file formats include BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP.
Alt Text: Optional. Alternative text that describes the image for accessibility.
Sizing: Optional. Specifies the image dimensions. There are several possible values:
0 Fit the image in the cell and maintain its aspect ratio.
1 Fill the cell with the image and ignore its aspect ratio.
2 Maintain the original image size, which may exceed the cell boundary.
3 Customize the image size by using the height and width arguments.
Height: Optional. The custom height of the image in pixels.
Width: Optional. The custom width of the image in pixels.
Excel IMAGE Function Examples
https://flagpedia.net hosts images of the flags from the world. I used Power Query to download their JSON file containing the URLs for the flag images and loaded them in a table called FlagURLs where I added the IMAGE function to get the images.
I can sort the Flag column, which actually sorts by the URL.
Filtering the table elegantly hides the images with no hint of them being visible unlike shapes and other objects:
The images automatically adjust when you resize cells up to their maximum size if you omit the size argument or use the default size argument of 0:
[UPDATE] Unfortunately, the tooltip is no longer available.
I can reference cells containing IMAGEs in other formulas, for example I can use SORTBY to extract the flags sorted in order by country name as shown in column E below:
Or use them with data validation and XLOOKUP to return the matching image:
Or correspondingly lookup the image and return the country name from the FlagURLs table:
Images Stored on OneDrive, SharePoint or Google Drive
Fellow MVP, Bernard Obeng Boateng discovered the following methods for using images on OneDrive, SharePoint or Google Drive:
Steps for OneDrive:
Select image in OneDrive or SharePoint (online) > Click on "Embed" > Generate Code and use that link.
Steps for Google Drive:
Select image > Copy Shared link (Anyone can view option). There is a unique ID in that link. Paste it in this part of the URL: "https://drive.google.com/uc?export=view&id=file's ID"
Excel IMAGE Function Limitations
- Currently only images saved on external sites are supported.
- If the URL requires authentication, the IMAGE function will not render.
- Moving between Windows and Mac etc. may result in irregular image rendering.
- Images don’t display in PivotTables, Slicers or Data Validation lists.
- While the IMAGE function supports GIF files, they only appear as a static image:
Hopefully the limitations above will be resolved before the IMAGE function is generally available.