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. 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. 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. This will open a file picker menu where you can select the Excel file with your image URLs. This will open up the Navigator window. 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. Now you need to publish this dataset to the Power BI online service so the Organization data type will show up in Excel. 📝 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. 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
The IMAGE function has the following required arguments.
url is the web address of the image you want to display in the cell.
The IMAGE function has the following optional arguments.
alt_text is an input for an alternative text for accessibility.sizing is an option to determine how you want the image to fit into the cell.0 will fit the cell and maintain the image aspect ratio.1 will fill the cell and ignore the image aspect ratio.2 will add the image and maintain its original size.3 will allow you to enter a custom height and width size for the image.height allows you to set the height of the image.width allows you to set the width of the image.
📝 Note: The height and width arguments are required when sizing is set to 3.
IMAGE Function Example
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!
Conclusions
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!
