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 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