Microsoft Excel proposes a lot of different features for creating a perfect presentation of your data. One of these features is linked elements. This tip shows how to create a drop-down list and automatically show linked objects, such as image, text, hyperlink, etc. for the selected drop-down list item.
For example, if you want to show the linked image and text for the selected item:
To create a drop-down list with linked images, do the following:
I. Create a drop-down list from your data [see Creating a drop-down list in a cell for more details]:
In this example the drop-down list was created in the List sheet:
II. To show the linked picture of the selected item, do the following:
1. Create the name range of pictures:
1.1. On the Formulas tab, in the Defined Names group, click Define Name [see Creating and using named ranges for more details]:
1.2. In the New Name dialog box:
- In the Name field, enter the name of this new range, for example, Pictures,
- In the Refers to field, enter the formula:
=INDEX[<list of pictures>, MATCH[<selected item>, <list of items>, 0]]
In this example:
=INDEX[Data!$C$3:$C$5, MATCH[List!$B$3, Data!$B$3:$B$5, 0]]
2. Select any picture and copy it by doing one of the following:
- On the Home tab, in the Clipboard group, click the Copy button:
- Right-click in the selection and choose Copy in the popup menu,
- Click Ctrl+C.
3. Select the cell where you want to show the linked picture.
4. On the Home tab, in the Clipboard group, click on the Paste list and then choose one of the options:
- Keep Source Formatting:
- Picture:
5. Select the pasted picture and in the Formula Bar type the name of your range [in this example, Pictures]:
III. To show the linked text of the selected item, do the following:
On the appropriate cell, where you want to see the linked text, enter the following formula:
=VLOOKUP[<selected item>, <data table>, <column to show>, <table option>]
Where <table option> should be:
- TRUE, if items in your table are sorted,
- FALSE, if items should find an exact match.
In this example:
= VLOOKUP[B3, Data!$B$3:$D$5, 3, FALSE]
See also this tip in French: Comment afficher l’image liée à l’élément de liste.
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.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, chartsand anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar [easily edit multiple lines of text and formula]; Reading Layout [easily read and edit large numbers of cells]; Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows [all cells are empty]; Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter [save and apply filter schemes to other sheets]; Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Read More... Free Download... Purchase...
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!
Read More... Free Download... Purchase...
Sort by Newest
symbols left.
You are guest
or post as a guest, but your post won't be published automatically. Post
Loading comment... The comment will be refreshed after 00:00.
To post as a guest, your comment is unpublished.
how do i repeat the last method in next cell
To post as a guest, your comment is unpublished.
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!
To post as a guest, your comment is unpublished.
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.
To post as a guest, your comment is unpublished.
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
To post as a guest, your comment is unpublished.
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
To post as a guest, your comment is unpublished.
I can get everything to work except the last step when I name the image.I get a "reference not valid" statement
To post as a guest, your comment is unpublished.
Before this step, you have to match correctly Fruits and Pictures.
Good luck !
To post as a guest, your comment is unpublished.
Thank you so much.
It really cool technique.To post as a guest, your comment is unpublished.
I can get everything to work except the last step when I name the image.I get a reference not valid...any thoughts?
To post as a guest, your comment is unpublished.
I have the same error as JAson. Some basic settings different?