Do you want to insert an image into a cell? This post is going to show you exactly how to put a picture in a cell in Microsoft Excel!
An Excel cell can contain many different data types such as text, numbers, dates, Booleans, and error values. Until recently, images have not been a data type that you could insert into a cell.
Previously, you could only place the image above the cell and then change the image properties so it moves and resizes with the cells.
There are new features available in Excel that allow you to add images directly inside a cell. This means images are a proper data type and can be used in similar ways to other data such as text or numbers.
Get your copy of the example workbook used in this post and follow along!
Insert an Image in a Cell with Data Types
This first method won’t allow you to get any image embedded in your cell but is a quick and easy way to see images in action using Data Types.
Data types have been available for a while and allow you to pull certain geographical or financial data from the web.
These data types contain many pieces of data inside a single cell. A country data type might contain information about the population, capital, current president, or current prime minister.
The Geography data type in particular also includes a related image. A country, state, or province data type might have the associated flag image included in the data type.
This image can be extracted from the data type into a cell.
This example shows a small list of country names in Excel. These are plain text, so there is nothing special about them until they are converted into geography data types.
You can follow these steps to create a data type and extract the image into the grid.
- Select your list of cells with plain text geographical names such as country names.
- Go to the Data tab.
- Click on the Geography data type.
This will convert the plain-text country names into country data types. You’ll notice the small map icon to the left of the country name which indicates they are now Geography data types.
When you click on this map icon, it will open a pop-up data card that displays all the information contained in the cell. This can include an image of the flag at the top of the card.
Now you will be able to extract the flag image into a cell.
- Select all the data type cells.
- Click on the Extract button that appears at the top right of the selected cells.
- Choose the Image option from the list.
This will extract the data type image into the adjacent cell. You can see this is really a formula
=B2.Image that references the cell with the data type by using a dot notation to extract a piece of data from the data type.
💡 Tip: You can copy and paste as values so the image will then be disconnected from the data type.
Insert an Image in a Cell with Power BI Organization Data Types
The Geography data type is perfect if you want a flag image inside your cell!
But what if you want your own custom images added in a cell?
You can build your own custom Organization data type. These can then be used by anyone inside your organization in any of your spreadsheets.
Organization data types also allow you to specify an image URL which can then be extracted to a cell.
⚠️ Warning: This feature requires a Power BI pro license and a table of data that contains a column of image URLs.
- Open the Power BI desktop app.
- Click on the Import data from Excel or choose the Get Data option in the Home tab if your image dataset is not in Excel.
This will open a file picker menu where you can select the Excel file with your image URLs.
- Select the Excel file with your image URLs.
- Press the Open button.
This will open up the Navigator window.
- Select the table or sheet that contains your image URL data.
- Press the Load button.
- Go to the Model view.
- Select the table you loaded.
- Toggle the Is featured table option to Yes in the Properties window pane.
- Add a Description for the table.
- Select a Row Label column from the data. This will be the text displayed in the cell when you’re using this data type in Excel.
- Select a Key column from the data. This should be a unique identifier from the data.
In this example, the Row Label and Key column are the same since the name is what you want to be displayed in Excel and it also uniquely identifies the product.
Now you’ll need to set the column properties for the column of image URLs.
- Go to the Data view.
- Select the column that contains the image URLs.
- Go to the Column tools tab.
- Select Image URL for the Data category.
Now you need to publish this dataset to the Power BI online service so the Organization data type will show up in Excel.
- Go to the Home tab.
- Click on the Publish button.
- Select any workspace other than My workspace.
- Press the Select button.
📝 Note: My workspace is your own personal workspace in Power BI online. No one else will have access and the Organization data type feature does not work with it.
Now you can open Excel and use the new Organization data type. Just make sure Excel is signed into a Microsoft account on the same Microsoft tenant as the workspace you published in Power BI.
- Select some data from your Row Label column.
- Go to the Data tab in the Excel ribbon.
- Click on the button to expand the Data Type shelf.
- Click on your custom data type found in the From your organization section.
This will convert the items into the data type and you will be able to extract the image the same way as with the Geography data type.
In this example, the formula
=B2.Image will extract the picture since Image was the name of the column that was set to an Image URL type.
Insert an Image in a Cell with the IMAGE Function
Creating an organization data type is a long process, especially if you only want to add an image inside a cell.
Thankfully there is a better way to insert an image if you have a public web address for it.
The IMAGE function allows you to insert an image in the cell based on a URL.
IMAGE Function Syntax
= IMAGE ( url, alt_text, sizing, height, width )
The IMAGE function has the following required arguments.
urlis the web address of the image you want to display in the cell.
The IMAGE function has the following optional arguments.
alt_textis an input for an alternative text for accessibility.
sizingis an option to determine how you want the image to fit into the cell.
0will fit the cell and maintain the image aspect ratio.
1will fill the cell and ignore the image aspect ratio.
2will add the image and maintain its original size.
3will allow you to enter a custom height and width size for the image.
heightallows you to set the height of the image.
widthallows you to set the width of the image.
📝 Note: The
width arguments are required when
sizing is set to
IMAGE Function Example
= IMAGE ( "https://www.howtoexcel.org/wp-content/uploads/2022/10/Insert-Images-Into-Cells.jpg" )
The above formula will insert an image from this website. The
url argument can be entered as a text string or you can reference a cell that contains the web address.
💡 Tip: You can use an online drive app such as SharePoint or OneDrive to create a public web address that can be used in the IMAGE function. Set the file access as shared with anyone with the link!
Getting an image inside a cell has long been a sought-after feature in Excel. But until recently, there has only been a poor workaround method available.
Now there are some great options available for images in a cell.
You can use the Geography data type quickly get flag images inside cells, and if you want a custom image you can create Organization data types with Power BI.
Images can also be entered into the cell via the IMAGE function. This is a quick and easy way to get any image with a web address into an Excel cell.
Did you know about these features? What are you going to use them for? Let me know in the comments section below!