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.
Table of Contents
1. IMAGE Function Syntax
IMAGE(source, [alt_text], [sizing], [height], [width])
2. IMAGE Function Arguments
| 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. IMAGE function example
Formula in cell E3:
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:
4.1 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 dissmiss 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
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 many 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