Google Sheets Autofill: Fill Cells Automatically (A Comprehensive Guide)

Over the years, Google Sheets has continued to add much-needed features with every update, especially those catered toward making data presentation easy. One such feature is the autofill of Google Sheets.

In this simple guide, we will discuss how to apply an autofill for different types of data as well as have a look at other new features related to it in Google Sheets.

Let’s get started.

How to Autofill Values in Google Sheets

Auto-filling any data in Google Sheets revolves around the Fill Handle.

The fill handle is the small blue square that you see in the bottom-right corner of the active cell.

fill handle of an active cell - autofill google sheets

Step 1: Placing the mouse cursor over the fill handle transforms it into a plus (+) symbol.

Step 2: At this point, you can click and drag the cursor in any direction (horizontal or vertical) to automatically fill the cells with the active cell value.

using the fill handle to autofill cells in google sheets animated

This is the primary way to autofill values in Google Sheets.

Autofill in Google Sheets Without Dragging

Sometimes clicking and dragging may just not cut it. This is especially true when you have to autofill the same data in a worksheet over a large data range.

For that, Google Sheets provides us with a way to autofill values without dragging. That is by simply double-clicking the fill handle.

autofill without dragging in google sheets (double click) animated

However, double-clicking to autofill is not exactly the same as dragging to autofill in Google Sheets. There are two points to note about this method.

1. Using the double-click method the active cell and corresponding auto-fillable cells require adjacent value cells. As you can see in the image above, the autofill stops immediately at the same row as the last names.

In the image below, you will see the double-click autofill stop when there is a gap in the adjacent column:

adjacent blank cells stop autofill using the double click method

2. The double-click autofill only auto-fills with the value that is in the active cell.

Autofill Using Shortcuts

If you are looking for that little edge in efficiency, Google Sheets has got you covered with its keyboard shortcuts.

We have three different shortcuts that we can use to autofill in Google Sheets:

  1. CTRL+D (Fill Down)
  2. CTRL+R (Fill Right)
  3. CTRL+ENTER (Fill All)

For Mac users: Use instead of CTRL.

On their own, these shortcuts do nothing as Google Sheets cannot determine the data range for the fill. So, the user must select or highlight the range of cells where these autofill shortcuts need to be applied.

autofill using shortcut in google sheets animated

Examples of Using Autofill in Google Sheets

Autofill is not limited to generating a single value throughout the data range. There are scenarios where you may need to autofill a sequence of values or even formulas. For that, the application has updated its autofill feature enough.

Let’s see how we can do that with a few examples.

1. Autofill a Series/Sequence of Numbers

A common use of autofill is to generate a sequence of numbers. But, as we have seen just previously, using the fill handle on a single value only auto-fills the active cell value. So, how do we autofill a sequence of numbers in Google Sheets?

Step 1: Input the first two values of the sequence. Selecting these two values will present a fill handle under the second value.

use the fill handle after selecting the first two numbers of the series

Step 2: Click and drag the fill handle down the column to autofill it with the series of numbers.

use fill handle to autofill a number sequence in google sheets

And we are done!

Note: The double-click method will work for this particular sequence. However, for a series of numerical values that have a difference of greater than 1, then the double-click (non-drag) method will not work.

Auto-filling numerical values are not limited to serial numbers, you can also use this method to autofill a sequence of values or even dates, which are numerical themselves.

Learn More: How to Autofill Numbers in Google Sheets (An Easy Guide)

2. Autofill Formulas in Google Sheets

Another common use of autofill is to fill formulas down a column.

Consider the following dataset where we have a formula already inserted in the first row:

dataset with formula

We want to apply the formula for each row of the dataset. How do we do that?

Fortunately, it is nothing new. We can use the fill handle to autofill formulas in Google Sheets.

autofill formula in google sheets animated

As you can see from the image above, the autofill automatically updates the cell references in the formula accordingly.

Learn More: How to Autofill Formula in Google Sheets (3 Easy Ways)

New Feature: Google Sheets Smart Fill

Smart Fill of Google Sheets has been around for a couple of years now. While this is not necessarily new, many users are now discovering its various uses. You may have already encountered it while using Sheets.

Smart Fill is meant to automatically predict data and formulas to autofill down a column in Google Sheets.

To initiate a smart fill, simply type in relative data in a cell. For example, we will begin by adding a formula to a cell. As we continue adding, a suggestion box will pop up:

smart fill suggests autofill in google sheets

Clicking on Show formula will show you the formula that Google Sheets smart fill will use to autofill.

smart fill can automatically generate formulas for the user

This feature is not limited to simple formulas only, it works with virtually any value that has a pattern to it.

How to Perform Autofill in Google Sheets Mobile App (Android/iOS)

Google Sheets has its own version of the mobile application to help users on the go. While it is still missing many features, most basic features are available. One of which is autofill.

Autofill in Google Sheets mobile app follows a similar method to that of the browser version.

For this example, we will autofill a numerical sequence in the mobile app.

Step 1: Input and select the first two values of the sequence. Tap once on the first value then drag it onto the second cell.

selecting the first two values in google sheets mobile

Step 2: As you can see the fill handle in the Google Sheets mobile app is circular. Tap, hold and drag the fill handle to select the entire column. Tap on any selected cell to bring up the menu. Here you’ll find the Auto-fill option.

tap on selected cell to bring out the auto-fill option

Step 3: Tapping on the Auto-fill menu will autofill the selected cells in the sequence of the first two values.

autofill in google sheets mobile app

Final Words

That concludes our simple guide on how to apply and use autofill in Google Sheets. The autofill feature is nearly as old as the inception of spreadsheet software. But even then, Google Sheets continues to innovate to cater to user convenience. One outcome of this innovation is the Smart Fill.

Feel free to leave any queries or advice you might have in the comments section below.

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo