Google Sheets has some great shortcuts that can help you get the work done a lot faster (and more accurately).
One such functionality is Fill Down in Google Sheets.
Fill Down is an autofill Google Sheets setting you can apply to a column (or row) based on the pre-existing pattern.
For example, suppose you want to quickly get a series of 100 numbers (i.e., 1, 2, 3… and so on). While you can do this manually, that’s a waste of time.
Instead, you can use the fill down Google Sheets function to get this series in a few seconds. Google Sheets can autofill numbers based on the previous numbers.
Below are some examples where you can use fill down in Google Sheets to be more efficient and get the work done faster (each of these examples is also covered in detail later in this tutorial):
- Get a series of numbers (with a specified gap between each number)
- Get all 12-month names (or month and year names)
- Copy down the formula
Let’s get started getting Google Sheets to drag down numbers. First we’ll learn what a fill handle (that makes fill down possible) is and then look at some useful examples of using the Google Sheets fill down function.
Table of Contents
What is a Fill Handle?
When you select a cell or a range of cells in Google Sheets, you will notice that the bottom-right edge of the selection has a small square (as shown below). You can use these to make Google Sheets autofill a formula, numbers, or even text strings.
When you place the cursor over this small blue square, the cursor changes to a plus icon. This indicates that if you use the left-click on the mouse (or trackpad), you will have the fill handle selected and you can use it to fill down data in Google Sheets.
At this point, if you hold the left mouse key and drag the fill handle down, you will notice that it automatically fills the cells with some values.
The values that are filled will depend on what you already had in the cells that were selected initially (more on this in the examples covered later in this tutorial).
Now let’s have a look at some examples where you can use fill handle to quickly fill down data/formulas and save time in Google Sheets.
How to Configure Autofill
You can change the Google spreadsheet fill down setting by navigating to Tools > Autocomplete
Autofill Google Sheets Using the Same Cell Value in a Column and the Fill Handle
In case you want to repeat the same number (or even text) in a column, you can easily do that in Google Sheets.
Suppose you have a dataset as shown below and you want to quickly fill the cells A3:A11 with the same value that’s in A2.
Below are the steps to do this:
- Select cell A2
- Place the cursor over the fill handle icon (the blue square at the bottom-right of the selection).
- Hold the left key on the mouse (trackpad) and drag it down to cell A11
The above steps would fill the same department ID in all the cells.
Fill Down a Series of Numbers
If you want to quickly add a series of numbers in a column, you can do this in seconds using Fill Down.
A good use case of this could be when you want to fill a series of numbers (1, 2, 3… and so on). For example, suppose you have a dataset as shown below and you want to get the serial numbers in column A.
Below are the steps that will fill a series of numbers in a column using fill down:
- Enter 1 in cell A2 and 2 in cell A3
- Select both the cells A2 and A3
- Place the cursor over the fill handle icon (the blue square at the bottom-right of the selection).
- Hold the left key on the mouse (or the trackpad) and drag it down to cell A11
The above steps would add serial numbers to the cells where you dragged the fill down handle.
In this example, the pattern was an increment by one in each cell and this is why fill handle gives you a series of numbers.
In case you use 1 and 3 in cell A2 and A3 respectively, using the fill handle will give you a series of odd numbers (as the pattern is to keep a difference of 2 in the numbers).
Using the fill handle to fill down numbers also work when you have some text along with the number. For example, instead of just using the number, if you use #1 and #2 and in the first two cells and then fill down, it will be able to recognize the pattern and give you #3, #4, and so on.
Fill Down Months (or Months and Years)
Just like numbers, you can also use the fill handle to quickly get the month names (or month and year names).
Suppose you have a dataset as shown below and you want to quickly fill the month name and year value in column A.
Below are the steps to fill month and year value using fill handle:
- Enter ‘Jan 2019’ in cell A2 and ‘Feb 2019’ in cell A3
- Select both the cells A2 and A3
- Place the cursor over the fill handle icon (the blue square at the bottom-right of the selection).
- Hold the left key on the mouse (trackpad) and drag it down to cell A11
The above steps would insert the month name and year value in all the cells in Column A. Since the pattern in the first two cells is to increment the month by 1, using the fill handle would continue this pattern and give values.
In case you only use the month name (such as Jan, Feb, etc) and then fill down, Google Sheets will give you the months till December and then repeat the months’ name by starting over from Jan. In case you have both Month name and Year value, although the month name repeats, the year gets incremented.
Fill Down a Formula (Apply to the Entire Column)
You can also use fill-down functionality to quickly copy a formula to the entire column.
While you can also do this by using the old-fashioned copy and paste, using the fill handle to do this saves time and is more efficient.
Below is a dataset where I have to copy the formula in cell C2 to all the cells below it. The cell C2 has the formula to get the sales target for next year where I have simply multiplied the value in cell B2 with 1.1.
Below are the steps to fill down a formula in Google Sheets:
- Select cell C2
- Place the cursor over the fill handle icon (the blue square at the bottom-right of the selection).
- Hold the left key on the mouse (trackpad) and drag it down to cell C13 (you can also double click on the bottom right blue square and it will fill the cells)
Note that for this to work, your formula should not be using a locked cell reference. So the formula can be =B2*1.1 or =$B2*1.1 (but it can not be =$B$2*1.1 or =B$2*1.1).
In case there is a dollar sign before the row number, it would not allow the formula to adjust to the row number when you fill down.
Fill Rows using Fill Handle
Just as you can use the fill handle to fill down (or fill up), you can also use it to fill autofill a formula in Google Sheets in the rows.
The concept is the same where it will try to figure out the pattern based on the selected cells and then repeat the same pattern to all the cells in which you drag the fill handle.
Suppose you have the dataset as shown below and you want to fill the row by mentioning the week number.
Below are the steps to fill rows using fill handle in Google Sheets:
- Enter ‘#1’ in cell B1 and ‘#2’ in cell C1
- Select both the cells B1 and C1
- Place the cursor over the fill handle icon (the blue square at the bottom-right of the selection).
- Hold the left key on the mouse (trackpad) and drag it down to cell J11 (or whatever cell till which you want to fill the week numbers).
The above steps would fill the cells in the row with the week numbers.
How to Autofill in Google Sheets Without Dragging by Using Keyboard Shortcuts
You can use keyboard shortcuts to autofill formulas instead of dragging. All you have to do is:
- Highlight the cells you wish to autofill, including at least one with the formula you wish to autofill
- Use one of the following keyboard shortcuts
Fill Right: Ctrl + R
Fill Down: Ctrl + D
Fill Range: Ctrl + EnterFor Mac OS, use the ⌘ button instead of Ctrl
Basically, if you want to autofill an entire column with a formula:
- Put the formula into the first cell in the column
- Click on the column header
- Press Ctrl + R
For rows, use Ctrl + R instead.
How to Autofill Numbers in Google Sheets FAQ
How Do I Autofill in Google Sheets Without Dragging?
You can use keyboard shortcuts instead. (Use ⌘ instead of Ctrl if using a Mac)
- Ctrl + R to autofill to the right
- Ctrl + D to autofill down
- Ctrl + Enter to autofill a selection
Let’s say you wanted to autofill a row from column C onwards in your spreadsheet. To do that, you would have to:
- Type the formula that you want to fill the row with in the left-most column you want to apply the formula from (column C)
- Click on the row header
- Hold Ctrl and click the cells you don’t want to apply the formula to
- Press Ctrl + R
What Is the Shortcut for Autofill in Google Sheets?
(Use ⌘ instead of Ctrl if using a Mac)
- Ctrl + R to autofill to the right
- Ctrl + D to autofill down
- Ctrl + Enter to autofill a selection
How Do You Repeat a Formula in Google Sheets?
- Highlight the formula you wish to repeat
- Click and drag the fill handle (little blue square) in the bottom right over the cells you want to apply the formula to
Why Is Google Sheets Not Auto Filling?
You may have accidentally changed the settings. You can check by navigating to Tools > Autocomplete
Keep Learning
We hope this tutorial has helped you learn how to use the very handy autofill Google Sheets features. Now you can use Google Sheets to autofill numbers and fill series pretty much instantly!
You may also like the following articles:
