Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Displaying Images based on a Result.

Displaying Images based on a Result

by Allen Wyatt
(last updated June 3, 2014)


Dave has a large database that he keeps in an Excel workbook. It consists of material samples and uses the VLOOKUP function to generate various forms and reports. Dave wants to include a bitmap image on the form that changes according to one of the variables. For instance, if the form is describing an apple, then Dave wants a picture of an apple to appear; if describing a pear, then a pear should appear; and so on.

This is certainly a challenging task, but it is one that can surprisingly be done without macros. The steps are involved, but not that difficult to perform once you get to it:

  1. On a new worksheet, select a cell (such as cell B4).
  2. Enter the name "apple" into the Name box. This defines the name "apple" to refer to cell B4.
  3. With cell B4 still selected, choose Insert | Picture | From File. Select the picture of the apple and insert it.
  4. Enlarge the width and height of cell B4 so that the picture of the apple is contained entirely within the cell.
  5. Repeat steps 1 through 4 for each of your other pictures, placing each picture in a different cell and naming them according to the contents of the picture. (For the sake of this example, I'll assume that "pear" is cell D4 and "orange" is cell F4.)
  6. On the worksheet that will contain your form, create a formula that will contain the names of the fruit, such as the following formula, which displays "apple," "pear," or "orange," depending on the value in cell G1. (It is important that the formula reference the names exactly as you defined them in step 2 for each fruit's picture. For the sake of this example, I'll assume that you entered this formula in cell A1 of Sheet1.)
  7.      =IF(G1=1,"apple",IF(G1=2,"pear",IF(G1=3,"orange","")))
  8. Choose Name from the Insert menu, then choose Define. Excel displays the Define Name dialog box. (See Figure 1.)
  9. Figure 1. The Define Name dialog box.

  10. Replace the contents of the Names In Workbook box with the word "Picture".
  11. Replace the contents of the Refers To box with the following formula:
  12.      =INDIRECT(Sheet1!$A$1)
  13. Click the OK button. You've now defined the name "Picture" to contain the formula entered in step 9.
  14. On the worksheet that will contain your form, select the cell where you want the dynamic image to appear.
  15. Choose Insert | Picture | From File. Select and insert a picture (it doesn't matter which one).
  16. Make sure the picture you inserted in step 12 is selected.
  17. In the formula bar, enter the formula =Picture. (This is the name you defined in steps 7 through 10.) The picture should change to reflect whatever fruit is named in cell A1.

Now, whenever the fruit name in cell A1 changes (which is, in turn, based on the value in cell G1), the image will change.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3128) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Displaying Images based on a Result.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Ensuring Proper Page Numbers for a Table of Authorities

Automatically create a Table of Authorities entry in your document, and Word might place the necessary field at the wrong ...

Discover More

Deleting a Macro

Don't need that old macro any more? Here's how to get rid of it so that it is no longer a part of your workbook.

Discover More

Files Opening Slowly If Many Files Exist

Managing large numbers of documents in Word can lead to some interesting challenges. One potential challenge is that your ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (menu)

Flipping a Drawing Object

Don't like the way a drawing object looks? Perhaps flipping the object could help appearances. Excel allows you to flip ...

Discover More

Deleting Graphics when Deleting a Row

If you use Excel to keep a graphic with each row of data you amass, you may wonder if there is a way to easily delete the ...

Discover More

Deleting All Graphics

Graphics can really add pizzazz to a worksheet, but they can also present some drawbacks. If you want to get rid of all ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three less than 5?

2018-05-12 08:06:32

Richa Kataria

I am not able to put formula on selecting the picture in the last step.

The formula bar is not working when I select the picture.

Pls suggest

2017-12-14 16:57:09


Thank you for this it was bery useful. I was forced to amend to VLookup instead of nested IFs as I had too many images.

2017-05-18 13:01:37


I followed your tips for this technique and was initially successful. I was using a Data Validation Drop Down List to choose the Item, that would in turn display the associated Picture. After the Picture inserted correctly, I could no longer utilize the Drop Down List. When I selected the cell that is the reference for the pictures, the drop down arrow was visible, but disappeared, not allowing me to choose from the menu. I thought I could not have the reference cell for the Indirect be a drop down, so I created the drop down in a different cell and made the Indirect reference cell = the cell I made the drop down in. Seemed to work fine. Then I tried to insert the Picture into a different sheet within the workbook after testing and made its Formula =Picture. The picture initially changed, but now ALL of my drop down menus are behaving as described above, the arrow shows initially when the cell is selected, but goes away and I am unable to interact with the menu.

If anybody is looking at this can you explain what is going on as this fits my needs perfectly, but I obviously need to be able to interact with my drop down menus.


2017-02-09 05:15:21

umair ahmed

help required for shapes linked with formulas

2017-01-23 05:48:36


1. Explain the need for a solution to a design problem.
2. Plan your research
3. Analyse existing designs
4. Present your research findings and write a design brief.

2016-12-10 01:32:22


Can i get a sample excel, somehow i am unable to execute the function, it is reflecting errorr

2016-10-26 08:00:41


ok, it is working quite ok but what if I need to have cell , where picture should appear, that will be automaticly filled up with picture according to the some other cell content?
for example, i have item number code.
and i want to program several cells with vlookup so some data related to the item no. i hvae will be take from other table ...but how i can automaticly copy pictures....using idea you describe(indirect) i need to write picture name in each cell and it take lots of time for often work ....

2016-10-14 06:20:38


This worked perfectly for me thank you. My only problem is that I get a white border at the top and left around the image, I've tried playing around with the cells and the images but can't seem to get rid of it (I only notice it as the report I have the picture in has a black background. If there is no solution I can just make the report white


2016-09-21 04:29:57


This is a miraculously hidden feature.
Thanks for the heck. Wonderful.

2016-09-07 05:45:51



If you want to remove the border,go to view and swith the gridlines off. It works, at least for me.

Anyway, what is missing in this manual is the fact that to have it working one needs to paste a randomly chosed empty cell by paste special: linked image (or sth like that, I've got the menu in Polish).


2016-05-11 19:55:09


Thank you!!! Master

2016-03-25 12:05:00


I just found this and it was useful. And for those who were complaining about the grey box: that are the borders around your cell. Just change the boarder color of your picture database and problem solved.
Hope that will help.

2015-11-11 20:28:19


You, sir, are a genius.

Works beautifully in Excel 2016.

2015-09-21 09:17:56


Is this only valid for Picture not for Shapes ?

2015-09-10 13:02:04

kadr leyn

Thank you very much sir,

I did a similar example :
" Calling Image From Another Page "

it's codes :

Resim1a =OFFSET(resim!$C$2;MATCH(Weather!$C$8;Liste;0)-1;0;1;1)
Resim1p =OFFSET(resim!$C$2;MATCH(Weather!$C$9;Liste;0)-1;0;1;1)
Resim2p =OFFSET(resim!$C$2;MATCH(Weather!$C$11;Liste;0)-1;0;1;1)
Resim2t =OFFSET(resim!$C$2;MATCH(Weather!$C$12;Liste;0)-1;0;1;1)
Resim3t =OFFSET(resim!$C$2;MATCH(Weather!$C$13;Liste;0)-1;0;1;1)

You can view descriptions and download template here :

2015-09-10 11:41:03


" Tapa 16 Jun 2015, 17:47
Thank-you so much for sharing this. It worked for me. However, I also got a grey border around the image. It gets away if I set soft edges to 1 point, but this also hides the image by 1 point as there is no extra space in my image. So, I cannot set soft edges to 1. Any idea if/how I can remove the border?"

Tampa - The grey border is the result of the formatting around the selected "source" cells. So where you have stored the pictures of all the oranges, apples, etc. You must remove all borders around these cells and set the fill color of the source cells AND adjacent cells to white. Once done the dynamic image will be the picture without any border. If you like you can then select the dynamic photo and select the "format picture" tab to add a border around the entire image.


2015-09-01 10:18:04


Works great! Thanks

2015-08-18 18:37:31


This is one of the most poorly written procedures I've ever seen. The instructions are all over the place. Very confusing.

2015-08-01 04:37:20

Pramod Sinha

Could anybody help me out the complete procedure of preparing ID Card data sheet with required formulae. I shall be so thankful as I am very much in need of such a software prepared on excel.

2015-06-16 17:47:11


Thank-you so much for sharing this. It worked for me. However, I also got a grey border around the image. It gets away if I set soft edges to 1 point, but this also hides the image by 1 point as there is no extra space in my image. So, I cannot set soft edges to 1. Any idea if/how I can remove the border?

2015-05-31 22:39:05


I need help to make my work easy using Excel. Whenever I type the item number, the data shows up with the help of Vlookup formula.

I would like to have the picture of the item added as well. I have hundreds of pictures and the sizes are not the same. It needs to be compressed without being distorted, something like being relevant to the original size.

Whenever I type the item name b2, the picture should be inserted in b3. The next item would be on f2, the picture should be inserted in f3, and so on.

I hope someone here can really help me out.

2015-05-21 12:23:05


Also if found it easier to simply go into the Name Manager and create a Name for what you want the picture to be called and then under "refers to" click on where you have the picture stored. Then create name of "Pictures"(like in the example) and this refers to where you are storing the INDIRECT formula

2015-05-21 11:56:21


Works great!

It did though require some reworking. Here are some tips that helped me:

1. In step 2, it took me awhile but I found the name box was where the cell reference is(the box in the upper left corner of the spreadsheet). You need to change this to whatever you want to call the picture

2. In the last step, where I mainly got the error, I came to find out that the INDIRECT formula in step 9 must reference where you are putting the names of the pictures. So let’s say you, like in the example, are dealing with fruit and you have you have the name of apple in b15 and want the picture in c15. The in the INDIRECT formula you will change it to = INDIRECT(Sheet1!b15). Then you paste any picture in c15 and while it’s selected enter =Picture. This should work. I was getting the “Reference is not valid” error and it was because I wasn’t referencing the correct cell. Also as noted the name in the cell you are referencing must match what you named the picture.

Also one thing that's annoying is that you will have to create a new "Name" value for each picture you insert, assuming you want them in different cells. So if you want one in c15 and c16, you need two "Picture" Names

2015-05-01 09:37:09


Doesn't work. I find the instructions cryptic. I get the same error as others, 'Reference is not valid'. This was very frustrating. jmathane's solve didn't work either. Big waste of time for me.

2014-11-30 18:22:05

Neil Sweeny

Can you help me please. I am doing a spreadsheet for my school work.

I want to trigger a set of text messages in a cell based on values in another cell.

There are 5 one word text messages which will appear in the target cell

The values in the main cell will all have ranges e.g. 2-5, 6-13, 14-17, 18-25, 26 +

What formula should I use?

Thank you.

2014-10-17 00:15:23


thankyou sir,, it works

2014-09-25 10:12:07

Leks Inans

Works like a charm

2014-08-27 06:32:07

James Koch

Very Cool, i couldn't get it to work at first but now that it does its very cool, i could have used this so many times before.

2014-08-13 14:02:49


I cannot enter "=Picture" to the formula bar as I keep getting a 'Reference is not valid' message. I tried jmathane's solution but had no success (using Excel 2010). Has anyone been able to solve this problem? Thanks

2014-08-12 14:07:32



I agree that use the advice of jmathane and insert as a picture from the developer tab so you can see the formula.

Also, you don't have to define names for each picture. You can just make it say something like "Sheet1!A1" instead of using "apple".

2014-07-31 18:30:11


I want to use this trick in Excel 2013. Can you help please?
I have Date1 and Date2. In a cell, I want to show Green Dot if Date1 is greater than Date2, Yellow Dot if both dates are equal, and Red Dot if Date1 is smaller than Date2.

2014-07-12 15:51:40


Allen Wyatt: thank you very much, it work fine, and i'm happy to learn new things ..
Thanks again

2014-06-16 07:39:34

Rajroshan I

Dear Sir,

I want to know how below example works

Here number of work orders are there with Name and Month, for example:

Work order Name Month

1236 ADV Jan-13
1456 RGE Feb-13
8597 REHD March-13
9567 TYU Apr-13
8649 IUR MAY-14

Suppose if entering work order in one row Name and month should display if that work order is available in the sheet orl's it should display -NA-

Ex: If i am entering..

Work order: 8649

Output should be:

Name: IUR Month: MAY-14


Work order: 10010

Output should be

Name: -NA- Month: -NA-

kindly reply as soon as possible.


2014-05-26 17:17:36

larry trimboli

this works very well---thank you--- but i do not understand the "indirect" seems like voodoo on how it pics up the does this accomplish this....a better understanding of the function would make it allot easier to use on a regular basis

2014-04-23 15:13:39


Great work Allen. I got it to work after 2-3 tries. The directions need to be polished up a bit, but overall I was able to accomplish my task. Thanks!

2014-02-08 19:48:33


I too have made this work, and it is an interesting exercise.
In reply to Joe, I think it is not an image border you are seeing, but the border around the cell which contains the image. If you turn off gridlines, your image should no longer have a border.
I am also finding that when this works, other aspects of Excel don't. Specifically, cell comments display for a split second then disappear. I'd be keen to understand why this is the case.

2014-02-08 07:29:07


When I follow this tip the image is always in a square box. Is there any simple way to have the image appear without a surrounding white background? I am trying to create a map of interlocking hexagons so would need the dynamic image created to be hexagon rather than square.

2014-02-03 12:44:28


Ok, I answered my own question.

You have to right click and select Format Picture.

The only thing I could find that would alter the border was the Glow and Soft Edges.

Setting the Soft Edge to 1pt seems to work well.


2014-02-03 12:13:48


I found that my named ref had to include the sheet name also.


I also noticed that when you click on the image and put in the named ref, is switches the picture to kinda like a Text Box with the picture inside of it.

This creates a BORDER around all of it. How do we turn off the gray border?

2014-01-17 15:06:48


I tried this and it broke the rest of my VBA code. For those who had the same problem, you might have better luck putting all of the pictures on top of each other and editing their .visible property based on the control cell's value. It may not be the most elegant way, but it worked for me. Here is some of my Excel 2010 VBA code to get you started:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$B$12"
If Range("$B$12").Value = Range("$S$2").Value Then
Shapes("Picture 17").Visible = True
Shapes("Picture 17").Visible = False
End If
If Range("$B$12").Value = Range("$S$3").Value Then
Shapes("Picture 11").Visible = True
Shapes("Picture 11").Visible = False
End If
If Range("$B$12").Value = Range("$S$4").Value Then
Shapes("Picture 12").Visible = True
Shapes("Picture 12").Visible = False
End If
Case Else
End Select
End Sub

Good luck,

2014-01-16 16:59:27


Hi guys,

I have followed the instructions and I got it working, really happy. It would be life-saving if someone could look at this situation:

The workbook where the picture is updating also contains a number of cells with values selected from drop-down lists. After the modifications to have the picture updated, the drop-down lists are not shown anymore. You click on the cell and the arrow indicating the drop-down list shows up for a split of the second only, then dissapears. The actual list is not displayed. You can still type the value in the cell.

This happens right after the step where I select the picture and then type '=Picture' in the formula bar, which is step 14. The "picture updating" feature starts to work but I loose the drop-down lists, as described above.

All objects are visible, I have checked this. I am using Excel 2013.

Any reply will be much appreciated.

2014-01-05 09:44:46

Art Lichtenberger

I am trying to create an info {name, email, group} and pic directory for everyone who uses my lab (>50 people) where I print it off on 18x24 paper... I want to have two column sets of this info laid out on the page (sorted by last name)... so you would start reading the names/info in the first column, read down to the bottom, then start reading at the top right column etc. I have a different/source sheet with all this info in one column-set including pics for each person/row. I can use Vlookup to create the 2column-set directory, but it doesn't work for pics. Your method seems a bit intractable, given that I have over 50 pics, and they change every semester. Is there an alternative approach for this?

2013-12-25 08:35:33


For those who could not find way to access formula bar while pic being selected, just follow jmathane's instruction. It works great. You may wanna save your workbook to make it works.

2013-11-22 23:11:20

Arlindo Campos

Solving Step 14

In order to be able to enter the formula(=Picture) on step 14 try first to go to G1 and input any of the pictures value (1,2 or 3) then select the picture and enter the formula(=Picture) on the formula bar. When you input a number on G1 the correspondent picture gets associated to Picture(defined on step 7). If there's no picture associated at the time you try to input the formula the system considers that there's no referenced picture and wont allow you to input the formula.

Another tip:

The formula on A1 =IF(G1=1,"apple",IF(G1=2,"pear",IF(G1=3,"orange",""))) is not really needed. If you just type "apple", "pear" or orange on A1 it will work just fine.

I hope it helps, it is working great for me!
If you want to

2013-10-30 08:20:09


Your instructions get unclear at Step 9. This would have me enter the word "Picture" in the blank box immediately following "Names in workbook:", modify the formula in the "Refers to:" box to "=INDIRECT(Sheet1!$A$1)," and then click OK.

This results in the word "Picture" being added to the list of names, and later results in an image of the word itself (not the image).

Also, you say, "insert a picture (it doesn't matter which one)," and immediately thereafter say "make sure the picture you inserted in step 12 is selected." OK - it doesn't matter, but it does matter.

And where the hell is step 12?

I hope no one's paying for this advice.

2013-09-03 18:00:39


Hi Julien B,

This is probably very late, but yet again, it might be useful for someone out there:

You could delete the picture link at the start of your code using

Worksheets("Sheet_Name").Shapes("Picture_Name").DrawingObject.Formula = ""
Worksheets("Sheet_Name").Shapes.Range(Array("Picture1","Picture2","Picture3")).DrawingObject.Formula = ""
[if you have many pictures with the same link]

and resetting it at then end using

worksheets("Sheet_Name").Shapes("Picture_Name").DrawingObject.Formula = "=Picture"

[here Picture_Name refers to the dynamic picture that is actually changing]

Alternatively, if you have many (different) picture links then you could try setting the calculation mode to 'manual' at the beginning of your vba code

Application.Calculation = xlCalculationManual

and then resetting it to 'automatic' at the end

Application.Calculation =xlCalculationAutomatic

. However, if your vba code depends on calculated formulae, then you need manually calculate the worksheet/workbook at the appropriate locations in your code using

All the best

2013-05-01 10:52:07


Hi folks, I finally found the solution : In the Developer bar, chose the menu Insert > Active X Controls > Image (chose the corresponding icon). Place and size the image field on your sheet. You can attach the formula "=Picture" and it works !!

2013-04-28 11:09:43

Alex Quinn

Works for me! (Excel 2013, Win7)

The picture is floating, not attached to a cell, but which picture it shows is indeed controlled by the formula. The instructions are perfect.

2013-03-27 17:11:20


Seems Im having the same problem as J Winters. That last step just not working for me in Excel 2007. Clearer instructions and any help highly appreciated.

2013-03-27 08:41:33

Julien B


I also used this technic in some of my files.

However, some some reasons I'm not aware of, it ruins the speed of the VBA code of all the file...

My VBA code, which doesn't manipulate at all these images, takes ages to complete.

When I supress the conditional images, it comes back to normal (a few seconds).

Has anyone a clue on this issue?

Thanks in advance

2013-03-14 08:43:24

Diogo Borges

amazing stuff! thank you very much!

2013-01-18 05:24:02

Charles Schroeder

I now want to add the same images in a different cell to be triggered by the value in another cell. I tried doing it using the same image but then nothing works. I tried to duplicate the images with a different name i.e. pear now becomes pear2, etc and also defined the name of cell A2 as Picture2, and in the Formula bar enter =Picture2. The second image still does not work. What have I done wrong!

2013-01-15 03:39:14

J. Winters

This does not work in Excel 2007.

It will fail on the last step since you cannot select a picture and enter a formula in the formula bar. The picture is float on top of the worksheet.

Perhaps this worked differently in previous versions of excel?

2013-01-11 15:38:22

Charles Schroeder

This is so simply and it works. I must admit that I had to do it a few times to get it right. Thanks a million.
My tip is to print the instructions and then to do it slowly step by step. Regards Charles (in Cape Town)

2012-11-05 04:27:06


This is exactly what i was looking for. Many thanks!
Changing pictures without having to fall back on VBA..

2012-08-23 13:46:36


This is just what I was looking for, thanks! And fyi, the numbering in your document appears to reset for the different sections, i.e. your step for your formula for apple/pear/orange resets, so later on when you refer to "Step 12" it's actually the 3nd step 2 :)

2012-07-24 05:07:25


It appears you need to refresh the formula (=picture) to get it to show a changed image.

2012-05-11 09:18:37


In the last step, I can access the formula bar with the picture selected. What am I missing?

2012-04-14 09:23:37


Like John, I am also trying with a larger (30) number of pictures. I am trying to make a NHL Playoff pool spreadsheet, and I would like to display the logos based on which team is selected. My challenge is also that I will need this funtionality throughout the spreadsheet, so a dynamic method would be preferred. Any ideas?

2012-04-13 15:03:33


What if you are wanting to do this on a much bigger level? I have this function creating a link to the image: =HYPERLINK(VLOOKUP(B1,Image_Names!$A$1:$B$200000,2,FALSE) where when the item entered in cell B1 is found in column A of sheet "Image_Names", a hyperlink is made of the filename that is in column B. What I want to do is have the image shown in the Excel file like above, only with thousands of images it would not be practical to name them individually for the method above. Any ideas on how to display based on the hyperlink?

2012-03-11 11:26:23

T. Chan

Step 1-4 can't bind the image to a cell. The image is "float" on the spreadsheet.

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.