From stock parts to holiday villas, from employees to logos, there are plenty of reasons to insert and automatically change an image based on a cell. You may think of this as a lookup function that returns a picture, rather than a value. Ultimately, the purpose is to link an image or picture to a cell; change the cell value and the picture automatically changes.
The Beta version of Office 365 has an IMAGE function that works with web images. That feature has not yet been fully released; Therefore, I believe the methods presented below are best available.
In this post, we’ll explore three different methods to achieve this task. Each solution has it’s own strengths and weaknesses; learn them all and pick the one which best meets your requirements.
Let’s look at each of these methods in turn.
Download the example file: Click the link below to download the example file used for this post:
Change image with a named range + INDEX/MATCH + linked picture
If you’re working along with the example file, we’ll begin with the Linked Picture tab.
As an overview, this method works as follows:
- Create a dynamic named range using the INDEX MATCH formula combination
- Use the named range as the source for a linked picture
Now let’s look at each of these steps in turn.
The example data contains a list of countries in Column A and their relevant flags in Column B.
Create a dynamic named range with INDEX MATCH
Start by entering one of the country names in a cell (for our example, use cell D2).
Next, we’ll create a named range by selecting Formulas -> Define Name from the ribbon.
In the New Name dialog box, create a new named range called CountryLookup.
Rather than a range, the Refers to box needs to contain a formula calculates to a range. We will use INDEX/MATCH for our example.
The formula in the Refers to box is:
=INDEX('Linked Picture'!$B$2:$B$11, MATCH('Linked Picture'!$D$2,'Linked Picture'!$A$2:$A$11,0))
In this formula:
- Cells B2-B11 contain the flags
- Cell D2 contains the name of the country to be matched
- Cells A2-A11 contains the names of the countries
Click OK to close the New Name dialog box.
Using a named range as the source for a linked picture
Now that the named range has been created, follow the following steps to create a linked the picture.
- Select any cell which contains a flag.
- Click Home -> Copy (or Ctrl + C) to copy the cell.
- Select a different cell (use E2 if working with the example file).
- Click Home -> Paste (drop-down) -> Linked Picture (alternatively, the Camera Tool is an option).
- The pasted image will appear.
- Select the pasted image.
- Change the address in the formula bar to be equal to the named range we created earlier (CountryLookup in our example).
Change the name of the country in cell D2 and watch the image change. For completeness, change cell D2 into a data validation drop-down list containing all the countries.
The “Reference isn’t valid” Error
If you receive the Reference isn’t valid error there are two likely problems:
- The result of the INDEX/MATCH function produces an error. This usually occurs where the name of the country is not identical to one of the countries in the list.
- The named range applied to the picture does not exist.
Other formula options
INDEX/MATCH is a formula combination, which can achieve some amazing things; picture formulas are just one of those amazing things. But any formula which returns a range will work inside the named range. Some easy alternatives are:
XLOOKUP (new function available in Excel 365)
=XLOOKUP('Linked Picture'!$D$2,'Linked Picture'!$A$2:$A$11, 'Linked Picture'!$B$2:$B$11)
Find out more about the XLOOKUP function in this article: XLOOKUP function (support.office.com)
OFFSET / MATCH
=OFFSET('Linked Picture'!$A$1,MATCH('Linked Picture'!$D$2, 'Linked Picture'!$A$2:$A$11,0),1)
To learn about other formulas that return ranges, check out this post.
Change image with chart fill + #N/A
If you’re working along with the example file, check out the Chart Fill tab.
As an overview, this method works as follows:
- Create a formula that calculates 1 for the selected country or #N/A for the other countries.
- Create a stacked column chart with the country list and formula created in 1 above as the source
- Add the images as the fill for each chart series
Here are those steps in more detail
Formula to calculate as 1 or #N/A
Enter a country name in a cell (for our example, use cell D2).
Columns A contains the names of countries.
Enter the following formula in cell B2:
The purpose of the formula is to calculate 1 if cell D2 is the same as the country in column A; otherwise, #N/A is displayed. Copy the formula down to the last country name in the list.
Create a stacked column chart
Now it’s time for the chart trickery:
- Select the list of country names and the formula result (cells A2 to B11 in our example).
- From the ribbon, click Insert -> Charts -> Stacked Column
- A new chart will appear on the worksheet.
- Right-click on the series, then click Select Data… from the menu.
- The Select Data Source dialog box will open.
- Click the Switch Row/Column button, the Legend Entries box on the left should now contain the list of countries as different chart series.
- Click OK to close the Select Data Source dialog box.
- Right-click on the data bar and select Format Data Series… from the menu.
- Change the Gap Width to 0%
- Right-click on the vertical axis and select Format Axis… from the menu.
- Set the axis maximum equal to 1.
- Select the Chart Title, press the delete key.
- Next, select the vertical axis and delete it.
- Then, select the horizontal axis and delete that also.
- Re-position and resize the chart to fit your requirements. The worksheet will look similar to this:
- Change the selected country to be the first country in the list.
- Right-click on the chart fill and select Format Data Series… from the menu.
- Change the fill to Picture or texture fill and click Insert…
- Navigate to the picture location and click Insert.
- Repeat steps 16 to 19 for each country in the list.
The fully working chart achieves a similar result to the first example.
Inserting images one at a time, in this way, can be time-consuming. So, I have created a macro that can automate this for you.
Add the chart fill automatically with a macro
The macro below inserts pictures into the chart fill automatically. To work, the names of the countries must exactly match the names of the pictures.
To run the macro for your scenario:
- Change “Chart 1” to the name of your chart.
- Set the file path to the folder containing the images, and change the file extension.
- In the worksheet, select the cells containing the country names and run the macro. The chart should automatically populate with the relevant images.
Sub InsertPicturesIntoChart() Dim i As Integer Dim selectedCells As Range Dim imageFullName As String 'Select the cells with the country names before running macro For Each selectedCells In Selection i = i + 1 'imageFullName is the file path to the image. 'Country name in Column A must match the name of the image. imageFullName = "C:\Users\marks\Documents\Flags\" & Cells(i + 1, 1).Value & ".png" 'Change the fill of the chart Series. Change Chart 1 to the name of the chart. ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(i).Format.Fill.UserPicture imageFullName Next selectedCells End Sub
Change image with a VBA User-Defined Function
If you’re working along with the example file, look at the UDF tab. Finally, we will consider a VBA user-defined function. This is a formula created using VBA macros to insert and change pictures. Learn more about UDF’s here.
WARNING! User Defined Functions that change objects are not officially supported in Excel. While this works in the Windows version of Excel, I’ve not successfully made it work on the Mac version. Other VBA options are available, but they are not as flexible as the UDF method presented here.
The VBA code
Copy the following code into a standard module within the Visual Basic Editor.
Public Function PictureLookupUDF(FilePath As String, Location As Range, Index As Integer) Dim lookupPicture As Shape Dim sheetName As String Dim pictureName As String pictureName = "PictureLookupUDF" sheetName = Location.Parent.Name 'Delete current picture with the same Index if exists For Each lookupPicture In Sheets(sheetName).Shapes If lookupPicture.Name = pictureName & Index Then lookupPicture.Delete End If Next lookupPicture 'Add the picture in the right location Set lookupPicture = Sheets(sheetName).Shapes.AddPicture _ (FilePath, msoFalse, msoTrue, Location.Left, Location.Top, -1, -1) 'Resize picture to best fit the range If Location.Width / Location.Height > lookupPicture.Width / lookupPicture.Height Then lookupPicture.Height = Location.Height Else lookupPicture.Width = Location.Width End If 'change the picture name lookupPicture.Name = pictureName & Index PictureLookupUDF = "Picture Lookup: " & lookupPicture.Name End Function
Using the UDF on the worksheet
The user defined function can now be used on the worksheet just like a normal function.
The PictureLookupUDF uses the following arguments:
=PictureLookupUDF(FilePath, Location, Index)
- FilePath = Full file path to the image, including the file extension.
- Location = Range of cells where the image should be placed.
- Index = A unique reference number to identify the image.
The formula is used in the example below.
In cell D6 the formula is:
- D2&C6&D4 – consolidates the folder path, country name, and file extension
- D6:D12 – the range of cells of where the place the picture
- 1 – the unique reference number to identify the image
Images can be placed on a different worksheet, by using a standard sheet reference:
Look at this function in action. It behaves just like the previous examples.
The Index argument can be used to insert multiple images. For example, to include a second image, increase the index number by 1, as shown below.
If you wish to amend the VBA code to change the images further, such as size, rotation, etc., you can find further guidance here.
Advantages & disadvantages of each option
Named range + INDEX/MATCH + linked picture
- All the images are contained on the worksheet
- Could accidentally move images outside of the cell range
- Takes a lot of preparation to use multiple images
Chart fill + #N/A
- Images contained within the chart, so difficult to change images in error
- Time-consuming to set-up
- Requires a helper column to function correctly
- Takes a lot of preparation to use multiple images
VBA User Defined Function
- No additional columns required on the worksheet
- Easy to use with multiple images
- Technique not fully supported by Microsoft
- Requires users ability to use VBA
- Image name must be the same as the value on the worksheet
- Difficult to share the workbook with others who don’t have access to the image folder
There are many ways to change an image based on a cell value. Only one VBA option has been presented in this post, but there are many other possible solutions.
Which method should you use? It really depends on your circumstances. My advice is to learn them all and apply what you think works best in each scenario.
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: