Excel's Latest Feature: Placing Pictures Inside of Cells - Part 2
Combining Dynamic Arrays with Pictures Inside of Cells.
Want to get the most out of this lesson? Download the sample data at the end of the lesson so that you can try this feature out!
In our last lesson we went over an introduction of Microsoft’s latest feature: placing pictures inside of cells. As mentioned, this opens up a TON of possibilities when it comes to how we can use pictures to make our Excel sheets more dynamic and interactive.
In todays edition we are going to jump into some of the more dynamic and interactive possibilities that this feature can create. All thanks to dynamic spill arrays!
XLOOKUP + Pictures
As you may or may not know I am a HUGE fan of Football (the American kind). There are 32 team, broken into 2 major conferences, which are then broken into 4 divisions each. The four division are ROUGHLY distributed by geography. Each team also has a designated logo. Here is my data set:
Let’s jump into a really basic example - using =XLOOKUP() to find a teams logo.
Step 1: Create a data validation drop down list.
Step 2: Use =XLOOKUP() to find the logo. In this case:
lookup_value: Set on the data validation drop down list.
lookup_array: The list of team names found in column A of this example.
return_array: The list of logos found in column B of this example.
The final syntax in the example looks like this:
=XLOOKUP(E4, A:A, B:B)
With this setup you have now created a simple pull down which returns the logo of the team you have chosen!
Reversing the Process - Lookup by Picture
Once I did this it left me wondering - can I reverse the process and lookup the team name by logo? The answer is yes, but in a different way.
If we try and create a data validation list based upon the pictures, Excel returns the following:
Not very helpful, right? So we won’t be able to use Data Validation to create a list of logos. What we CAN do is insert the logo picture into a cell with an =XLOOKUP() function to return the team name.
lookup_value: Set on a cell where we will insert a logo picture. In this case E7.
lookup_array: The list of logos found in column B of this example.
return_array: The list of team names found in column A of this example.
The final syntax will look like this:
=XLOOKUP(E7, B:B, A:A)
Now when we place a logo into the cell, the team name appears. In this case I did a copy & paste.
Combining =SORT() & =FILTER() to create a super dynamic list
Incorporating =SORT() & =FILTER() can take this new feature to another level. I mentioned earlier that teams were divided into two conferences (known as the AFC & NFC) and further into 4 divisions in each conference roughly based on geography. That spreads the 32 teams over 8 divisions effectively. Using =SORT() & =FILTER() we can create a dynamic list that displays each of the teams and their logos based on the division we choose.
Step 1: Create a data validation list based on the divisions.
Step 2: Use the =FILTER() function to display only the names & logos of the teams in the division chosen. Here is the syntax breakdown for =FILTER():
array: The columns with the names & logos we want to return. In this case columns A & B.
include: The data in the column that equals the chosen division. In this case we want the data in column C to equal the data in cell E11, where the data validation menu is.
After we do this we want to nest this function inside of =SORT() in order to return the list in alphabetical order.
The final syntax looks like this:
=SORT(FILTER(A:B, C:C=E11))
Now when you choose a division, Excel updates both the list and the logos!
Thanks for reading this lesson which expanded on the new picture feature in Excel. Also, I appreciate your patience in waiting for this edition to come out. I enjoyed some much needed down time with my family.
Make sure that you practice using this feature. This is easy to do - just download the sample data set right here: