Adding pictures into Excel is about to get a lot easier

At long last, there’s an easier way to put images into an Excel workbook. IMAGE() is a new function which imports an external picture into a cell.  We’ll explain how it works and some ways that Image() doesn’t go far enough.

Image() lets you drop an picture into a cell with some options to fit, fill or size the image. Use it to add images of products, staff or, in our case, some amazing people.

The full syntax is:

=IMAGE(source, [alt text], [sizing options], [height], [width]),

source  (required, obviously) a secure URL path to the image file

• Must be a “https://” link
• According to Microsoft “Supported file formats include BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP” . The word ‘include’ suggests that the list of supported formats isn’t complete.
• A disappointing omission from the list is .SVG (aka Microsoft Office icons).

alt text (optional) add alternative text about the image, for accessibility.

Sizing options (optional) choices for Fit, Fill or sizing the image.

0: (the default) Fit the image in the cell and maintain its aspect ratio.

1: Fill the cell with the image and ignore its aspect ratio.

2: Maintain the original image size, which may exceed the cell boundary.

3: Customize the image size by using the height and width

Height

Width set an exact height and width, in pixels, only when using sizing 3 option.

Trying out Image()

The best way to try out Image() (or any multi-parameter Excel function) is to make a sheet with cells for each parameter.  Then change the cells which is easier and clearer than messing about in the function itself.

To stop errors when switching between the Sizing parameters (see row 8 above), try this simple formula to separate Sizing = 3 (which needs the height/width parameters) from the other options which don’t need (or want) the extra parameters.

=IF(B3=3,IMAGE(B1,B2,B3,B4,B5),IMAGE(B1,B2,B3))

If you want to be extra Excel clever, make a Lambda function to cover the options in Image and avoid errors.

Who gets Image()?

Image() has been deployed to beta releases of Excel on all platforms, except online/browser based.  

• Windows: v2209 build 15608.10000 or later
• Mac: v16.65 build 22080701 or later
• iOS: v2.65 build 22080701 or later
• Android: v16.0.15608.10000 or later

As usual these days, having the right software version might not be enough. Microsoft gradually rolls out trial features users based on some mysterious criteria known only to them.

Sample images

In their documentation, Microsoft  provided links to some sample images you can use to try out Image()

Sphere: https://support.content.office.net/en-us/media/2d9e717a-0077-438f-8e5e-f85a1305d4ad.jpg

Cylinder: https://support.content.office.net/en-us/media/35aecc53-b3c1-4895-8a7d-554716941806.jpg

Pyramid:  https://support.content.office.net/en-us/media/926439a2-bc79-4b8b-9205-60892650e5d3.jpg

Wish list

Image() is OK but there’s some noticeable omissions.  Some are understandable but others are not.

• No support for local or network sourced images.  In particular Image() can’t link to images saved in the same directory or sub-directory as the workbook.
• Again, no SVG support.  Considering the effort Microsoft has put into SVG support, it’s a very curious omission.
• No option to cache the image in the workbook file, so an image will appear even if the external link isn’t available.  In other words, there’s no equivalent to the Office ‘Insert and Link’ feature.
• No option to rotate an image.  That’s important if the source picture (usually a JPG) isn’t oriented correctly and you can’t edit the source image.

Images in Office – Inserted or Linked?
Excel’s data from picture now in Windows!
Clever picture borders in Excel and PowerPoint
Make better Excel Charts by adding graphics or pictures