In this article we will show you how to fill down Google Sheets by using the drag method and the autofill feature.
This is a simple method to copy the same data down a column. While our example will use a number as dummy data, you can use this method for the following:
We will have an example for each of these cases.
The value can be a number or a string, or even a formula.
When you select a cell, a blue highlight box appears around it. A small blue box appears on the lower-right corner. The cursor also changes shape from an arrow to a thin cross. When it changes shape, it means that the cursor is over the small box. Hold-click when it happens.
For short columns dragging it down works best while for longer ones that stretch beyond the screen, double-clicking the small blue box on the lower-right corner of the cell is preferrable.
Output:
Screencap:
If you want to fill down a series of sequential data, such as increasing or decreasing numbers, you just need to make a little adjustment to the steps highlighted above:
The first and second values can be a whole number or with decimal values, and can either form an increasing or a decreasing pattern. Adding the two values allows Google Sheets to analyze what pattern to use for the fill down command.
Selecting both cells helps Google Sheets to check the pattern to use.
If the range stretches beyond the screen, you can still double-click the small box, and you will get the same result.
The result is:
Screencap:
You can also fill down columns using the same formula in a few clicks. Here are the steps:
Set up the formula as you would, but make sure to set the references correctly as Google Sheets. The FAQ section below has a guide to help you set the references correctly in your formula.
When you select a cell, a blue highlight box appears around it. A small blue box appears on the lower-right corner. The cursor also changes shape from an arrow to a thin cross. When it changes shape, it means that the cursor is over the small box. Hold-click when it happens.
For short columns dragging it down works best while for longer ones that stretch beyond the screen, double-clicking the small blue box on the lower-right corner of the cell is preferrable.
Output:
Screencap:
This method works when you want to fill down a column with the same formula but with different inputs that depend on the row it is located. Here are the steps:
It is best to select the first cell of the column because it is also the closest to the header rows, if you have them. This allows you to later check the formula if it is correct or not.
Set up the formula for the first row and then press enter.
If it is the first time you add the formula, a pop-up box will appear labeled Auto Fill with a suggested autofill for the whole column. If you are satisfied with the suggestion, click the check button.
Output
Screencap
You can also fill down a series of dates using the same steps as in the previous examples. The major difference, however, is that the dates progress by a day each row by default:
Google Sheets will automatically detect the date format in the input, and will use it to customize your sheet.
When you select a cell, a blue highlight box appears around it. A small blue box appears on the lower-right corner. The cursor also changes shape from an arrow to a thin cross. When it changes shape, it means that the cursor is over the small box. Hold-click when it happens.
For short columns dragging it down works best while for longer ones that stretch beyond the screen, double-clicking the small blue box on the lower-right corner of the cell is preferrable.
Output:
The output is a series of dates. Google Sheets makes exceptions for dates and sets them in increasing value day-by-day.
Screencap:
By default, when you fill down dates in Google Sheets, the resulting column contains dates progressing as you go down the column. However, you may need to copy the same date along the column. Here is a workaround:
Input the date in one of the date formats allowed by Google Sheets.
The formula simply consists of the reference to the first cell:
=first_cell
You can either type the formula or simply type the equal symbol then click the first cell. Add a space before you press Enter because Google Sheets will automatically add +1 to the formula to create an increasing trend in the dates.
This is one of the ways to fill down the column. You can also hold-click the small box and then drag it down the column.
Output
Screencap
Google Sheets does a good job of adjusting the references when you fill down a column with the same formula. However, formulas can contain references that should be fixed. If you don’t fix these references, you can get errors and unusual values:
For example, we have the formula of a weighted score combining Criteria A, B, and C. The formula is:
= A_weight * Criterion_A + B_weight * Criterion_B + C_weight * Criterion_C
The formula for Row 3 (first row) is:
=B2*B3+C2*C3+D2*D3
The criteria weights are all along the 2nd column and are fixed there as they are supposedly applied to other rows. As you can see, when the fill down action was done, the formula references adjusted for other rows, so the formula now mistakenly sets the values from other rows as the criteria weights.
The solution is simple: Fix the criteria weights as absolute references before doing the fill-down step. It simply involves inserting dollar signs $ to references that need to be fixed. For this example, the formula for Row 3 becomes:
=$B$2*B3+$C$2*C3+$D$2*D3
The result is:
We hope this article has helped you and given you a better understanding of how to fill down in Google Sheets. You might also like our articles on how to remove gridlines in Google Sheets and how to fill series in Google Sheets.
Find out how to set up automatic emails from Google Sheets by clicking here.
