NEW IMAGE Function - Insert images in cells with a FORMULA!

Abrine ß X
49 followers
9 Video Views·Oct 18, 2022
Excel finally has a function that enables you to insert images into cells with a formula.
It supports all the standard image file types and you can reference them in formulas, sort
them, filter them, even do a reverse image lookup and more.
Now, it's currently only available in the Microsoft 365 beta channel, so the function
may change slightly before it's generally available as Microsoft take on feedback during
the testing phase.
All right, let's take a look.
The image function syntax starts with the source, which is the URL.
Now you can either reference the cell containing the URL or you can wrap it in double quotes
and enter it directly into the formula.
I'm using a link to an image of the United States flag from flagpedia.net.
I'll just note that the URL path must use the HTTPS protocol and currently it can't
be a SharePoint or OneDrive link and it also doesn't support locally stored files, but
hopefully that changes before it becomes generally available.
Now the alt text is optional.
I'll enter the flag name for completeness.
Now there are several options for the sizing.
The default is zero, that is fit the image to the cell and maintain the aspect ratio.
We can have it to fill the entire cell and you're likely to end up with a distorted image
unless you get lucky with the size of your cell.
We can choose the original size, which will maintain the aspect ratio, but it might spill
out of the cell.
Or you can specify a custom size.
We're going to try that.
With custom size we can then specify the height and width.
Now I happen to know that it's 42 pixels high and 56 wide.
Now there has already been some feedback from MVPs to Microsoft to request that the order
of the height and width arguments are switched around in line with current conventions, so
you may see this change.
So that's my image formula.
We'll press enter and you can see it returns the flag image.
And I've just used formula text here to show the formula.
Images respond to cell alignment settings, so I can center it horizontally and vertically
in the cell.
Hovering over the image pops up the tooltip with a larger image.
In my case it's wasted here because it's already at full size and it's a tiny image, but we'll
see an example later where this is more useful.
Now unfortunately you can't make this pop up any smaller, the pull handles just don't
respond.
But we've given feedback to Microsoft requesting that this can either be adjusted in size or
turned off altogether because it can get annoying.
Images work well in tables too.
Here I've got a list of all the flags available on flagpedia.net.
Let's insert the image and I'm just going to reference the URL column, close my parentheses
and let it set to the default sizing and there I have my flags.
I can sort the flag column, so let's sort them A to Z and you can see it's actually
sorted it based on the URL because we now have United Arab Emirates listed second and
that's because the URL flag code for United Arab Emirates is AE.
The images also respond really well if you filter the rows, so for example let's just
choose a few different flags that are interspersed throughout the table.
And unlike shapes or other objects, here you can't see any remnants of the images sticking
out of the hidden rows as you typically have with image objects.
The images also automatically adjust when you resize cells up to their maximum size
if you omit the size argument as I've done here or if you use the default size argument
of zero.
I can also reference cells containing images in other formulas, so for example if I want
to sort by, so I'm going to sort the flags based on the country name and I want to sort
it in ascending order, you can see sort by returns the flag images.
Or I can use XLOOKUP with image to find the flag for the country in column B, so equals
image, XLOOKUP, I'm going to look up the country in the flag table and return the flag URL,
close parentheses on XLOOKUP, and then for my image alt text I'm just going to skip that,
I'm going to set it to three, and then I want to pick up the height and width from these
cells behind here, and close parentheses on image, and there's our flag.
And now I can change the country selected here, so for example we can choose France,
and I can change the height and width, so we can make it bigger or smaller.
Correspondingly I can look up the image and return the country name, so we can use XLOOKUP
to look up the image in the flag URL table and return the country.
The image function also supports GIF images, however they currently aren't animated.
Let's take a look, so there's my GIF image.
Let's resize it a little bit, and we'll just look at it via the tooltip,
and let's make it really big.
You can see here it's just a static image, but hopefully that will be rectified before
the function is generally available.
By the way, here you can see where the tooltip pane is super helpful.
I can resize it to get a better look, however once I click away it doesn't remember the size,
which is super annoying, another thing I hope they fix.
Now there are a few other limitations that I haven't mentioned.
For example, if the URL that you use requires authentication, then the image won't render.
Moving between Windows and Mac etc may result in irregular image rendering,
and images don't currently display in pivot tables, slices or data validation lists.
Fingers crossed those things get added as well.
I hope you found this tutorial useful.
You can download the Excel file for this lesson from the link here,
and if you like this video please give it a thumbs up and subscribe to our channel for more,
and why not share it with your friends who might also find it useful.
Thanks for watching.
