How to use the IMAGE function
What is the IMAGE function in Excel?
The IMAGE function inserts an image into a cell using an Excel formula.
Which image formats is supported?
Working picture formats are BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP.
What is the benefit of using the IMAGE function compared to copying and pasting an image into the worksheet?
- The IMAGE function can reference cells containing image URLs or file paths. If these references change, the displayed image updates automatically.
- You can use formulas to conditionally display different images based on data or user input.
- The function allows for precise control over image dimensions across multiple cells or sheets.
- Images inserted via the function are linked rather than embedded, potentially reducing the overall file size of your workbook.
- Updating images becomes simpler, especially when the same image is used multiple times. You only need to change the source URL/path.
- The function allows you to add alt text easily, improving accessibility for users with screen readers.
- Images can be more tightly integrated with your data, enabling dynamic visualizations or icons based on data values.
- The IMAGE function can be part of more complex formulas or macros, allowing for automated image insertion or changes.
Table of Contents
1. Syntax
IMAGE(source, [alt_text], [sizing], [height], [width])
| source | Required. URL path, files on your hard drive won't work. |
| [alt_text] | Optional. Alternative text describing the image. |
| [sizing] | Optional. 0 - Fit cell. Fits the image in the cell and keeps its aspect ratio. (Default) 1 - Fill cell. Fills the cell, the aspect ratio is not maintained. 2 - Original size. The image is displayed with its original height and width. 3 - Customize size. Lets you specify the height and width. |
| [height] | Optional. Custom height. |
| [width] | Optional. Custom width. |
3. Example
The image above demonstrates how to use the IMAGE function, column B shows the results and column C shows the corresponding formulas. Each formula progressively demonstrates more advanced usage of the IMAGE function, from basic display to custom sizing and alt text addition.
The IMAGE function in Excel is being used to display images in cells. Let's break down each formulas.
Formula in cell B2:
This formula displays the image at its default sizing which is 0 (zero), with no additional parameters. 0 (zero) makes the image fit the cell as much as possible while keeping the aspect ratio.
Formula in cell B4:
This formula displays the image and adds "Flower" as alt text, which is useful for accessibility.
Formula in cell B6:
The ",1" parameter sets the sizing mode to 1, which fills the cell and the aspect ratio is not maintained.
Formula in cell B8:
The ",2" parameter sets the sizing mode to 2, which displays the image with its original height and width. The image is so large that the cell only shows a small part of the image.
Formula in cell B10:
This formula uses sizing mode 3, which allows specifying custom dimensions. The last two parameters (100,100) set the width and height to 100 pixels each.
4. Show an image based on a drop-down list
This example shows an IMAGE function linked to a drop-down list located in cell C4. A lookup is performed when the user selects a value. The corresponding URL on the same row as the lookup value is then used in the IMAGE function to load the correct image.
Formula in cell C2:
This formula combines the IMAGE function with the XLOOKUP function to dynamically select and display an image. Let's break it down:
XLOOKUP function: XLOOKUP(C4, B8:B9, C8:C9)
- C4 is the lookup value (what we're searching for)
- B8:B9 is the lookup array (where we're searching)
- C8:C9 is the return array (what we want to return)
This XLOOKUP is searching for the value in C4 within the range B8:B9. When it finds a match, it returns the corresponding value from C8:C9.
IMAGE function: The result of the XLOOKUP is then passed as the argument to the IMAGE function.
This formula is doing the following:
- It looks up an image URL based on the specified criteria in C4.
- Once it finds the matching image URL/path, it passes that to the IMAGE function.
- The IMAGE function then displays that image in the cell.
This setup allows for dynamic image selection based on criteria, which can be useful for creating interactive dashboards or reports where images change based on user input or other data in the spreadsheet.
4.1 How to add a drop-down list
- Select cell C4.
- Go to tab "Data" on the ribbon.
- Press with mouse on the "Data Validation" button. A dialog box appears.
- Select "List" in the drop-down list below "Allow:".
- Press with left mouse button on the "Arrow" button below "Source" and then select cell range B8:B9. Press Enter.
- Press with left mouse button on "OK" button to dismiss the dialog box.
A drop-down list handle appears when you select cell C4, see the image below.
Press with left mouse button on the "arrow" to expand values in the drop-down list, see the image below.
Press with mouse on a value to select it.
4.2 Explaining formula in great detail
Step 1 - Lookup selected drop-down value
The XLOOKUP function search one column for a given value, and return a corresponding value in another column from the same row.
Function syntax: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
XLOOKUP(C4,B8:B9,C8:C9)
becomes
XLOOKUP("Leaf",{"Flower";"Leaf"},{"https://www.get-digital-help.com/wp-content/uploads/2022/09/flower.png";"https://www.get-digital-help.com/wp-content/uploads/2022/09/Leaf.png"})
and returns
"https://www.get-digital-help.com/wp-content/uploads/2022/09/Leaf.png".
Step 2 - Evaluate the IMAGE function
The IMAGE function inserts an image into cells using an Excel formula.
Function syntax: IMAGE(source, [alt_text], [sizing], [height], [width])
IMAGE(XLOOKUP(C4,B8:B9,C8:C9))
becomes
IMAGE("https://www.get-digital-help.com/wp-content/uploads/2022/09/Leaf.png")
and displays the image in cell C2.
5 IMAGE function not working
The IMAGE function shows a #VALUE! error if
- an image format is not supported
- alt_text is not a text string
- size is larger than 3 or smaller than 0 (zero).
- size is 3 and height and with are omitted or is less than 1.
- size is 0 (zero) to 3 and width and height are also specified.
5.1 IMAGE function - #CONNECT error
The IMAGE function returns #CONNECT! error if the function fails to load the image.
5.2 IMAGE function - #BUSY error
The IMAGE function shows #BUSY! error until the image is fully loaded.
Useful resources
IMAGE function - Microsoft support
Excel IMAGE function - quick way to insert picture in cell with formula
Functions in 'Web' category
The IMAGE function function is one of 4 functions in the 'Web' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form