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
Download Workbook
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.
Hello and sorry to sound daft, but how did you use Power Query to grab the JSON file from the flags website?
Thanks!!
Dave
Hi Dave,
I used the JSON file connector for Power Query: Data tab > Get Dat > From File > JSON. If you download the example file for this lesson you can see the query in it.
Mynda
I have been working with “IMAGE()” function and have few things to report.
1) Images referred by a https URL are actually downloaded into the XLSX file (you will notice your file size bloat if you are referring to many photos like me at 500+ photos file size is 67MB”)
2) Once images load into the XLSX file you can actually save it wherever you want, share it with whoever and delete the images at HTTPS location as they are embedded into the XLSX file. The problem in emailing the file may be the file size if you have many photos embedded.
3) It is incorrect perception that the “IMAGE()” function simply refers to images as hot links, this was NOT explained anywhere! Image link is simply used to bring/import the photo into XLSX.
4) “IMAGE()” function can NOT be used for application of product catalogues where the image of the product may change. I have not been able to find any information how linked images can be reloaded/refreshed. This is a massive FAILURE BY DESIGN! Hopefully someone from MS reads this. I wasn’t able to find MS support where I can bring this to their attention.
5) Only images located on HTTPS:// address can be imported. Why is that MS? Why not from a local drive or HTTP:// locations? Is there a security concern or its simply a marketing tool?
UPDATE:
Point No 4) above is no longer true as I have found a workaround for updating images.
If photo has been updated/edited/changed the process to update it in the XLSX is as follows:
a) delete the contents of the cell where the updated photo is supposed to be
b) SAVE the file (this will flush the old photo from the XLSX file)
c) put back the “IMAGE()” code that you deleted in step a) above. This will re-fetch the new photo from the URL provided.
Very helpful, Sasha. Thanks for sharing.
You may like to look into Power BI Organizational Data Types for a solution to your needs.
Hi,
I have an interesting challenge where I have created a, what I thought was, a dynamic catalogue with photos.
I have recently realised that “=image” function actually doesn’t simply point to the images but imports them into the XLSX file, so the XLSX file in my case bloats to 67MB in size.
I have recently updated some images. I can not find a way to get XLSX to “reload” images so they update to the new updated version. The images are cached inside the file and I haven’t found a way to flush the cache so the images reload a fresh version.
Any suggestions?
Sasha
Hi Sasha,
Try pressing F9 to recalculate the workbook and force the formulas to update.
Mynda
Hi Mynda,
I tried F9 and it doesn’t reload the photos.
Its as if when the link is originally set the associated image is downloaded at that point and stored within the XLSX. If then original image changes, XLSX doesn’t care as it has its own copy it keeps referring to. As catalogue photos change from time to time I need to be able to get the XLS to update photos. How can the XLSX cashed photos be refreshed?
Sasha
If you edit the formula (F2) then press Enter, does it get the updated picture? Not ideal, but you could automate this with VBA or Office Scripts.
No, I tried that too.
eg. cell E525 has: =IMAGE(U525,B525,0)
U525 has a URL pointing to the https://…PHOTO1
B255 is just text
If I edit the U525 to point to PHOTO2 the image in E525 cell updates correctly to the new photo. However if I then re enter the original URL that pointed to PHOTO1 into the U525 the image in E525 returns to the original image instead of the image that is actually at the URL location.
I can’t get it to re-fetch the image and update to new image.
This is so simple, yet so annoying at the same time. It should be a simple process of F9 or simply reload the formula, but it doesn’t seem to work.
Are you able to try it?
– Have image at some https:// location
– Place link into a cell
– Put some text description for image into another cell
– Create “=IMAGE(URL,TXT,0)” equivalent line
– Ensure that the image loads properly.
– Go to the https:// location and replace the image with another but with same file name or edit existing image.
– Go back to XLSX and try to refresh the image.
I think that’s by design. I remember my fellow MVPs complaining about it, but I don’t recall a fix for it, sorry.
Hi Sasha. I have a solution to this problem which worked for me 🙂 Hope this is what you’re looking for and that it helps….
Row/Col A1: Image URL ( eg. https://images.wpdurl.com/rotate/2.jpeg )
Row/Col B1: A variable (I use todays date eg 20231214. Dont use spaces or special characters, stick to just letters and numbers only)
Row/Col C1: =IMAGE(CONCAT(A1,”?v=”,B1))
It’s essentially concatenating todays date to the end of the image location (eg. https://images.wpdurl.com/rotate/2.jpeg becomes https://images.wpdurl.com/rotate/2.jpeg?v=20231214)
If I need to refresh the image I can just update the value in B2. With the variable added to the end of the image URL Excel sees it as a new image so gets a fresh copy as soon as the value in column B changes.
The function can be retroactive with older versions?
If I open a file containing images imported with an older Excel version it will be okay or images will get an #ERROR?
The IMAGE function is not backward compatible. The user can open the file in Excel Online if they don’t have a compatible version.
Hi, I’m using the IMAGE function for creating product catalogs.
The problem is that some customers said me that they don’t see the images.
It’s really strange because I’ve tried to open the excel files from many devices and everytime I correctly visualized the images.
When I open the file there’s a warning “Data connections have been blocked”, But I always can see the images imported by IMAGE() function.
the link format for images is this: sample image
It don’t seem to be a blocked content, it’s just a cached image on the website
I suspect the customers are not using Microsoft 365, which is required for this new function. If they have an older version of Excel, then the IMAGE function won’t work.
As I thought, thanks for the explaining.
Hi, my IMAGE function doesn’t work.. i have updated my office version but still.. not working, what may I do? I have 2210 version
Hi Alice, you need to be on the Office Insider beta channel.
You are very kind, really, thank you very very much.
Nevermind, I solved it!
(I closed Excel and reopened it, and then the regular ol’ align features were working. Phew!)
Hi Mynda! Great article. I’m just using =image for the first time (connected to a slicer and some nested ifs! so fun!).
Do you know if it’s possible to align the image within the cell, e.g., to the left, center, or right of a cell?
Thanks.
Glad you like it, Ann! Yes, you can use the text alignment tools on the images 🙂