How To Tie Photos To A Dropdown List Selection In Excel

Here’s how you can have a photo change based on the selection from a dropdown list in Excel:

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

This post came from a question asked on a LinkedIn user group forum and the response referenced a dueling podcast between Mr. Excel and Excel Is Fun. Here is the link to the Dueling Podcast on this topic.

Many of the processes I will use here have been discussed in previous posts, so if you need a refresher on any of them, just search my site for that topic and you should find a related post.

The goal here is to create a dropdown list of photo names, and have the related photo appear.

Here are 4 photos and the names I want to associate with each:

wine1

Here is the result I want to achieve:

wine2

As with some previous blog posts, I would strongly encourage you to watch the video. I will go through the steps in a cursory fashion below, but the video will best explain the details to accomplish this.

Step 1: Load your photos onto a worksheet, and make sure each photo is contained within a single cell. In a future step we will be referencing the cell where that the photo is located, so this is an important step.

Step 2: Create the list for your Data Validation dropdown. I first named the list from cells A2:A5 as “Wine” using “Create from Selection” on the Formulas tab.

Step 3: Locate where you want the dropdown list, and using Data Validation – Lists, enter the list based on what you named it. In my case: “=Wine”:

wine3

Step 4: Name the cells where the photos are located. Again, I used “Create from Selection” and named cells B2:B5 based on the names in cells A2:A5.

Step 5: Click on the cell where the dropdown list is located, and create a named formula. To do this, use the keyboard shortcut Ctrl + F3, then click on “New”. I then wrapped the cell location in an INDIRECT function. INDIRECT converts a text string to an actual cell reference. I also named it “WinePhoto”. Now, any name we call up with the dropdown list will tie to the cell reference of that name that we did in Step 4:

wine4

Step 6: Finally, copy any of the photos to the desired location near the dropdown list, then click on the photo and in the Formula bar, enter = followed by the name of the formula from Step 5:

wine5

Now, any selection you make from the dropdown list should produce the photo of that name!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , ,

