564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|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!|
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 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).
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:
Here we're displaying each image with its original aspect ratio, but to fit inside the cell (ie using the default sizing option 0).
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):
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
ImagePath = ImagePath1
'display this image in target cell
Dim imageFormula As String
imageFormula = "=image(""" & ImagePath & """,""Picture"",1)"
ImageCell.Formula2R1C1 = imageFormula
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:
25 Aytoun Street