Images are a great way to add some life to your documents. Google sheets is already an amazing tool, but it can be even better with images!
How do you insert an image into Google Sheets?
There are three ways to do this.
- Insert an image above the sheet.
- Insert an image inside a cell.
- Insert an image using a function.
I will go over all three of these in detail inside this post! Get your copy of the example workbook to follow along.
Insert Images Over Cells from the Insert Menu
The Google Sheet’s Insert menu allows you to insert images from various locations such as Google Drive, the camera on your device, or even a Google image search.
This first method allows you to place an image inside a sheet as an object that floats above the grid.
This means, your picture will cover from view any data or formula below. The image won’t interfere with any of the grid except to obscure the view from users.
To insert an image above the grid, follow these steps.
- Click on the Insert menu.
- Select Image from the options.
- Select Image over cells from the submenu options.
This will open up the Insert image menu where you can choose the location of the image.
- Click on the GOOGLE DRIVE tab.
- Find and select the image you want to insert. A blue bar will appear once you have selected an image where you will find an insert button.
- Press the INSERT button.
You’ll be able to insert an image from any of the 6 options available.
- UPLOAD This option allows you to drag and drop images or select images from a file picker menu directly from your device storage.
- CAMERA This option allows you to use the camera on your device to take and insert a picture.
- BY URL This option allows you to insert a picture based on its address. You can use this method to insert a specific photo you find on the web.
- PHOTOS This option allows you to insert an image from your Google Photos account.
- GOOGLE DRIVE This option allows you to add an image from your Google Drive storage.
- GOOGLE IMAGE SEARCH This option allows you to search Google for an image based on a keyword. Careful with this method though, as images may be copyrighted.
Once your image is inserted into Google Sheets it will appear in its original dimensions by default. You can resize this as needed using the handles that appear around the image.
Click and drag on any of the handles to resize the image.
Note: Resizing with the corner handles will keep the aspect ratio intact, while resizing with the middle handles will distort the image ratio.
You can also move this image around the sheet to any location. Just click and drag the image to move it.
When you select an image which has been inserted over the cells, you will notice three vertically aligned dots in the upper right corner.
This is a menu with further options available; Replace image, Delete image, Assign script, Reset size, Alt text, and Put image in selected cell.
Two of the more interesting options are Assign script and Put image in selected cell.
The Assign script option allows you to run an apps script when the user clicks on the image. This means you can create custom buttons to run automations.
The Put image in selected cell option allows you to place the image inside a cell. This is a slightly quicker option for inserting an image inside a cell when it already exists over the cells.
Insert Images Inside a Cell from the Insert Menu
The second method of inserting an image into Google Sheets involves inserting an image inside a cell.
This is the most interesting way of adding an image as you will then be able to reference the image inside of formulas. Functions such as VLOOKUP can even return images as their values!
The possibilities are endless with images inside cells.
To insert a picture inside a cell follow these steps.
- Select the cell which you want to contain the image.
- Click on the Insert tab in the main menu.
- Select Image from the options.
- Select Image in cell from the options.
An Insert image window will pop up. These are the exact same options as inserting an image over the cells as seen previously.
- Click on the GOOGLE DRIVE tab at the top of the menu.
- Select the image you want to insert.
- Press the INSERT button.
The image will appear inside your selected cell.
Images are scaled down to fit inside the cell, so it will appear very small inside a cell that hasn’t been resized.
You can click and drag the row or column to resize the cell. This will automatically resize the image inside as well.
If you want a larger image without resizing the row height and column width, you could achieve this by merging multiple cells to create a single large cell. This method can create a larger picture without distorting the cells containing any other data.
Images inside a cell are left-aligned by default, but you can easily center align them just like any other data inside a cell.
Insert Images with the IMAGE Function
Inserting an image inside a cell is a very useful option but selecting each image can be time consuming when you need to add a lot of images.
If you have a list of URLs for each photo, then there is an easier way to get the images inside your cells.
Google Sheets has an IMAGE function which may cause a bit of jealousy for anyone that uses Microsoft Excel instead of Google Sheets and doesn’t have such a powerful function.
This is a great option for inserting images in bulk!
Syntax for the IMAGE Function
The IMAGE function takes the URL of an image and returns the image inside the cell.
IMAGE ( url, mode, [height], [width] )
- url is the web address of the image to be inserted.
- mode is either 1, 2, 3, or 4.
- 1 will scale the image to fit the dimensions of the cell while keeping the aspect ratio intact.
- 2 will stretch the image to fill the cell. This ignore the aspect ratio and can distort the image.
- 3 will leave the image at the original dimension and will crop anything that doesn’t fit in the cell.
- 4 will allow you to use the height and width arguments to specify a custom dimension for the image.
- height is the height in pixels for the image. This optional argument can only be used when the mode is 4.
- width is the width in pixels for the image. This optional argument can only be used when the mode is 4.
Example Using the IMAGE Function
= IMAGE ( B6, C6, 50, 120 )
In this example, the URL for an image is in cell B6. The mode has been entered as 4 which will allow you to specify the height and width with the remaining two arguments.
Here the height and width have been entered as 50 pixels by 120 pixels respectively.
The result is the same as inserting an image inside a cell manually. It can also be referenced by other formulas.
The only difference is it can be much easier to insert multiple images using the IMAGE function when you have a long list of URLs. All you need to do is copy and paste down a formula!
The IMAGE function also has the added advantage as it gives you much more control over the size and shape of your image by using the mode argument.
Tip: Once the image has been inserted with the IMAGE function, you can remove the formula so it’s just an image. All you need to do is copy the formula then use the paste values only option.
Insert Images as Background
One thing that comes up again and again with images in Google Sheets is the desire to add an image as a background.
I showed you how to add an image in the foreground above the grid, but unfortunately, it’s not possible to add images in the background.
Google Sheets has no option to add a background image below the grid.
Google Sheets makes it possible to insert images over cells as well as inside cells.
Google makes this easy no matter where the image is whether it’s Google Drive, Google Photos, or even an image you find on Google Image Search.
All great places to find photos that you can then use in your Sheet.
Finally, if you want to insert pictures from a list of URLs this is so easy with the IMAGE function.
These are all certainly great options for working with images in Google Sheets. Do you have a favourite? Let me know in the comments below!