Pictures in Excel 3: Pictures with URLs

Retrieving and Displaying Online Pictures in Microsoft Excel

Introduction

Hello and welcome to the next post in our Pictures in Excel series, where we are exploring applications of inserting images into cells. In the previous videos, we focused on inserting pictures from our local devices into cells on the worksheet. Today, we will learn how to retrieve online pictures and display them in our Excel workbook. So, let’s dive right in!

Note: this post discusses the IMAGE function, which is used to retrieve an online image from a given URL and is not available in all Excel versions.

Video

Creating a Data Table with Online Picture Links

We will begin by setting up a simple data table that includes the URLs to some online images that we’d like to display in our workbook.

For this illustration, we’ll include a table of my Excel books, and it will have the volume number, title, ISBN, and a URL to the cover image. This table is named Table1.

Our goal is to retrieve the online cover images using the URLs in this table.

Retrieving the URL with XLOOKUP

Now that we have our data table ready, let’s learn how to retrieve the URL from the data table with a lookup function.

When a user enters the given volume number into another worksheet, we’d like Excel to display the related cover image. The other worksheet looks something like this, where the user enters the volume number into C6 and we’d like the cover image displayed in C7:

The first step is to retrieve the correct URL from the data table. You can use your favorite lookup function. In this illustration, I’ll use XLOOKUP.

Note: for this illustration, I’ll use XLOOKUP which is not available in all Excel versions. If your Excel version does not include XLOOKUP, you can use a different lookup function such as VLOOKUP.

Select the cell where you want to display the image, in this case C7, and write the following formula:

=XLOOKUP(C6,Table1[Volume],Table1[Image])

Explanation:

  • XLOOKUP is the lookup function.
  • C6 represents the value we want to find (volume number).
  • Table1[Volume] is the column in the data table where the lookup value (volume number) is located.
  • Table1[Image] is the column in the data table that contains the URL we want to retrieve

Hit Enter, and the formula returns the cover image URL in cell C7:

The final step is to convert the URL into the actual image it points to.

Converting the URL to a Picture with the IMAGE function

All we need to do to convert the URL into the actual picture it points to is wrap the IMAGE function around the XLOOKUP function like this:

=IMAGE(XLOOKUP(C6,Table1[Volume],Table1[Image]))

The IMAGE function takes the URL provided by XLOOKUP, retrieves the online image, and displays it in our worksheet:

Note: the IMAGE function is not available in all Excel versions.

The user can enter a different volume number and Excel updates the worksheet with the corresponding cover image:

Floating the Image and Applying Picture Formats

In addition to displaying the image in the cell as above, we can create an additional copy of the image in the drawing layer of the worksheet. Images in the drawing layer float above the grid, and can be easily resized, repositioned, or formatted. To do so:

  1. Right-click on the image in the cell.
  2. From the context menu, select “Picture in Cell > Create Reference.” This action creates an image that floats above the grid.
  3. Move the floating image to your desired location on the worksheet.
  4. Resize as desired.
  5. Optionally, apply formats from the Picture Format tab

Note: the Picture in Cell command is not available in all Excel versions.

This floating image is a reference back to the picture in the cell, so it will dynamically update along with the picture in the cell.

Conclusion

Congratulations! You have successfully learned how to retrieve online pictures using the IMAGE function and display them in Microsoft Excel. You can now enhance your worksheets by incorporating images from URLs.

If I can provide any additional information, or if you have any suggestions or alternatives, please share by posting a comment below.

Sample file

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

1 Comment

  1. Sajid Hussain on July 16, 2024 at 1:34 am

    Hi, There
    It is not working with me, even your the sample file is not working with me. Its showing error in the cell where picture supposed to appear.

Leave a Comment