Display pictures on your Excel Dashboard using an Excel IMAGE formula. This episode has a deeper dive into the function and how to use it.
(0:00) Welcome
(0:24) Finding Image Address
(0:54) Building Image URL
(1:30) Using IMAGE function
(1:52) Hover for Pop-out Image
(2:30) Pivot Table
(2:55) Formula to Display All Images
(3:04) FILTER to convert to array
(3:15) IMAGE function
(3:20) TRANSPOSE for 2501 Bug
(3:35) WRAPROWS function
(3:45) Excel MVP by Country
(4:10) Caption under picture but how?
(5:19) Caching images #BUSY! error
(5:57) Insiders beta now
(0:00) Welcome
(0:24) Finding Image Address
(0:54) Building Image URL
(1:30) Using IMAGE function
(1:52) Hover for Pop-out Image
(2:30) Pivot Table
(2:55) Formula to Display All Images
(3:04) FILTER to convert to array
(3:15) IMAGE function
(3:20) TRANSPOSE for 2501 Bug
(3:35) WRAPROWS function
(3:45) Excel MVP by Country
(4:10) Caption under picture but how?
(5:19) Caching images #BUSY! error
(5:57) Insiders beta now
Transcript of the video:
Hey, welcome back to the MrExcel netcast episode 2500.
Let's take a deeper dive into the image function in Excel. This was something that came out as I was doing 2499, and I threw a quick little example in there at the end, but I've had some time now to play with this a little bit more.
I have this great database that I downloaded from Microsoft of all the MVPs, and it has a URL like this.
So we come out to Chrome and we see that there is an image on this page.
So I'm going to right-click and say, copy the image address.
And then come back to Excel and paste, right?
And what we see is that it's going to be fairly consistent in that they have this prefix. And then at the end, they have the MVP ID, right?
So in my database over here, luckily I have that MVP ID.
So to come up with a column of the photo URL out here.
It's just that prefix, ampersand, whatever the ID is.
And now I have a whole bunch of photo URLs.
I know a lot of people want this to work on their local hard drive.
And when I originally did it for a client, Jerry, back in 2002 or 2003. We were using images on the local hard drive.
But today, with every company having a website, it makes a lot more sense.
Those products are probably going to be available on the web and there might be a nice, easy way to convert the part number into an image URL.
So for me, I'm perfectly happy with this current plan. All right.
So we just put the image function in.
There are optional arguments out there for alt text, sizing, height, width, but it just works, right?
We don't need to do that. It always appears really small.
And so, you want to take the row height, increase it, column width and increase it.
And you get a nice image there. Oh, now this was driving me nuts.
At first, every time I would go above that cell hover above that cell, I get a larger version of the image. Why are they doing this?
But then I realized that in this case, I just have one image.
So I'm making it large, but there's going to be a lot of times where we just have a database, right?
And now we have all of these tiny images.
And if we want to jump in here and get a larger picture, it makes sense that we can hover like that.
So I understand the pop-out.
Boy, it'd be nice if there was a way that we could turn it off for the dashboard situations where we were doing things larger.
All right. So I took this data here.
I ran it into a pivot table, a very simple pivot table with just photo URL.
And then I did an X lookup to go back and grab the name next to each URL.
I have slicers down here first to get just the Excel MVPs and then by region and country.
And what you'll see over here in cell L47 is one formula.
Now this formula has four different functions in it and will work from the inside out.
The first thing I want to do is take those pivot table results.
I don't know how many there's going to be and convert it into an array.
So I use the filter function to get everything work column A is greater than quote, quote.
Then I send that into the image function and that converts them to images.
Then just to handle the bug in episode 2501, which I've already posted, use a transpose that prevents these four images from going down the page, Joe McDade from Microsoft confirms, that's a bug and that'll be fixed.
And then finally wrap rows into four with a quote, quote to handle anything that happens to have five or something like that, right?
And what we get now is we get to this beautiful little dashboard where I can choose from the slicer.
You'll recognize a lot of your favorite YouTube Excelers out here as I go through country by country.
And you see that it automatically takes the data from the slicer and gives me images of those folks.
What an amazing, amazing tool this is going to be.
Now, I'll tell you the one thing that I really would like to have done is I ended up putting the names up here in a text join, and I thought I would've been really cool to have each picture and then immediately underneath the picture, the name, right?
So I try to do equal image ampersand CHAR 10, to go to a new line, ampersand the name.
But unfortunately, that doesn't work anytime I try and can catenate the image with a text.
It's giving me the value error, and maybe I can write one of those new lambda functions that to...
What would be map, I think maybe where I'd go through each item in the pivot table and basically create two cells from that one, with the image, and then the next cell with the name, probably if I was doing this for a client.
We'd have to try and figure out something like that.
But here, just for this quick little example, it is cool, right?
Very, very cool like that. So great.
I now have a beautiful little tool here to show all the Excel MVPs.
I can do it by country where we can just get the whole list and what I didn't realize.
I've actually been working on this workbook for a few days and the pictures are very, very fast, but then I copied it to a new workbook for the podcast episode.
And the very first time that I rendered this, it showed up with a busy error for oh, a good 30, 45 seconds while it downloaded all of the images, right?
It compresses them, but still to get them in here, it takes a bit of time.
But then once they're there, I mean, it's very, very fast.
It's obviously cached somewhere in the workbook. So awesome little tool.
Now this image function just came to insiders fast in the beta channel this week.
It will probably be a few months before it gets to the current channel, the monthly channel, and then onto the semi-annual channel, probably what July of 2023.
Just guessing maybe if we're lucky, January 2023, but probably seems like it'll go to the monthly channel for a while.
Let people test it out and then everyone else will see it in 2023.
All right. Well, hey, I want to thank you for stopping by.
We'll see you next time for another nextcast from MrExcel.
Let's take a deeper dive into the image function in Excel. This was something that came out as I was doing 2499, and I threw a quick little example in there at the end, but I've had some time now to play with this a little bit more.
I have this great database that I downloaded from Microsoft of all the MVPs, and it has a URL like this.
So we come out to Chrome and we see that there is an image on this page.
So I'm going to right-click and say, copy the image address.
And then come back to Excel and paste, right?
And what we see is that it's going to be fairly consistent in that they have this prefix. And then at the end, they have the MVP ID, right?
So in my database over here, luckily I have that MVP ID.
So to come up with a column of the photo URL out here.
It's just that prefix, ampersand, whatever the ID is.
And now I have a whole bunch of photo URLs.
I know a lot of people want this to work on their local hard drive.
And when I originally did it for a client, Jerry, back in 2002 or 2003. We were using images on the local hard drive.
But today, with every company having a website, it makes a lot more sense.
Those products are probably going to be available on the web and there might be a nice, easy way to convert the part number into an image URL.
So for me, I'm perfectly happy with this current plan. All right.
So we just put the image function in.
There are optional arguments out there for alt text, sizing, height, width, but it just works, right?
We don't need to do that. It always appears really small.
And so, you want to take the row height, increase it, column width and increase it.
And you get a nice image there. Oh, now this was driving me nuts.
At first, every time I would go above that cell hover above that cell, I get a larger version of the image. Why are they doing this?
But then I realized that in this case, I just have one image.
So I'm making it large, but there's going to be a lot of times where we just have a database, right?
And now we have all of these tiny images.
And if we want to jump in here and get a larger picture, it makes sense that we can hover like that.
So I understand the pop-out.
Boy, it'd be nice if there was a way that we could turn it off for the dashboard situations where we were doing things larger.
All right. So I took this data here.
I ran it into a pivot table, a very simple pivot table with just photo URL.
And then I did an X lookup to go back and grab the name next to each URL.
I have slicers down here first to get just the Excel MVPs and then by region and country.
And what you'll see over here in cell L47 is one formula.
Now this formula has four different functions in it and will work from the inside out.
The first thing I want to do is take those pivot table results.
I don't know how many there's going to be and convert it into an array.
So I use the filter function to get everything work column A is greater than quote, quote.
Then I send that into the image function and that converts them to images.
Then just to handle the bug in episode 2501, which I've already posted, use a transpose that prevents these four images from going down the page, Joe McDade from Microsoft confirms, that's a bug and that'll be fixed.
And then finally wrap rows into four with a quote, quote to handle anything that happens to have five or something like that, right?
And what we get now is we get to this beautiful little dashboard where I can choose from the slicer.
You'll recognize a lot of your favorite YouTube Excelers out here as I go through country by country.
And you see that it automatically takes the data from the slicer and gives me images of those folks.
What an amazing, amazing tool this is going to be.
Now, I'll tell you the one thing that I really would like to have done is I ended up putting the names up here in a text join, and I thought I would've been really cool to have each picture and then immediately underneath the picture, the name, right?
So I try to do equal image ampersand CHAR 10, to go to a new line, ampersand the name.
But unfortunately, that doesn't work anytime I try and can catenate the image with a text.
It's giving me the value error, and maybe I can write one of those new lambda functions that to...
What would be map, I think maybe where I'd go through each item in the pivot table and basically create two cells from that one, with the image, and then the next cell with the name, probably if I was doing this for a client.
We'd have to try and figure out something like that.
But here, just for this quick little example, it is cool, right?
Very, very cool like that. So great.
I now have a beautiful little tool here to show all the Excel MVPs.
I can do it by country where we can just get the whole list and what I didn't realize.
I've actually been working on this workbook for a few days and the pictures are very, very fast, but then I copied it to a new workbook for the podcast episode.
And the very first time that I rendered this, it showed up with a busy error for oh, a good 30, 45 seconds while it downloaded all of the images, right?
It compresses them, but still to get them in here, it takes a bit of time.
But then once they're there, I mean, it's very, very fast.
It's obviously cached somewhere in the workbook. So awesome little tool.
Now this image function just came to insiders fast in the beta channel this week.
It will probably be a few months before it gets to the current channel, the monthly channel, and then onto the semi-annual channel, probably what July of 2023.
Just guessing maybe if we're lucky, January 2023, but probably seems like it'll go to the monthly channel for a while.
Let people test it out and then everyone else will see it in 2023.
All right. Well, hey, I want to thank you for stopping by.
We'll see you next time for another nextcast from MrExcel.