42 Responses to How To Tie Photos To A Dropdown List Selection In Excel

  1. Jonathan February 19, 2015 at 3:06 pm #

    This is a great solution, but I find if its two words it doesnt work? So it has to be abu_dhabi for the system to pick it up? any way around this?

    • Michael Rempel February 19, 2015 at 3:53 pm #

      Jonathan,

      You are correct – named ranges can not have spaces. You could add another level to the process using something like VLOOKUP so that when you choose “abu dhabi” if will look that up in a table and return “abu_dhabi” and that could be tied to the photo.

      • Jonathan February 19, 2015 at 4:59 pm #

        Thats great, but could you give me some example of that please?

  2. Naalia April 24, 2015 at 7:25 am #

    Hi Michael,
    Thanks for this simple and effective way to show the linked images. I am trying to use this setting but every time I select one of the options from the dropdown menu all my images show up in the desired cell instead of just the one linked to the selected text. Can you help me on this? I believe I’m creating the wrong name.
    Thanks in advance.

  3. Trevor May 14, 2015 at 10:02 am #

    Hello Michael.

    Thanks for sharing this information! I’m having problems once I make it to step 5. I get the appropriate cell to generate a drop down of my choices. I then click on that cell and perform CTRL F3. I click New. I add INDIRECT and the (). Once I click on the icon to check the “Refers to”, I am unable to have it show that it is referencing the cell with the picture. Any insight will be greatly appreciated. Thanks!

  4. Gabriele August 13, 2015 at 9:39 am #

    Nice solution, but what if I need to replicate the process down the WHOLE column? The arrow at the bottom-right corner of the products name cell is not shown so I cannot swipe it down, and what should I do with the first image on the right column? Copy and paste throughout every single cell of the column?

    • Michael Rempel August 13, 2015 at 10:01 am #

      Try this video and let me know if it answers your question: http://www.excel-bytes.com/photos-tied-to-dropdown-list-update-in-excel/

      • Dan January 29, 2016 at 7:05 am #

        I used your first video nd it was great and this solution is ok for duplicating a few times, but I have a sheet that has hundreds of rows and I want to be able to select from a dropdown in every one of these rows which returns the associated picture.

        Is there a way of doing this without repeating these steps hundreds of times?

        • Michael Rempel January 29, 2016 at 9:01 am #

          Dan, I never thought about modifying the process for volume usage. Let me give it some thought over the weekend and see if I can come up with some shortcuts. If you want to send me a copy of your file to see what you are doing, maybe I can come up with some alternatives. You can send it to mrempel@excel-bytes.com.

          • Laura Sheppard May 5, 2016 at 2:44 pm #

            Hi Michael,

            I am having the exact same problem Dan was having. Would you mind sharing if you ever found a solution to that?

            Thanks,
            Laura

          • AJ March 3, 2017 at 5:59 am #

            Hi Michael,

            I have exact same question as Dan has. If you’ve had any solution please share with me

          • Michael Rempel March 3, 2017 at 9:26 am #

            AJ, I sent you an e-mail with a video attached that I hope will help.

          • Izzy Joseph June 14, 2017 at 4:27 pm #

            Hi Michael,

            I am also looking to do this for over 500 cells. I was wondering if you had found a solution.

            Thanks,

            Izzy

          • Michael Rempel June 14, 2017 at 4:32 pm #

            I don’t know a quick way to manage this process for that many cells. This was never designed for that. One major issue is the fact that it uses the INDIRECT function which is a volatile function. A volatile function is one that refreshes every time you update something on your worksheet. So you will have all those functions updating everytime you make a change, so even with a small file size, it can really slow down a process. What are the images you are pulling in? Can they be substituted with webdings or conditional formatting?

          • Izzy Joseph June 20, 2017 at 4:28 pm #

            conditional formatting worked perfectly for what i am trying to do. thanks so much Michael.

  5. MJ September 27, 2015 at 9:32 am #

    Nice solution.

  6. Col December 16, 2015 at 7:52 am #

    Hello Michael

    I’m having the same problem as Trevor.

    I generate a drop down box, click on that cell and CTRL F3, click New, add INDIRECT and the (). Clicking on the box to check the “Refers to” doesn’t highlight a picture. Trying to add the name ut tells me “reference is not valid”.

    Any suggestions?

    Regards
    Col

    • Michael Rempel December 16, 2015 at 9:49 am #

      It’s usually one of two issues. Either the drop down item doesn’t exactly match the name of the cell the photo is contained in – for example one has spaces and the other underscores. Also, make sure there is something selected in your drop down list when you go to copy the photo, or it won’t work. If neither of these solve your issue, you can send me the file and I will take a look at it. mrempel@excel-bytes.com.

      • Col December 17, 2015 at 2:43 pm #

        Hi Michael

        Many thanks for replying and your offer. Selecting one of the items in the list first has done the trick. Spendid. 🙂

        Bst regards
        Col

  7. Kenneth Chisholm January 21, 2016 at 7:08 pm #

    Hi Micheal,

    I enjoyed your lesson and I learned some great things from it. However, I am struggling to make it work. I am putting a little tweak to it and maybe that is where my problem lies. Let me give you an idea of what I am trying to do. I work in Aviation Maintenance and I am making a spreadsheet to show the status of the aircraft. There are (3) different status, Fully Mission Capable (FMC), Partial Mission Capable (PMC), and Non-mission Capable (NMC). So, in my drop down list I show FMC, PMC, and NMC. Instead of pictures I am trying to utilize shapes, color coded up and down arrows. Green Up Arrow = FMC, Blue Up Arrow = PMC, and a Red Down Arrow = NMC. So when the FMC is selected I want to appropriate colored arrow to pop. This is not happening. The arrow I copy and paste as you did at the end of the video does not change, or not that I can see when I select another choice in my drop down list. I assumed that the shapes would work the same as your pictures do, but perhaps I was wrong. Can this work and if so, will it work the same way as your pictures did in this lesson? Any help would be appreciated so much. If there is a way to just have the colored arrows in the drop down list, that would be even better. Everyone who works in aviation maintenance knows what the arrows mean, I really don’t need the letters if they do not have to be used.

    • Michael Rempel January 21, 2016 at 9:17 pm #

      Kenneth, Yes, you can use shapes. I never tried it before but I did tonight and it worked, although it took a couple tries before it worked. I will send you an e-mail with a file attached with two options, look at both worksheets. If you’d like, you can e-mail me your file and I will be happy to look at it. mrempel@excel-bytes.com

      • Nichola March 14, 2017 at 12:16 pm #

        Could you send me your example files using shapes please? I have followed your steps but I still can’t get it to work.

  8. ISHTAR August 6, 2016 at 8:30 am #

    OMG!!! YOU ARE A LIFE SAVER!!!!!! THANK YOU SO MUCH

  9. Hans November 9, 2016 at 7:28 am #

    Michael,

    Many thanks for the solution provided, it works very well. I do have one question: when the dropdownlist field is cleared, the last selected picture remains visible. How do I make the picture blank again as well?

    Thanks in advance.

    • Michael Rempel November 9, 2016 at 4:42 pm #

      Name a blank cell “blank”, then changed the formula set up in the Name Manager from =INDIRECT(Catalog!$C$2) to =IF(ISBLANK(Catalog!$C$2),INDIRECT(Data!$A$6),INDIRECT(Catalog!$C$2)). I will e-mail you a file showing this.

      • Hans Vink November 10, 2016 at 3:07 am #

        Worked like a charm, thanks!

  10. SGentry January 10, 2017 at 9:12 am #

    Is there a way to hide the outer box (light grey color) that appears around the photo? I’m trying to incorporate this into a PowerPoint presentation. Everything seems to work although the color of the box and aligning everything to look visually appealing is turning into a hassle. Any ideas or help?

    • Michael Rempel January 10, 2017 at 9:48 am #

      Select the photo where you want it to appear. Then click on the dialog box launcher for Picture Styles on the Format tab. Choose Soft Edges 1 pt. You can also try various other formatting options for different appearances.

  11. Murali March 3, 2017 at 3:12 am #

    Hello Michael,

    First off, thanks for a well explained article on this topic.

    I do have one follow up question. I have made an excel where the first 300×20 cells are exclusively for lists and images and the like. My drop down list is below these rows and works exactly as your example referring to a list and a bunch of images in the “reserved” cells.

    Now, if I collapse the 300×20 cells, the images don’t appear with changing dropdown values if I use the “scale & move with cells options”

    the source images remain visible if I keep the “only move with cells” option. although the dropdown menu works as expected.

    Is there a way to keep the images in a hidden area in the sheet while still allowing the reference to work?

    I know that the charts have an option to plot from hidden cells, but I could find no such options for an image with a formula

    Thanks

    • Murali March 3, 2017 at 3:57 am #

      Ok, I feel like I have overcomplicated the issue.

      1) I have implemented your method successfully
      2) I would like the source cells for the images and lists to be hidden (so that other users don’t mess with them)
      3) Hiding Rows and columns where I have my images and lists causes the above method to stop working, the dropdown is still having the text values present in my list, but the images do not display anymore.

      Is there a workaround or something I’m missing? Or do I have to create a locked sheet with everything visible as a source sheet?

      Thanks.

      • Michael Rempel March 3, 2017 at 9:33 am #

        Murali,

        Hiding rows will not work since the photos are not totally captured withing the named cells. Hiding the worksheet with them on it is the best alternative. You can also “very hide” the worksheet (a trick that I will be doing a video on soon) so that even the nosy ones won’t find it. To do so, open the VBA application using the the keyboard shortcut Alt + F11, select the sheet that the photos are on, then in the properties below, next to “visible”, click the drop down arrow and choose “sheetveryhidden”. This way, if someone right clicks on any tab and chooses “unhide” it will not appear in that dialog box.

  12. Cynthia Campbell March 13, 2017 at 2:13 pm #

    Michael,

    First of all, thank you for posting this – it has been very helpful. I am using it to place operator signatures and checker initials at the bottom of test reports for our Mechanical Testing lab.

    However, I am experiencing an issue where sometimes the signatures are larger than the “picture” where they show up (essentially the signature gets “cropped” and/or is not centered in the picture” box). I made sure that all my signatures (kept in a separate spreadsheet tab) are fully contained within the cell they are placed in, as you instructed.

    The only way I have found to rectify the issue is to shrink the size of the .jpg inside the cells where the signatures reside (in the separate tab). But I don’t think this is the proper way to fix this problem…

    I feel like there are some kind of “properties” settings that are necessary to set (for the signature box), in order for the entire photo to be shown…(?) I already tried messing with the properties including the “crop” settings, but that didn’t fix it!

    Do you have any ideas as to what could be happening?

    I can send you an example file if you want to look at it.

    I’m using Excel 2013.

    Thanks in advance!
    Cindy

    • Michael Rempel March 13, 2017 at 5:52 pm #

      Cynthia, what I usually do is:

      1) select all the objects – F5 – Special – Objects – OK
      2) in for Format tab, go to the “Size” group and click on the dialog box launcher in the lower right corner
      3) here you can adjust the height and width of all objects at once so they are all the same size
      4) while objects are still selected, in the Format tab, in the Arrange group, click on Align and align them left (or right)
      5) then go through to make sure they don’t need to be moved up or down to keep them within the row height

      Feel free to send me your file if you want me to look at it. mrempel@excel-bytes.com

  13. Yves D June 15, 2017 at 12:27 pm #

    Good morning

    Thank you for this video it is amazingly helpful. There is only one issue that took me a couple hours to track down that I ran into. In the part under “Formula” then “Create from selection” then “Left column”, if the left column names start or end with a number or has a dash in it (e.g. a5 or 38LL or SS-TT) then the right column has a name that has a “_” added to it (e.g. a5_ and _38LL and SS_-TT) which are not recognized in the indirect process so there is no connection created and no display. Thanks again for a very nice instructional video. Y

    • Michael Rempel June 18, 2017 at 4:43 pm #

      Yes, Excel is a bit picky about naming conventions. You can’t start with a number – it will automatically insert an underscore to start. Glad you found the video useful.

Trackbacks/Pingbacks

  1. Photos Tied To Dropdown List UPDATE in #Excel | Excel Bytes - February 23, 2015

    […] Here is the link to the original blog post video. […]

  2. How To Tie Photos To A Dropdown List Using Excel 2007 | Excel Bytes - July 23, 2015

    […] Photos Tied To A Dropdown List Photos Tied To A Dropdown List UPDATE […]

  3. How To Sort Data With Photos or Images In Excel | Excel Bytes - November 3, 2015

    […] Excel 2010 or 2013, click here or here for the updated […]

  4. The Two Most Common Errors When Selecting Photos From A Drop Down List In Excel | Excel Bytes - January 27, 2016

    […] For Excel 2010 & 2013 […]

Leave a Reply