Microsoft Excel Tutorial - new feature to place a picture in a cell in Excel.
Can the Excel IMAGE function use a local hard drive? Not yet.
New Excel VBA method: InsertPictureInCell
Insert Many Pictures in Excel in one command.
Feature Requests:
Expose a VBA property to show original source of the image
Allow IMAGE function to point to local hard drive.
Allow Power Query to load in-cell images.
To download this workbook: Excel - Picture Place In Cell - Episode 2606 Sample Files - MrExcel Publishing
Table of Contents
(0:00) New Picture In Cell feature
(0:33) Pictures on drawing layer
(0:47) Inserting image in cell in Excel
(1:05) Fitting in Cell
(1:20) Alignment
(1:35) No apparent way to rotate image
(2:02) Use Case from Jerry
(3:48) Using VBA Macro Recorder
(4:11) Inserting 400 images
(4:36) VBA InsertPictureInCell
(4:53) Is there a VBA Property?
(5:07) Sorting images with cells
(5:39) Using a formula to refer to image
(6:00) File Size concerns
(6:30) Really need IMAGE formula for local drive
(7:04) Feature Request: Power Query support for images
(7:25) Wrap-up
Can the Excel IMAGE function use a local hard drive? Not yet.
New Excel VBA method: InsertPictureInCell
Insert Many Pictures in Excel in one command.
Feature Requests:
Expose a VBA property to show original source of the image
Allow IMAGE function to point to local hard drive.
Allow Power Query to load in-cell images.
To download this workbook: Excel - Picture Place In Cell - Episode 2606 Sample Files - MrExcel Publishing
Table of Contents
(0:00) New Picture In Cell feature
(0:33) Pictures on drawing layer
(0:47) Inserting image in cell in Excel
(1:05) Fitting in Cell
(1:20) Alignment
(1:35) No apparent way to rotate image
(2:02) Use Case from Jerry
(3:48) Using VBA Macro Recorder
(4:11) Inserting 400 images
(4:36) VBA InsertPictureInCell
(4:53) Is there a VBA Property?
(5:07) Sorting images with cells
(5:39) Using a formula to refer to image
(6:00) File Size concerns
(6:30) Really need IMAGE formula for local drive
(7:04) Feature Request: Power Query support for images
(7:25) Wrap-up
Transcript of the video:
Hey, hey. All right.
We got a new feature, it's called Pictures Place in Cell From This Device.
What I really wanted was the new image function to be able to use the local hard drive.
And that's not working.
The great news is though, Insert Picture in Cell is a new VBA method so we can have new ways to insert from VBA, and let's just look at this.
It's finally a way to insert many pictures at once, which is great.
But still why not as a formula or why not from Power Query?
All right, so let's take a look at this.
Historically, pictures are on a drawing layer above the grid. They're floating there.
If there were numbers behind this, you can't see the numbers because the pictures are on top.
So, it's a drawing layer above the grid.
Well now, a picture can live inside the cell and we've been able to do this for a few months with the image function.
So, Insert, Pictures, Place in Cell, from This Device.
I have a folder here of images and if I just choose one of these, click Insert.
You see that they fill the entire width of the cell.
So if I make the cell wider, it becomes, well a little bit larger, I'd have to make the cell taller as well. And it's cool, it follows the alignment.
So if I align top, it moves to the top. If I align bottom, it moves to the bottom.
Align, was that middle? I think.
Also if I would constrain the height and make it taller, then I can use left align and right align. I can even fill, add a fill color behind.
Now one problem we have is that Oz is saying that some of his pictures are coming in sideways.
And so I thought, oh well this would be great.
We can just use the alignment, use Rotate Text Up, which should be able to turn the picture sideways. But that does not seem to be working, right?
So it's cool definitely.
But what I really wanted, this goes back to a beautiful phone call that I received from Jerry, my friend Jerry in 2002.
He says, look, I have a chain of retail stores.
I need you to import a sales report to Excel, find the top-selling items last week, and then display their pictures in Excel.
And he said, everyone who's using this have the images on their hard drive.
If you find that the top-selling item is J12345, then you can find the image at C:\qimage\qiJ12345. I mean it's easy to figure out.
And I remember this.
I said, "Excel is for numbers, it's not for pictures". He goes, "Well look, I have John Walkenbach's book here.
And on this page he shows the code, the VBA code, how to do it".
And I look up on my bookshelf right above my desk and I have the exact same book.
I pulled the book down.
Sure enough, there's the code to insert pictures in Excel, right?
And that began eight years of me creating amazing reports in Excel using VBA.
And when the image function came out, the very first thing I thought of was Jerry.
I called Jerry, I said, you were 20 years ahead of your time wanting to display these pictures in Excel.
I said, unfortunately it has to come from a website, not from the local hard drive, which of course isn't a problem today because all of those images are on the website.
But what I really wanted was a formula where I could point to C:\qimage\qiJ12345.
And it's safe in this company because everyone has the exact same files.
They have an amazing file syncing system. Everyone's laptop has them.
So I have to tell you, when I saw Place in Cell and that is not using a formula, I was just horribly disappointed. But let's take a look at what we do have.
I mean there's plenty of uses for what they gave us.
I'm going to record a macro how to insert many images. Click okay.
And then Insert, Picture, Place in Cell, from This Device and I'm going to choose them all.
And it's doing it. It's downloading those images into this file.
Great. So it does it.
Let's take a look at the VBA code.
There's our new method, InsertPictureInCell with the path and file name.
Except for the VBA recorder, I can't find any way to get this path and file name from the image.
If I look in the formula bar, it just says Picture. So where have they hidden where this came from?
It's like when you have a hyperlink in a cell to be able to get the actual URL out of that hyperlink. You have to use VBA.
Is there a VBA property for this? I don't know.
All right, but let's look at the positive things.
So what I have here is a list of 400 file names over in column A, the file size, I got that from Power Query and then the image and you need to be really careful.
There were a few problems here, especially with numbers where it wasn't sorting the same way. So I had to move a few of those around.
But now that I have it, I should be able to sort by size, data, Z to A.
And it sorts with the rows. That's cool.
We'll come back here and sort alphabetically and it sorts correctly.
You can even have a little formula over here. So equal this.
And that formula will return the image, it will follow the same alignment as the image itself.
Of course an X lookup or filter would all work.
One concern that I have is the pictures that are in cell are full size, they're saved at full size.
So here this is a picture that I inserted in a cell and this is the same picture inserted over the cells.
PlacePictureOverCells has been around forever and it's automatically compressing the picture, apparently. PictureInCell is using the whole thing.
So this file where I just added 400 images, it's going to be massive because it's storing all of those images inside the XML of the workbook.
So it is great that Microsoft gave us this new feature and there's lots of reasons why we could use it, but wow, I really, really need a way to use the image function point to a path, point to a file name and have that come in as the image that would be so useful.
And when they had mentioned that they were going to find a way to have this work with local images on the local hard drive, I'm like, oh, this is going to solve a problem that's been a problem for me for 20 plus years.
And all I can do is hope that this bit of it will eventually come.
And same thing here. This is Power Query.
Those are list of all those files in the folder.
Boy, if I put content here and include that in Close & Load, it would be so nice if they would take that binary file and insert it as an image in Excel. That would be very useful.
Well, hey, there you go.
A first look at Insert Pictures Place in Cell From This Device.
Lots of cool functionality there, although I hope they're not. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
We got a new feature, it's called Pictures Place in Cell From This Device.
What I really wanted was the new image function to be able to use the local hard drive.
And that's not working.
The great news is though, Insert Picture in Cell is a new VBA method so we can have new ways to insert from VBA, and let's just look at this.
It's finally a way to insert many pictures at once, which is great.
But still why not as a formula or why not from Power Query?
All right, so let's take a look at this.
Historically, pictures are on a drawing layer above the grid. They're floating there.
If there were numbers behind this, you can't see the numbers because the pictures are on top.
So, it's a drawing layer above the grid.
Well now, a picture can live inside the cell and we've been able to do this for a few months with the image function.
So, Insert, Pictures, Place in Cell, from This Device.
I have a folder here of images and if I just choose one of these, click Insert.
You see that they fill the entire width of the cell.
So if I make the cell wider, it becomes, well a little bit larger, I'd have to make the cell taller as well. And it's cool, it follows the alignment.
So if I align top, it moves to the top. If I align bottom, it moves to the bottom.
Align, was that middle? I think.
Also if I would constrain the height and make it taller, then I can use left align and right align. I can even fill, add a fill color behind.
Now one problem we have is that Oz is saying that some of his pictures are coming in sideways.
And so I thought, oh well this would be great.
We can just use the alignment, use Rotate Text Up, which should be able to turn the picture sideways. But that does not seem to be working, right?
So it's cool definitely.
But what I really wanted, this goes back to a beautiful phone call that I received from Jerry, my friend Jerry in 2002.
He says, look, I have a chain of retail stores.
I need you to import a sales report to Excel, find the top-selling items last week, and then display their pictures in Excel.
And he said, everyone who's using this have the images on their hard drive.
If you find that the top-selling item is J12345, then you can find the image at C:\qimage\qiJ12345. I mean it's easy to figure out.
And I remember this.
I said, "Excel is for numbers, it's not for pictures". He goes, "Well look, I have John Walkenbach's book here.
And on this page he shows the code, the VBA code, how to do it".
And I look up on my bookshelf right above my desk and I have the exact same book.
I pulled the book down.
Sure enough, there's the code to insert pictures in Excel, right?
And that began eight years of me creating amazing reports in Excel using VBA.
And when the image function came out, the very first thing I thought of was Jerry.
I called Jerry, I said, you were 20 years ahead of your time wanting to display these pictures in Excel.
I said, unfortunately it has to come from a website, not from the local hard drive, which of course isn't a problem today because all of those images are on the website.
But what I really wanted was a formula where I could point to C:\qimage\qiJ12345.
And it's safe in this company because everyone has the exact same files.
They have an amazing file syncing system. Everyone's laptop has them.
So I have to tell you, when I saw Place in Cell and that is not using a formula, I was just horribly disappointed. But let's take a look at what we do have.
I mean there's plenty of uses for what they gave us.
I'm going to record a macro how to insert many images. Click okay.
And then Insert, Picture, Place in Cell, from This Device and I'm going to choose them all.
And it's doing it. It's downloading those images into this file.
Great. So it does it.
Let's take a look at the VBA code.
There's our new method, InsertPictureInCell with the path and file name.
Except for the VBA recorder, I can't find any way to get this path and file name from the image.
If I look in the formula bar, it just says Picture. So where have they hidden where this came from?
It's like when you have a hyperlink in a cell to be able to get the actual URL out of that hyperlink. You have to use VBA.
Is there a VBA property for this? I don't know.
All right, but let's look at the positive things.
So what I have here is a list of 400 file names over in column A, the file size, I got that from Power Query and then the image and you need to be really careful.
There were a few problems here, especially with numbers where it wasn't sorting the same way. So I had to move a few of those around.
But now that I have it, I should be able to sort by size, data, Z to A.
And it sorts with the rows. That's cool.
We'll come back here and sort alphabetically and it sorts correctly.
You can even have a little formula over here. So equal this.
And that formula will return the image, it will follow the same alignment as the image itself.
Of course an X lookup or filter would all work.
One concern that I have is the pictures that are in cell are full size, they're saved at full size.
So here this is a picture that I inserted in a cell and this is the same picture inserted over the cells.
PlacePictureOverCells has been around forever and it's automatically compressing the picture, apparently. PictureInCell is using the whole thing.
So this file where I just added 400 images, it's going to be massive because it's storing all of those images inside the XML of the workbook.
So it is great that Microsoft gave us this new feature and there's lots of reasons why we could use it, but wow, I really, really need a way to use the image function point to a path, point to a file name and have that come in as the image that would be so useful.
And when they had mentioned that they were going to find a way to have this work with local images on the local hard drive, I'm like, oh, this is going to solve a problem that's been a problem for me for 20 plus years.
And all I can do is hope that this bit of it will eventually come.
And same thing here. This is Power Query.
Those are list of all those files in the folder.
Boy, if I put content here and include that in Close & Load, it would be so nice if they would take that binary file and insert it as an image in Excel. That would be very useful.
Well, hey, there you go.
A first look at Insert Pictures Place in Cell From This Device.
Lots of cool functionality there, although I hope they're not. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
