Sep
16

Excel IMAGE function: quick way to insert picture in cell

by | updated on

Learn a new amazingly simple way to insert a picture into a cell by using the IMAGE function.

Microsoft Excel users have inserted pictures into worksheets for years, but that required quite a lot of effort and patience. Now, that's finally over with. With the newly introduced IMAGE function, you can insert a picture in a cell with a simple formula, place images within Excel tables, move, copy, resize, sort and filter cells with pictures just like normal cells. Instead of floating on top of a spreadsheet, your images are now its integral part.

Excel IMAGE function

The IMAGE function in Excel is designed to insert pictures into cells from a URL. The following file formats are supported: BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP.

The function takes a total of 5 arguments, of which only the first one is required.

IMAGE(source, [alt_text], [sizing], [height], [width])

Where:

Source (required) - the URL path to the image file that uses the "https" protocol. Can be supplied in the form of a text string enclosed in double quotes or as a reference to the cell containing the URL.

Alt_text (optional) - the alternative text describing the picture.

Sizing (optional) - defines the image dimensions. Can be one of these values:

  • 0 (default) - fit the picture in the cell maintaining its aspect ratio.
  • 1 - fill the cell with the image ignoring its aspect ratio.
  • 2 - keep the original image size, even if it goes beyond the cell boundary.
  • 3 - set the image's height and width.

Height (optional) - the image height in pixels.

Width (optional) - the image width in pixels. Excel IMAGE function

IMAGE function availability

IMAGE is a new function, which is currently available only in the Office Insider Beta channel to Microsoft 365 users for Windows, Mac and Android.

Basic IMAGE formula in Excel

To create an IMAGE formula in its simplest form, it is sufficient to supply only the 1st argument that specifies the URL to the image file. Please remember that only HTTPS addresses are allowed and not HTTP. A supplied URL should be enclosed in double quotes just like a regular text string. Optionally, in the 2nd argument, you can define an alternative text describing the image.

For example:

=IMAGE("https://cdn.ablebits.com/_img-blog/image-function/items/umbrella.png", "umbrella")

Omitting or setting the 3rd argument to 0 forces the image to fit into the cell, maintaining the width to height ratio. The image will adjust automatically when the cell is resized:

When you hover over the cell with an IMAGE formula, the tooltip pops out. The minimum size of the tooltip pane is preset. To make it bigger, drag the lower-right corner of the pane like shown below. Hover over the cell with an IMAGE formula to see the tooltip.

To fill the whole cell with an image, set the 3rd argument to 1. For example:

=IMAGE("https://cdn.ablebits.com/_img-blog/image-function/items/water.jpg", "ocean", 1)

Normally, this works nicely for abstract arts images that look well with almost any width-to-height ratio. Fill the whole cell with an image.

If you decide to set the image's height and width (4th and 5th argument, respectively), make sure your cell is big enough to accommodate the original size picture. If not, only part of the image will be visible.

Once the picture is inserted, you can get it copied to another cell by simply copying the formula. Or you can reference a cell with an IMAGE formula just like any other cell in your worksheet. For example, to copy a picture from C4 to D4, enter the formula =C4 in D4.

How to insert pictures in Excel cells - formula examples

Introducing the IMAGE function in Excel has "unlocked" many new scenarios that were previously impossible or highly complicated. Below you will find a couple of such examples.

How to make a product list with pictures in Excel

With the IMAGE function, creating a product list with pictures in Excel becomes incredibly easy. The steps are:

  1. Make a new product list in your worksheet. Or import an existing one from an external database as a csv file. Or use a product inventory template available in Excel.
  2. Upload the product images to some folder on your website.
  3. Construct the IMAGE formula for the first item and enter it in the topmost cell. In the formula, only the first argument (source) needs to be defined. The second argument (alt_text) is optional.
  4. Copy the formula across the below cells in the Image column.
  5. In each IMAGE formula, change the file name and the alternative text if you've supplied it. As all the pictures were uploaded to the same folder, this is the only change that needs to be made.

In this example, the below formula goes to E3:

=IMAGE("https://cdn.ablebits.com/_img-blog/image-function/items/boots.jpg", "Wellington boots")

As a result, we've got the following product list with pictures in Excel: A product list with pictures in Excel

How to return an image based on another cell value

For this example, we are going to create a drop-down list of items and extract a related image into a neighboring cell. When a new item is selected from the dropdown, the corresponding picture will appear next to it.

  1. As we aim at a dynamic dropdown that expands automatically when new items are added, our first step is to convert the dataset to an Excel table. The fastest way is by using the Ctrl + T shortcut. Once the table is created, you can give any name you want to it. Ours is named Product_list.
  2. Create two named ranges for the Item and Image columns, not including the column headers:
    • Items referring to =Product_list[ITEM]
    • Images referring to =Product_list[IMAGE]
    Define names for the table columns not including the headers.
  3. With the cell for the dropdown selected, navigate to the Data tab > Date Tools group, click Data Validation, and configure the dropdown list based on an Excel name. In our case, =Items is used for Source. Create a dropdown list.
  4. In the cell designated for an image, enter the following XLOOKUP formula:

    =XLOOKUP(A2, Product_list[ITEM], Product_list[IMAGE])

    Where A2 (lookup_value) is the dropdown cell.

    As we look up in a table, the formula uses structured references such as:

    • Lookup_array - Product_list[ITEM] which says to search for the lookup value in the column named ITEM.
    • Return_array - Product_list[IMAGE]) which says to return a match from the column named IMAGE.

    The result will look something like this: Build an XLOOKUP formula to return a matching image.

And here's our dropdown list with related pictures in action - as soon as an item is selected in A2, its image is immediately displayed in B2:
A dropdown list with related images

In earlier Excel versions, there was no way to add pictures to a drop down list. The IMAGE function has changed this. Now, you can make a dropdown of pictures in 4 quick steps:

  1. Start with defining the two names for your dataset. In our case, the names are:
    • Product_list - the source table (A10:E20 in the screenshot below).
    • Images - refers to the IMAGE column in the table, not including the header.

    For detailed instructions, please see How to define a name in Excel.

  2. For each IMAGE formula, configure the alt_text argument exactly as you want the alternative text to appear in the dropdown.
  3. In A2, make a drop-down list with Source referring to =Images. Make a dropdown list of images.
  4. Additionally, you can retrieve more information about the selected item with the help of these formulas:

    Get the item name:

    =XLOOKUP($A$2, Product_list[IMAGE], Product_list[ITEM])

    Pull the quantity:

    =XLOOKUP($A$2, Product_list[IMAGE], Product_list[QTY])

    Extract the cost:

    =XLOOKUP($A$2, Product_list[IMAGE], Product_list[COST])

As the source data is in a table, the references use a combination of the table and column names. Learn more about table references.

The result is shown in the screenshot: A dropdown list with pictures in Excel.

Excel IMAGE function known issues and limitations

Currently, the IMAGE function is in the beta testing stage, so having a few issues is normal and expected :)

  • Only images saved on external "https" websites can be used.
  • Pictures saved on OneDrive, SharePoint and local networks are not supported.
  • If the website where the image file is stored requires authentication, the image will not render.
  • Switching between Windows and Mac platforms may cause issues with image rendering.
  • While the GIF file format is supported, it is displayed in a cell as a static image.

That's how you can insert a picture in a cell using the IMAGE function. I thank you for reading and hope to see you on our blog next week!

Practice workbook

Excel IMAGE function - formula examples (.xlsx file)

Table of contents