Use an Excel formula to insert pictures into cells with the IMAGE function

Microsoft recently announced the rollout of the new IMAGE function. It enables us to write a formula that inserts an image directly into a cell. This opens up many new interesting possibilities.

Summary

We have been able to insert images into Excel worksheets for a long time. Historically, these images float above the grid in the drawing layer. Images have been independent of the grid. For many situations, this works great. However, when trying to incorporate pictures dynamically, this method was cumbersome.

Recently, Microsoft announced the availability of a new function, IMAGE. With it, we are able to write a formula that displays a picture in a cell. Since the path to the image is stored in the formula, we are able to easily change the image path and filename dynamically using the power of Excel formulas.

Note: currently, the image must be available via an https URL, meaning, stored on the internet. Currently, the IMAGE function is available to Insiders on the Insiders update channel. Perpetual licenses of Excel won’t receive this enhancement retroactively. Office 365 users should receive the update based on their update channel. Current file formats include PNG, JPG, GIF and other common image types.

Walkthrough

The basic function syntax is:

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

Where:

• source – the URL of the image file
• [alt_text] – optional alternative text provided for accessibility
• [sizing] – optional image dimensions (fit image, fill cell, original size, custom size)
• [height] – optional custom height
• [width] – optional custom width

For example, if the image we wanted to pull into the cell was stored at https://www.domain.com/image.png, we could pull it into a cell with the following formula:

=IMAGE("https://www.domain.com/image.png")

Since this is a formula, we are free to construct the function arguments dynamically, based on other cell values or formulas. For example, we can use concatenation or lookups.

Concatenation

If all of the files are stored in the same place, we can store the file path in a cell and then combine that with the image filename.

For example, we could store the path (https://www.domain.com/) in A1. And if the filename was stored in B1, we could combine them like this:

=IMAGE(A1&B1)

Alternatively, we could store the filenames in a table column. If the filename column was named PicFile, we could write the following formula in a new table column to retrieve all of the images:

=IMAGE(A1&[@PicFile])

Hit Enter and bam:

Lookups

Using a formula with the IMAGE function also enables us to use lookups to construct the function arguments dynamically. For example, we could allow the user to select a Model from a data validation drop-down, and then do a lookup to return the corresponding filename. If the path was stored in A1, the model input cell was C2, and the filename was in the third table column, we could use a formula like this:

=IMAGE(A1&VLOOKUP(C2,Table1,3,0))

That way, when a user selects a specific Model, Excel dynamically retrieves the correct image along with any additional information we may want to present:

As you can see, the IMAGE function opens up many interesting possibilities 🙂

Let me know what you think by posting a comment below … thanks!