Most people know how to fill down in Google Sheets using the simplest fill down approach. However, there are many tricks you can use to do this more efficiently and making sure the data that fills the cells contains the values you want. In this article, you’ll learn all of the ways to available to fill down in Google Sheets quickly and accurately.
How to Fill Down in Google Sheets
The approach you take to filling down a column in Google Sheets depends on what you’re trying to do. The easiest is duplicating one value down the column. Slightly more involved approaches involve counting, matching an existing pattern, and handling dates.
Filling With the Same Value
When you’re filling out a spreadsheet from scratch, you may need to duplicate a value all the way down a column. This is the easiest fill situation because you can just select the one cell and drag the value as far down the column as you need. To do this, select the cell and then click and hold the blow box as you drag it down the column.
Once you’ve dragged down the number of cells you want to duplicate the original value across, just release the mouse button and all of those cells will fill with that value.
You can do this for text as well. So long as the original cell before the drag and release is a single cell with one value or text, Google Sheets will duplicate the value in that cell.
Filling With Existing Pattern
The next most common approach to performing a fill down in Google Sheets is counting down sequentially in a column based on initial cell values. To do this, you’ll need two or more cells containing values that represent the pattern. This could be consecutive numbers by one, or it could be numbers that follow any other pattern.
To fill the column using the same pattern, just select and hold the blue box at the lower right corner of the last cell. Drag this down as far as you’d like to fill the column and then release the left mouse button. This will fill the column as far as you drag the pointer using your original pattern.
This method and the previous one for filling down in Google Sheets also work for filling rows to the right as well. The process is the same, but you’ll just need to drag the blue box to the right rather than down.
Filling With Complex Patterns
You aren’t limited to filling columns down in simple sequence like the above examples. If your existing pattern includes larger increments or calculations, Google Sheets is smart enough to recognize and repeat the pattern. For example, if your initial numbers include increments by three and you drag down to automatically fill the column, you’ll notice that all of the filled cells increment the value above it by three as well.
How Autofill Works in Google Sheets
This pattern recognition doesn’t end with simple increments either. You can use Google Sheets autofill feature to automatically fill calculations to the bottom of a column of values as well. To do this, create the calculation you want to use in that column. The example below contains an average of the previous five years of temperature data in a column on the far right.
If you were to drag the blue box in the corner of this cell down a few cells in this column it would update the formula to match, with the cell references updated as appropriate. This means each cell in your column will always average the previous five years of data. To automatically fill all cells in this right column to the bottom of the cells of existing data, just hold down the Ctrl key on the keyboard and double click the blue box on the bottom right corner of the cell.
This will fill the rest of the column all the way down to the last cell where there’s existing data in the column to the left of it. Autofill in Google Sheets can save a lot of time, especially when you need to fill down a Google Sheets column in a very large spreadsheet with a very large dataset.
Filling Down in Google Sheets with Dates
Google Sheets includes some additional intelligence when it comes to filling down with dates data. For example, if your first cell contains a data in any standard date format and you fill down in that column, it’ll increment that date by one day in each cell.
You aren’t limited to the day value in this autofill increment. Google Sheets will recognize patterns in dates as well. So if you create two initial cells where the year increments by one instead of the day, selecting those two cells and filling down will fill the rest of the column incrementing the year by one in each cell.
Google Sheets also recognizes other dates like months, days of the week, and more. If your first cell is a month and you fill down, Google Sheets is intelligent enough to fill the rest of the column with sequential months.
The same is true if you use days of the week in the initial cells. Google Sheets will fill the column with sequential days of the week, and cycling back to Sunday from Saturday. This can be useful if you’re trying to do something like creating a Calendar in Google Sheets.
Google Sheets handles other units similarly, such as time, and even repeating text patterns with strings.
How to Configure Autofill
If you try any of the techniques above and fill down or the pattern autofill tricks aren’t working as described, you’ll need to enable autocomplete. To do this, select the Tools menu, and select Enable autocomplete from the dropdown list.
Once enabled, all of the fill down tips listed in this article will work as described above. You can save a lot of time once you start using the fill down and autofill features in Google Sheets. It’s especially useful if you are filling out a lot of columns in a very large sheet. Instead of dragging data down entire columns at a time, you can let Google Sheets do all of the work for you. Examples of data and result of drag down 1) AirR4 becomes AirR5, AirR6 etc. (I just want AirR4) 2)08-0320 become 08-0321, 08-0322 etc. (I just want 08-0320) Comment Name * Email *
Δ Save my name and email and send me emails as new comments are made to this post.
