We are going to look in some detail at one of Excel’s graphic features: the camera that creates dynamic linked pictures of worksheet cells. We will see how it can be used to add specific pictures to a report based on cell values. However, before focusing on the camera, we will briefly consider some significant changes to other graphics features in recent updates to the Office 365 edition of Excel.

From icons to cutout people

Over the years, new features have been added to the Illustrations group in all the Office applications, including animated 3D Models and a range of simple icons. In example 1, you can see that the list of available graphics types has recently been expanded to include: many additional icons, split across multiple categories; thousands of Stock Images to replace Clip Art; Cutout people; Stickers; and Illustrations – once again, all split into categories.

The Illustrations set has an extra superpower: the accent colour will change to match the palette of the Office theme in use in the workbook, document or presentation in which it is included.

Cutout people are pictures of people with transparent backgrounds. We will use some of these to create the pictures of our sales team.

Camera in focus

Our intention is to include, as part of a report, a picture of the salesperson with the highest value of sales. This picture should change to the correct highest-value salesperson as sales transactions are added.

The Excel Camera creates a ‘linked picture’ object that has a cell, or a block of cells, as its source. Although you can add the Camera tool to your Quick Access Toolbar (QAT) and use it to create your linked picture, you can also just select and copy your cells as normal, as shown in example 2. You can then use the Home Ribbon tab, Clipboard group, Paste dropdown, Paste Linked Picture command. If you do want to add the Camera tool, right-click in the QAT and choose ‘Customise Quick Access Toolbar’. From the ‘Choose commands from’ dropdown, choose All Commands and then find Camera in the alphabetical list. Select it and click on the Add button to add it to your QAT.

Now, you can just select the cells that you need, click on the Camera command, and then click to create a Linked Picture object. If you click on the Linked Picture, you will notice that the source cell reference appears in the formula bar, where it can be changed.

The Linked Picture is dynamic. If any of the contents of the source cells change, it will be immediately reflected in the picture. One possible use of the technique is to create a sheet that allows content from different worksheets to be assembled on one, so you only use a single piece of paper when printing.

Anyway, back to our chosen task of including a picture of our most successful salesperson. In example 3, we have two Excel Tables. The top one contains a list of our sales team’s names, together with each of their pictures sized to fit within a single cell, and a total for sales, calculated using a SUMIFS() formula based on our Table of invoices:

=SUMIFS(Invoices[ExtendedPrice],Invoices[Salesperson],I3)

We have included both Tables on the same sheet for demonstration purposes.

We have selected the cell containing the picture of Nancy Davolio (not the picture itself) and clicked on the Camera command that we added to the QAT. Example 4 shows this picture pasted into a cell on our dashboard sheet.

You can see that the formula bar refers to the source cell of our Linked Picture: =Invoices!$K$3

We want to replace this with a formula that will automatically choose the picture for the salesperson who has the highest total sales. This example uses the INDEX() function, the new XMATCH() function and MAX() to identify the highest value in the Total Sales column. Note that XMATCH() defaults to performing an exact match, unlike its predecessor MATCH(), which defaults to an approximate match:

=INDEX(SalesTeam[Picture],XMATCH(MAX(SalesTeam[Total sales]),SalesTeam[Total sales]))

In this formula, INDEX() selects the item in the SalesTeam Table, Picture column, that XMATCH() calculates as the position of the maximum value in the SalesTeam, Total sales column.

Unfortunately, we can’t just type this into the formula bar to replace the existing source for our Linked Picture as, in common with some other Excel objects, the source formula for a Linked Picture can’t contain Excel functions. The solution is to allocate our formula to an Excel Range Name and then use this name as the source of our Linked Picture.

There is another problem: using Table references in the formula seems to upset Excel. You might need to create your Range Name formula using cell references rather than Table references and then make it the source for your picture. Once the picture source successfully refers to the Range Name, you can go back and use the Name Manager to replace the cell references in the Range Name formula with Table references. Once your formula includes Table references, using it as a new picture source might result in a ‘Reference isn’t valid’ error. However, if a picture source already refers to that Range Name, it should continue to work.

In example 5, we have also used one of the Picture Format gallery options to format our Linked Picture. With the formula set as a reference to our Range Name, =Picture, our picture automatically changes to our star salesperson.