Whether you’re a marketer, a data analyst, or a content creator, the power to dynamically extract and display images from the web can significantly enhance your workflows and presentations. This capability becomes particularly valuable when dealing with large datasets or frequently updating information sources, such as sports team logos, product catalogs, or social media profiles.
To illustrate this powerful technique, let’s take a practical example from the world of sports. Imagine you need to display the logos of NFL teams dynamically and efficiently.
Typically, these logos are stored online and follow a predictable URL pattern. By leveraging the IMAGE function, we can automate the process.
Let’s get started:
Understanding the URL Pattern:
Before diving into the extraction process, it’s essential to understand the URL pattern. For instance, consider the URL format for NFL team logos hosted on ESPN:
https://a.espncdn.com/combiner/i?img=/i/teamlogos/nfl/500/dal.png&h=200&w=200
This link will get you the Dallas logo as you can see the PNG mentioned above is of team Dallas (dal).
Notice the team abbreviation part as highlighted. This is the dynamic segment that changes based on the team.
Extracting Images Dynamically:
We’ll use the IMAGE function and use the URL to extract the images into Excel. Which has the following syntax:
The source is the web URL from which the image is pulled, alternate text as the name suggests is an alternative text to refer to the image and the third argument is the sizing.
Let’s quickly look at the different sizing options available in this function.
There are four possible inputs for this:
Option 0 – The default option. This fits the cell and retains the aspect ratio. This is the recommended option to ensure the image is not distorted when row height/column width is modified.
Option 1 – Fill the cell and ignore the aspect ratio (could distort the image). This option is recommended only when the cell and image have the same aspect ratio (eg a perfect square). This is also recommended when there’s a pattern to fill irrespective of aspect ratio.
Option 2 – Retains the original image size. This is NOT a recommended option while working with multiple images of various sizes.
Option 3 – Customize image size by specifying height and width.
Let’s get back to our example. Say, we have all the team names in cells A2 onwards:
In our example, the dynamic parameter is the team abbreviation (e.g., ‘DAL’ for the Dallas Cowboys, ARI for the Arizona Cardinals). This parameter will be used to fetch the correct team logo.
We’ll use the concatenation operator ‘&’ in the URL to insert the dynamic parameter from cell A2. Here’s the formula structure, say in cell B2:
=IMAGE("https://a.espncdn.com/combiner/i?img=/i/teamlogos/nfl/500/"&A2&".png&h=200&w=200")This will fetch the logo against the team abbreviation in cell A2 which is Arizona Cardinals:
Click and drag the formula to extract the logos of all the 32 teams as shown:
Using the IMAGE function and identifying the patterns in your URL, extracting images into Excel is a task of mere seconds. This approach not only saves time but also ensures that your data presentations are always up-to-date with the latest visuals.
Watch our dedicated YouTube video on creating an NFL Grid visual in Excel, check here:
