This guide covers everything you need to know about the Google Sheets IMAGE function, including its definition, syntax, use cases, and how to use it.
What is the IMAGE Function? How Does It Work?
The IMAGE function in Google Sheets inserts an image into a cell using a URL not hosted on drive.google.com. The purpose of this function is to add visual attributes to your data. For instance, if you have a product list in your spreadsheet, you can use the IMAGE function to insert product images, making the product list more comprehensive and visually appealing.
One of the key features of the IMAGE function is its ability to customize the size and display of the image. This is managed by the optional mode parameter, which has four settings.
- Mode 1 resizes the image to fit inside the cell, maintaining the aspect ratio.
- Mode 2 stretches or compresses the image to fit inside the cell, ignoring the aspect ratio.
- Mode 3 leaves the image at its original size, possibly causing cropping.
- Mode 4 lets you set a custom size.
If you choose mode 4, you can specify the image’s height and width in pixels with the optional height and width parameters.
IMAGE Syntax
The syntax and arguments for the function are as follows:
IMAGE(url, [mode], [height], [width])
Each argument within the IMAGE function has a specific purpose:
-
- url: This is a mandatory argument where you provide the URL of the image you want to insert. The URL must include the protocol (e.g., http:// or https://). Note that the function does not support URLs hosted at drive.google.com or in SVG file format.
- mode: This is an optional argument, with the default value set to 1. The mode determines the sizing of the image within the cell. Here are the different modes:
- 1: Resizes the image to fit inside the cell while maintaining the aspect ratio.
- 2: Stretches or compresses the image to fit inside the cell, ignoring the aspect ratio.
- 3: Leaves the image at its original size, which may lead to cropping.
- 4: Allows you to specify a custom size for the image.
Please note that none of these modes cause the cell to resize to fit the image.
- height: This is an optional argument. It lets you set a custom height for the image in pixels. However, the mode must be set to 4 to use this argument.
- width: This is also an optional argument. It allows you to set a custom width for the image in pixels. Like the height argument, the mode must be set to 4 to use this argument.
Examples of How to Use the IMAGE Function
Here are some practical examples of how to use the IMAGE function in Google Sheets:
Example #1: Displaying an Image in a Cell
If you want to display an image in a cell, you can use the IMAGE function. For instance, if you have an image URL, you can use the following formula:
=IMAGE(“http://www.example.com/image.jpg”)
This formula will display the image from the URL in the cell.
Example #2: Controlling the Size of the Image
The IMAGE function allows you to control the size of the image. You can use the second optional argument to specify the mode of resizing. For example:
=IMAGE(“http://www.example.com/image.jpg”, 2)
This formula will stretch the image to fit into the cell, maintaining the aspect ratio.
Example #3: Controlling the Aspect Ratio of the Image
If you want to control the aspect ratio of the image, you can use the third optional argument. For instance:
=IMAGE(“http://www.example.com/image.jpg”, 2, 1)
This formula will stretch the image to fit into the cell without maintaining the aspect ratio.
Example #4: Setting a Custom Size for the Image
You can also set a custom size for the image using the fourth optional argument. For example:
=IMAGE(“http://www.example.com/image.jpg”, 4, 1, 100)
This formula will resize the image to a specified width of 100 pixels. The height will be auto-adjusted to maintain the aspect ratio.
IMAGE: Common Mistakes & Problems
When discussing the IMAGE function in Google Sheets, there are several common mistakes and problems that users often encounter. These include:
- Incorrect URL format: The URL provided should be publicly available and should be in a format that Google Sheets can read. If the URL is incorrect or private, the IMAGE function will not work.
- Not specifying mode: The IMAGE function in Google Sheets requires a mode to be specified. This mode determines how the image is displayed in the cell. If a mode is not specified, Google Sheets will default to mode 1, which may not give the desired result.
- Not properly sizing the cell: The cell in which the IMAGE function is used needs to be properly sized to fit the image. If the cell is too small, the image may be cut off or not displayed.
- Using incorrect syntax: The syntax for the IMAGE function is =IMAGE(“url”, [mode], [height], [width]). If the syntax is not followed correctly, the function will not work.
- Not using quotation marks around the URL: The URL for the IMAGE function needs to be in quotation marks. If the quotation marks are omitted, Google Sheets will not recognize it as a URL, and the function will not work.
- Overloading a single sheet with numerous IMAGE functions: Google Sheets has a limit on the total amount of Importrange and IMAGE functions that can be used in a single sheet. If too many are used, the sheet may become slow or unresponsive.
- Forgetting to enable the display of images: In Google Sheets, images are not displayed by default. You need to go to the View menu and enable the display of images for the IMAGE function to work.
Why Is IMAGE Not Working? Troubleshooting Common Errors
If you encounter issues with the IMAGE function in Google Sheets, this section will help you understand the common errors, their reasons, and how to rectify them.
#VALUE! Error
Cause: This error typically occurs when Google Sheets cannot find the image URL you’ve provided or the URL is incorrect. This might be due to a typo in the URL or because the image has been removed or moved from its original location.
Solution: Double-check the URL you’ve entered to ensure it’s correct. If the URL is correct, verify that the image is still available at that location. If the image has been moved or removed, you might have to replace the URL with a new one.
#REF! Error
Cause: The #REF! error is usually seen when you reference a cell that doesn’t exist. This could happen if you’ve deleted a cell used by your IMAGE function.
Solution: To resolve this, identify the cell reference in your IMAGE function and ensure that it exists and contains valid data. If you’ve deleted the cell, you may need to undo the delete or adjust your formula to reference a different cell.
#N/A Error
Cause: This error appears when Google Sheets can’t find the data you’re looking for. In the context of the IMAGE function, this error might happen if the function looks for a URL in an empty cell or contains non-URL data.
Solution: Check the cell that your IMAGE function is referencing. If the cell is empty, you must add a valid image URL. If the cell contains data, ensure it’s a valid URL.
#ERROR! Error
Cause: This error is a general error message that appears when Google Sheets encounters an unknown issue with your formula. This could be due to an unsupported file type, an incorrect argument in the IMAGE function, or other unspecified issues.
Solution: First, make sure your image is in a compatible file format, such as .png, .jpg, or .gif. Then, check the arguments in your IMAGE function to ensure they’re correct. The syntax should be IMAGE(url, [mode], [height], [width]). If the error persists, try simplifying your formula or breaking it down into smaller parts to identify the source of the issue.
#DIV/0! Error
Cause: This error occurs when a formula attempts to divide by zero. With the IMAGE function, this could occur if you’ve set the height or width of your image to zero.
Solution: Check the height and width arguments in your IMAGE function. If either is set to zero, adjust it to a positive number.
Using IMAGE With Other Google Sheets Functions
Combining the IMAGE function with other Google Sheets functions can further enhance your data presentation and reporting. Here, we’ll explore how to use IMAGE with functions like HYPERLINK, IF, and ARRAYFORMULA.
With HYPERLINK
Usage: One of the most common ways to use IMAGE in conjunction with another function is with HYPERLINK. This allows you to create a clickable image that directs the user to a specific URL when clicked.
Example: If you wanted to insert an image of a product and make it clickable so it directs to the product’s page on your website, you could use the following formula:
=HYPERLINK(“https://www.yourwebsite.com/productpage”, IMAGE(“https://www.yourwebsite.com/image.jpg”))
With IF
Usage: IF can be used with IMAGE to display different images based on certain conditions.
Example: Suppose you have a column that indicates whether a task is complete or incomplete. You could use the IF function with IMAGE to display a green checkmark image for completed tasks and a red X image for incomplete tasks.
=IF(A2=”Complete”, IMAGE(“https://www.yourwebsite.com/greencheck.jpg”), IMAGE(“https://www.yourwebsite.com/redx.jpg”))
With ARRAYFORMULA
Usage: ARRAYFORMULA allows you to apply a function across an entire range of cells. When used with IMAGE, you can insert images into multiple cells at once.
Example: If you have a column of URLs that link to different images, you can use ARRAYFORMULA with IMAGE to insert all of these images at once. Assume that column A contains the image URLs.
=ARRAYFORMULA(IF(LEN(A2:A),IMAGE(A2:A), “”))
This formula uses IF to check if each cell in the range A2:A is not empty (i.e., it contains a URL). If it’s not empty, IMAGE inserts the image. If it is empty, the cell remains empty.
For more details on the IMAGE function, check out the official documentation at the Google Docs Editors Help Center.
