Watch Video – Insert an Image in Google Sheets
When working with data and collaborating with other people in Google Sheets, you may sometimes need to insert an image in a cell in Google Sheets.
These could be company logos or product images, etc.
You will be quite surprised to know that Google Sheets makes it really easy to add images in a cell quite easily.
In this tutorial, I will show you a couple of methods you can use to insert images within the cells in Google Sheets.
Insert an Image Using the ‘Image in Cell’ option
Apart from the formula, Google Sheets also has another way to insert an image in a cell.
This option is available in the Insert option on the menu.
Below are the steps to insert an image in a cell:
- Select the cell in which you want to insert the image
- Click the Insert option in the menu
- Hover the cursor over the Image option
- Click on the ‘Image in cell’ option
- In the Insert Image dialog box, select the option through which you want to insert the image. You can upload an image, take a snapshot and insert that, use a URL to insert the image, use your albums/Google Drive to insert the image or search and then insert it. In this example, I will use the URL to insert the image
- Enter the image URL from which you want to insert the image in the cell
- Click on Select.
The above steps would insert the specified image in the selected cell.
Note that the image inserted using this method is placed in the cell and it moves and sizes with the cell. Also, if you hide the cell or filter it, the image would stick to the cell and would also get hidden/filtered.
Insert Image in a Cell Using the IMAGE Formula
Yes, there is a formula that you can use to add an image to a cell in Google Sheets.
Through the IMAGE formula, you can easily start the process of adding a picture in their preferred cell.
Syntax of the IMAGE function:
IMAGE (url, [mode], [height], [width])
- url: The link of the image should be included with an HTTPS or HTTP for it to work. It should also be surrounded by double quotes if you’re entering it as text only. You can even refer to a cell that has the URL of your image already.
- [mode] The mode is 1 by default, but you can switch it up. It is entirely up to the user to decide the image size which comes in four sizes.
- Size 1 simply just changes the size of the image to fit in easily inside the cell.
- Size 2 compresses or stretches your picture so that it can easily fit in the cell.
- Size 3 will crop the image so that it fits in the cell without changing the size of the image.
- Size 4 allows the user to choose the size (height/width) of the image.
- [height]: The height option simply indicates how long the picture should be to stay inside the cell and also be completely visible. If you want to use height, make sure your mode is 4.
- [width]: To use the width parameter, once again, your mode should be 4; otherwise, your whole image formula will result in an error. The width option lets the user define how wide the picture should be to stay completely visible in the cell.
Now that you know the syntax of the image function, let’s see some examples of how you can use it to insert an image in a cell in Google Sheets.
In this entire tutorial, I will be using the following URL of the logo of Google to show you how to use the IMAGE function in Google Sheets:
This image URL has been taken from the homepage of Google (source: https://www.google.com/)
Now let’s have a look at some examples.
To insert an image in a cell using the above URL, I can use the below formula in Google Sheets:
The above formula gives the result as shown below:
Since I have not provided any of the optional arguments, it will insert the image and fit the image into the cell.
Now let’s go through all the modes you can use in the IMAGE function and how it will impact the result.
When Mode is 1 – Image Resizes to Fit the Cell Size
The result you get when the mode is specified as 1 would be the same you get when you don’t specify any mode value.
In this mode, the image is resized to fit the cell. And if you change the size of the cell, the image would also resize.
When Mode is 2 – Image Stretches to Fit the cell
When you specify the mode as 2, the image would stretch (in height as well as width) to fit the cell (as shown below).
In this case, the aspect ratio is not maintained and you may end up getting an image that completely fits the cell.
When Mode is 3 – Image Displayed at Original Size
When you specify the mode as 2, the image would be displayed in its original size.
In case the cell height/width are not large enough to accommodate the image, the image would be cropped. And in case the size of the cell is larger than the image size, you will have empty white spaces in the cell (around the image).
When Mode is 4 – Displays the image with the specified Height/Width
When you specify the mode as 4, you also need to specify the height and width of the image in the cell (in pixels). For example, the below formula will insert the Google logo in a cell and make the size as 50 as height and 50 as width.
Benefits of using the IMAGE function
There are a couple of benefits of using the IMAGE function over inserting the image using the ‘Image in Cell’ functionality:
- The IMAGE function allows a lot more flexibility as compared with the ‘Image in cell’ option. You can choose from four different modes that allow you to choose whether you want the image to fit perfectly or stretch and fill the cell or remain at a specific height and width.
- IMAGE function can take a cell reference as the input (where the cell reference has the URL to the image). This makes it dynamic – which means that if you change the URL in the reference cell, the image would automatically update. Also, if you have 10 image URLs in a column, you can easily use the IMAGE function to get the images quickly for all the 10 image links.
Creating Dynamic Image Lookup Functionality in Google Sheets
Since the IMAGE function can easily update when you change the URL of the image, you can use this to create an image lookup (picture lookup) functionality in Google Sheets.
Something as shown below:
In the above example, as soon as I select a company name, the logo of that company automatically updates in the adjacent cell. This is made possible using the IMAGE function (along with the VLOOKUP function).
Below are the steps I have used to create this picture lookup in Google Sheets:
- Have the company names and the image links in a sheet in Google Sheets. This could be on the same sheet where you want the picture lookup or could be on a different sheet. In this example, I have these names and logos in a sheet named – ‘Image Links’
- Create a drop-down list in the sheet where you want the picture lookup to work. For this drop-down, use the company names so that these same names appear in the drop-down.
- In the adjacent cell, use the following formula to fetch the URL for the company logo. This fetched URL is used in the IMAGE function to insert the image of that fetched URL. Since this is connected to the drop-down value, when you change the value, the logo also changes: =IMAGE(VLOOKUP(A1,‘Image Links’!$A$2:$B$6,2,0))
This could be useful when you’re creating a dashboard and you want an image to appear based on the selection from the drop-down.
You may also like the following Google Sheets tutorials: