Do you ever want to have an image or picture lookup in Excel? Something like below.
In this article, learn how to set up an image lookup using Excel. You can use this to display staff details, product images or machine parts etc.
Video - Dynamic Image Lookups in Excel
If you want to understand the picture lookup technique and see it in action, check out my YouTube video below. For text instructions and images, please read on.
Step 1: Set up your data
To get image lookups, you need to set up your data, ideally in a separate worksheet. The trick is to have one image per cell. Something like this:
Tip: Do not use Tables for storing your data. Instead keep them in a spreadsheet range.
Your images need to fit snugly inside the cell, without touching the boundaries for best results.
Also make sure to turn off gridlines on that spreadsheet tab.
You can do this from View ribbon, as depicted below.
Step 2: Write formula to lookup the image
In a separate worksheet, we will write formula to lookup images. Let’s say we have an input cell with employee name in C5.
We need to get the matching employee picture.
The trick is to write a formula that returns a reference to the image of employee in C5.
You can use either INDEX+MATCH or XLOOKUP.
Let’s use XLOOKUP:
=XLOOKUP(C5, data!$D$3:$D$14, data!$C$3:$C$14)
This will return a reference to the cell with the image of employee in C5.
Of course the result of the formula would be 0, as column C (data!$C$3:$C$14) has no values (just pictures) in it.
Step 3: Insert a named range with the formula
Now that we know what formula to write, go to Formula ribbon and click on Define name button.
- Name will be “employee_picture”
- Refers to will be our XLOOKUP formula from above
See below illustration to understand how the name can be created.
Step 4: Set up a picture link to show image lookup result
This is the last step folks. Go to the data worksheet where you have all the pictures.
- Select any one cell with image and copy it (CTRL+C).
- Go back to the results worksheet, right click anywhere and select linked picture from Paste Special options. See below picture to understand.
- You will get a picture of the selected employee. But this one is special. It is linked to the cell you copied.
- Select the picture and formula bar will show the address of the cell to which it refers.
- Now comes the most important step. Go to formula bar and edit the reference. Type =employee_picture and press enter.
Bingo, you have just created a picture lookup.
If you change the input cell and type a different person name, the lookup image will show that person.
Related: Learn more about Picture Links
Tips on using Image Lookup Technique
Image lookup through picture links offers exciting possibilities. Here are some tips & gotchas you want to keep in mind.
- The picture link images are just like any other images in Excel. So when you select them, you can use picture format ribbon to apply various formatting options to them. For example, you can crop them to shape or apply shadow effect to them.
- Picture links work best if all images are of same size.
- While picture links look great on screen, they tend to be grainy when printed.
- They work well with dashboards too. For example, You can use picture links to display top 3 products or bottom 5 sales persons in dashboards.
- For image lookup scenarios, consider adding a missing person image and use that in XLOOKUP if_not_found parameter. It looks amazing.
⬇ Download Picture Lookup Example File
Click here to download sample file with the picture lookup example. It has few different XLOOKUP formulas, an additional image lookup trick to show team members and more. Examine the named ranges to learn how it works.
Got questions about image lookups? Fire away...
Give picture lookups a try and if you face any issues during implementation, please post a comment here so I can help you.
Also, check out these other types of lookups:
21 Responses to “How to Filter Odd or Even Rows only? [Quick Tips]”
Infact, instead of using =ISEVEN(B3), how about to use =ISEVEN(ROW())
So it takes away any chance of wrong referencing.
I like Daily Dose of Excel
I like it.
Just a heads up, you do need to have the Analysis ToolPak add-in activated to use the ISEVEN / ISODD functions. An alternative to ISEVEN would be:
=MOD(ROW(),2)=0
rather than use a formula, couldn't you enter "true" in first cell and "false" in the second and drag it down and than filter on true or false.
Just for clarification, is Ashish looking to filter by even or odd Characters or rows?
so many functions to learn!
Nice support by chandoo and team as a helpdesk. Give us more to learn and make us awesome. Always be helpful.......
In case you want to delete instead of filter,
IF your data is in Sheet1 column A
Put this in Sheet2 column A and drag down
=OFFSET(Sheet1!A$1,(ROWS($1:1)-1)*2,,)
(This is to delete even rows)
To delete odd rows :
=OFFSET(Sheet1!A$2,(ROWS($1:1)-1)*2,,)
If your numbered cells did not correspond to rows, the answer would be even simpler:
=MOD([cell address],2), then filter by 0 to see evens or 1 to see odds.
I sometimes do this using an even simpler method. I add a new column called "Sign" and put the value of 1 in the first row, say cell C2 if C1 contains the header. Then in C3 I put the formula =-1 * C2, which I copy and paste into the rest of the rows (so C4 has =-1 * C3 and so forth). Now I can just apply a filter and pick either +1 or -1 to see half the rows.
Another way, which works if I want three possibilities: in C2 I put the value 1, in C3 I put the value 2, in C4 I put the value 3, then in C5 I put the formula =C2 then I copy C5 and paste into all the remaining rows (so C6 gets =C3, C7 gets =C4, etc.). Now I can apply a filter and pick the value 1, 2, or 3 to see a third of the rows.
Extending this approach to more than 3 cases is left as an exercise for the reader.
Another way =MOD(ROW();2). In this case, must to choose betwen 1 and 0.
[...] How to Filter Even or Odd rows only [...]
very different style Odd or Even Rows very easy way to visit this site
http://www.handycss.com/tips/odd-or-even-rows/
Thanks for the tip, it worked like magic, saved having to delete row by row in my database.
Thanks!
Thankssssssssssssssss
Hi Chandoo- First of all thanks for the trick. It helped me a lot. Here I have one more challenge. Having filtered the data based on odd. I want to paste data in another sheet adjacent to it. How can I do that?
For Example-
A 1 odd
B 3 odd
C 4 even
D 6 even
I have fileted the above data for odd and want to copy the "This is odd number" text in adjacent/next sheet here. How can I do that. After doing this my data should look like this
A 1 odd This is odd number
B 3 odd This is odd number
C 4 even
D 6 even
Hi! Could you please help me find a formula to filter by language?
Thank you!
Chandoo SIR,
I HAVE A DATA IN EXCEL ROWS LIKE BELOW IS THERE ANY FORMULA OR A WAY WHERE I CAN INSTRUCT I CAN MAKE CHANGES , MEANS I WANT TO WRITE ONLY , THE FIG IS FRESH, BUT IN BELOW ROW IT WILL AUTOMATICALLY TAKE THE SOME WORDS FROM FIGS AND MAKE IN PLURAL FORM , WHILE USING '' ARE'' LIKE BELOW
The fig is fresh - row 1
Figs are fresh - row 2
The Pomegranate is red - row 3
Pomegranates are red - row 4
=IF(EVEN(A1)=A1,"EVEN - do something","ODD - do something else") with iferron (for blank Cell)