Skip to main content

How to create drop down list with images in Excel?

Author: Xiaoyang Last Modified: 2021-07-06

In Excel, we can quickly and easily create a drop down list with cell values, but, have you ever tried to create a drop down list with images, that is to say, when you click one value from the drop down list, its relative image will be displayed at once as below demo shown. In this article, I will talk about how to insert a drop down list with images in Excel.

Create a drop down list with image by Named Range feature

Create multiple drop down lists with images by a powerful feature

Insert multiple pictures based on cell values with a useful feature


Create a drop down list with image by Named Range feature

Normally, you can create a named range, and then use the linked picture for solving this task in Excel.

1. First, you should create two columns which contain the cell values and their relative images, see screenshot:

Note: To insert multiple images based on the cell values quickly and easily, please click to view here.

2. Then, you need to create a drop down list with the cell values, please click one cell where you want to output the drop down list value, and then click Data > Data Validation > Data Validation, see screenshot:

3. In the Data Validation dialog box, under the Settings tab, choose List from the Allow drop down list, and then select the cell values that you want to create the drop down list under Source section, see screenshot:

4. Then click OK button, and the drop down list with the cell values has been created, select one item from the drop down list, see screenshot:

5. Then, click Formulas > Define Name, see screenshot:

6. In the New Name dialog box, please do the following operations:

  • Enter a name called myimage into the Name text box;
  • Then copy and paste this formula: =INDEX(Sheet1!$A$2:$B$6,MATCH(Sheet1!$E$2,Sheet1!$A$2:$A$6,0),2) into the Refers to text box.

Note: In the above formula:

  • Sheet1!$A$2:$B$6: is the worksheet and range which contains the values and pictures you want to use;
  • Sheet1!$E$2: is the cell within the worksheet where you have created the drop down list;
  • Sheet1!$A$2:$A$6: is the list of cells that you are created the drop down list based on;
  • The variable number 2 is the column number containing the images. If your images are in column C, you should enter 3.

7. And then click OK to close the dialog. Then you need to copy and paste the corresponding picture based on the cell value in E2 from the original range to the cell F2, see screenshot:

8. Next, you should select the picture in cell F2, and then enter this formula =myimage (myimage is the range name that you named in step 5)into the formula bar, see screenshot:

9. After entering the formula, press Enter key, and now, when you choose one item from the drop down list, its relative picture will be displayed immediately. See screenshot:


Create multiple drop down lists with images by a powerful feature

If you have Kutools for Excel, with its Picture Drop-down List feature, you can create multiple drop down lists with their relative images with only several clicks.

Note:To apply this Picture Drop-down List, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

1. First, you should create two columns which contain the values and relative images in your worksheet.

2. And then, click Kutools > Drop-down List > Picture Drop-down List, see screenshot:

3. In the Picture Drop-down List dialog box, if you have created the columns of cell values and images, please ignore step1, then, select the original data and image range and the output range, see screenshot:

4. And then, click Ok button, the drop down lists with images have been created into the selected cells, and when selecting an item from the drop down cell, its corresponding picture will be displayed as below demo shown:

Free Download Kutools for Excel Now!


Insert multiple pictures based on cell values with a useful feature

To quickly insert the relative images based on the cell values without inserting them one by one, I can recommend a powerful tool-Kutools for Excel, with its Match Import Pictures utility, you can finish this job conveniently.

After installing Kutools for Excel, please do as this:

1. Click Kutools Plus > Import & Export > Match Import Pictures, see screenshot:

2. In the Match Import Pictures dialog box, please do the following operations:

  • Select the cell values that you want to insert the pictures based on under the Match range textbox;
  • Then click Add button to choose and insert the corresponding pictures into the list box;
  • And then click Import size button to specify the size for the pictures in the popped out Import Picture Size dialog box;
  • At last, click Import button.

3. Then, in the popped out dialog box, choose a cell where you want to insert the pictures, see screenshot:

4. Click OK, the pictures have been inserted into the specific cells which are consistent with the cell values.

Free Download Kutools for Excel Now!


More relative articles:

  • Increase Drop Down List Font Size In Excel
  • Drop down list is a very helpful feature in Excel, when you create drop down list in your worksheet, have you ever tried to increase the font size of the drop down list to make the selected record larger and more readable as left screenshot shown?
  • Create Multi Level Dependent Drop Down List In Excel
  • In Excel, you may create a dependent drop down list quickly and easily, but, have you ever tried to create multi-level dependent drop down list as following screenshot shown? This article, I will talk about how to create a multi-level dependent drop down list in Excel.
  • Highlight Rows Based On Drop Down List In Excel
  • This article will talk about how to highlight rows based on drop down list, take the following screenshot for example, when I select “In Progress” from the drop down list in column E, I need to highlight this row with red color, when I select “Completed” from the drop down list, I need to highlight this row with blue color, and when I select “Not Started”, a green color will be used to highlight the row.
  • Create Drop Down List But Show Different Values In Excel
  • In Excel worksheet, we can quickly create a drop down list with the Data Validation feature, but, have you ever tried to show a different value when you click the drop down list? For example, I have the following two column data in Column A and Column B, now, I need to create a drop down list with the values in Name column, but, when I select the name from the created drop down list, the corresponding value in Number column is displayed as following screenshot shown. This article will introduce the details to solve this task.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (10)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
how do i repeat the last method in next cell
This comment was minimized by the moderator on the site
Hello, guys,
Sorry for this, the method is only applied to one cell.
If anyone have the good method to make this work for a column, please comment here!
Thank you!
This comment was minimized by the moderator on the site
I found it difficult to follow but managed it. I want to know how I can make a full column have this option though, not just one cell. Can anybody assist please? Thanks.
This comment was minimized by the moderator on the site
Hi, Nice add on to Excel. I had also the same reference error. =INDEX(Sheet1!$A$2:$B$6,MATCH($A$2,Sheet1!$A$2:$A$6,0),2) The comma , should be written as a semicolon like ; Further had to read it lots of time. I think that it should be rewritten in more clear instruction text. I will help you with that if you like. I have also one question: When I want to insert a row in my target worksheet the formula is not working. I use the following formula: =INDEX(Pictos!$A$2:$B$11;VERGELIJKEN($D5;Pictos!$A$2:$A$11;0);2) Translate into English: =INDEX(Pictos!$A$2:$B$11;MATCH($D5;Pictos!$A$2:$A$11;0);2) What I do wrong? Best regards, Ed Boon
This comment was minimized by the moderator on the site
Hi, Nice add on to Excel. I had also the same reference error. =INDEX(Sheet1!$A$2:$B$6,MATCH($A$2,Sheet1!$A$2:$A$6,0),2) The comma , should be written as a semicolon like ; Further had to read it lots of time. I think that it should be rewritten in more clear instruction text. I will help you with that if you like. I have also one question: When I want to insert a row in my target worksheet the formula is not working. I use the following formula: =INDEX(Pictos!$A$2:$B$11;VERGELIJKEN($D5;Pictos!$A$2:$A$11;0);2) Translate into English: =INDEX(Pictos!$A$2:$B$11;MATCH($D5;Pictos!$A$2:$A$11;0);2) What I do wrong? Best regards, Ed Boon
This comment was minimized by the moderator on the site
I can get everything to work except the last step when I name the image.I get a "reference not valid" statement
This comment was minimized by the moderator on the site
Before this step, you have to match correctly Fruits and Pictures.
Good luck !
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations