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 allows you to quickly autofill 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 in Google Sheets to get this series in a few seconds.
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 gets started and first learn what is a fill handle (that makes fill down possible) and then look at some useful examples of using fill down in Google Sheets.
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).
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.
Repeat Same Cell Value in a Column using 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) in Google Sheets, you can also use it to fill 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.
You may also like the following articles:
