Watch Video – How to Insert an Image in a Cell in Google Sheets
Normally, the way people insert an image in a cell in Google Sheets is by going to Insert –> Image and the selecting the image or specifying the URL.
The issue with this method is that it treats the image as an object and hence it doesn't move or size with the cells. If you delete the cell which has the image, the image won't get deleted. Similarly, if you resize the cells, the image wouldn't follow suit (something as shown below):
This can prove to be a real pain if you work with a lot of images in Google Sheets.
The Right Way to Insert an Image in a Cell in Google Sheets
Google Sheets can boast of some really cool functions. One such function is IMAGE that can take a URL of an image and insert that image into the cell.
Yes.. you read that right. It's that simple.
Let's first learn about the IMAGE function.
IMAGE(url, [mode], [height], [width])
- url – this is the URL of the image that you want to enter in the cell
- [mode] – this is an optional argument and determines how the image gets fit in the cell. There are four numbers that you can use here:
1resizes the image to fit inside the cell, maintaining aspect ratio. If you leave the mode argument empty, it takes 1 as default.
2stretches or compresses the image to fit inside the cell, ignoring aspect ratio.
3leaves the image at the original size, which may cause cropping.
4allows the specification of a custom size.
- [height] – if you select 4 as the mode argument, then you can use this argument to specify the height of the image in pixels.
- [width] – if you select 4 as the mode argument, then you can use this argument to specify the width of the image in pixels.
Now let's see an example of how it works in the real world.
In the following example, I am using the Amazon logo which has the following URL, at the time of writing this tutorial:
In a cell, where you want an image, enter the following formula:
Note that the URL is within the double quotes.
Here is the result:
Since we didn't specify any mode argument, the image got resized to fit the cell.
Now let's see how different mode argument would work with the same image:
When Mod is 1 – The image gets resized to fit the cell.
When Mod is 2 – stretches to fit the cell and ignores aspect ratio.
When Mod is 3 – displayed at the original size which leads to the image getting cropped from the top.
When Mod is 4 – it takes the height and width values (in pixels) from the user and displays the image accordingly. Note that in the case of mode 4, if you don't provide the height and width argument, it will return an error.
Benefits of Using the IMAGE function
Apart from using the IMAGE function, you can also insert an image in Google Sheets by using the Insert Image option (it's in the Insert tab).
However, there are some benefits of using the IMAGE function over using the Insert Image functionality:
- IMAGE function would keep the image within the cell even when you resize the rows/columns. Also, if you hide the cells, the image would get hidden. On the other hand, Insert Image feature would place the image over the worksheet as a floating object which wouldn't resize or hide automatically.
- Since you can also use the cell reference in the IMAGE function, this makes it dynamic. For example, if I change the URL in the cell, IMAGE function would automatically update to show the image of the updated URL.
Creating a Dynamic Image LOOKUP in Google Sheets
Let's have a look at an example where you can use the IMAGE function in Google Sheets to do an Image Lookup.
Suppose you're creating an interactive dashboard where the user can select the name of a company, and the dashboard gets updated with that company's details.
You can also use the IMAGE function to do an image lookup so that as soon as a company is selected, it's logo would update.
Something as shown below:
Here is how you can create this in Google Sheets:
- Have a list of Company Named along with their image links as shown below.
- In a cell in your interactive dashboard or report, create a drop down using the names of the companies. In this example, the drop down is in cell A1.
- Now in cell B1 (where you want the image lookup functionality), use the following VLOOKUP formula: =IMAGE(VLOOKUP(A1,Sheet2!$A$2:$B$6,2,FALSE),1)
Now when you change the company name in cell A1, the image would automatically update based on it.
Hope you found this tutorial.
Let me know your thoughts in the comments section.
You May Also Like the Following Tutorials: