This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

The new Excel image function shows an image in a cell - and is fun to play with!
You've always been able to add images within worksheets, but now for the first time you can add them within cells - which is strangely satisfying!

Posted by Andy Brown on 01 February 2023

In this blog

The syntax of the IMAGE function
Using images in lists
Making images dynamic

Inserting images in Excel cells using the image function

Some new Excel features are necessary, but boring; others are probably not essential to your work, but bring a smile to your face. This new feature is definitely in the second camp!

One warning: if you type this into an Excel sheet and the argument list doesn't appear, you haven't got the latest update of Excel installed (you may have to wait till it's pushed out to you, although it is possible to accelerate this).

Here's an example showing the pastel de natas a greedy Wise Owl has eaten:

It's a good idea to include the URL of the image you're referencing in a separate cell, as here, to keep your formulae short and simple. See later on this blog for the formulae used.

The syntax of the IMAGE function

The new IMAGE function takes up to 5 arguments:

No.	Argument	Notes on use
1	source	The path (usually a website URL) where the image can be found
2	alt_text	Alternative text describing the image for those with screen readers
3	sizing	Options for how to size the image (see table below for possible values)
4	height	The height to use to display the image
5	width	The width to use to display the image

The sizing argument can take one of the following values:

Option	What it implies
0	Keep the image's aspect ratio (this is the default)
1	Fill the cell with the image (even if this distorts it)
2	Keep the image's original size, even if this means that it goes beyond the edge of the cell
3	Use the specific height and width set in arguments 4 and 5

For the example above, here is the function I used:

I've chosen to fill the cell with the image (so I needed to make sure the height/width ratio of the cell was roughly the same as that of the original image, to avoid distorting it).

One big advantage of filling cells with images is that any background colours don't then show through (these can look messy).

Using images in lists

The new feature means that you can show pictures of people next to their names or products next to their specifications, to choose two obvious examples. Here's how this can work:

Each sport's picture is different.

The formula for the function used for athletics, for example, is:

=IMAGE( $C$12 & $C$13 & B4 &".png", "Picture of " & B4)

Here we're displaying each image with its original aspect ratio, but to fit inside the cell (ie using the default sizing option 0).

Making images dynamic

Sadly there are always people who start doing silly things with new features:

In this system you can click on the button to change the image.

As proof of concept, here's a subroutine in VBA to achieve this (should you wish to you can download this file):

Sub Silly()

Dim ImagePath As String

'images to swap in and out

Const ImagePath1 As String = "Path to Wise Owl image here"

Const ImagePath2 As String = "https://www.nataco.uk/wp-content/uploads/2019/07/Nata_original_2.png"

'the cell to use

Dim ImageCell As Range

Set ImageCell = Range("B2")

'if first click, show Wise Owl logo; otherwise, toggle

If InStr(1, ImageCell.FormulaR1C1, "wiseowl") > 0 Then

ImagePath = ImagePath2

Else

ImagePath = ImagePath1

End If

'display this image in target cell

Dim imageFormula As String

imageFormula = "=image(""" & ImagePath & """,""Picture"",1)"

ImageCell.Formula2R1C1 = imageFormula

End Sub

Like I said, this is a fun new feature of Excel! My next project would be to get the pastel de natas moving round the screen ...

Some other pages relevant to the above blog include:

Excel training

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Inserting images in Excel cells using the image function

The syntax of the IMAGE function

Using images in lists

Making images dynamic

Head office

London

Manchester