.png)
How to Fill Down in Google Sheets in 3 Simple Steps
In this article we will show you how to fill down Google Sheets by using the drag method and the autofill feature.
How to Fill Down in Google Sheets (Drag Method)
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:
- Static data such as numbers and strings
- Sequential data
- Formulas
We will have an example for each of these cases.
Fill Down Static Data (Drag Method)
1. Add the data you want duplicated to the first cell
The value can be a number or a string, or even a formula.
2. Hold click the small box on the lower-right corner of the highlighted cell
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.
3. Drag it down until you reach the range you need or double-click the small blue box on the lower-right corner of the highlighted cell
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:
Fill Down Sequential Data (Drag Method)
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:
1. Input the first and second values
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.
2. Select both cells
Selecting both cells helps Google Sheets to check the pattern to use.
3. Click and drag down the small box on the lower-right corner of the highlighted range
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:
Fill Down Formula (Drag Method)
You can also fill down columns using the same formula in a few clicks. Here are the steps:
1. Add the formula to the first cell
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.
2. Hold click the small box on the lower-right corner of the highlighted cell
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.
3. Drag it down until you reach the range you need or double-click the small blue box on the lower-right corner of the highlighted cell
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:
How to Fill Down in Google Sheets (Auto Fill Method)
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:
1. Click the first cell of the column
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.
2. Enter the formula then press Enter
Set up the formula for the first row and then press enter.
3. Click the check button for autofill suggestion (when it appears) or double-click the small blue box on the lower-right corner of the highlighted cell
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
How to Fill Down a Range of Dates in Google Sheets
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:
1. Select the first cell of the column then add the first date
Google Sheets will automatically detect the date format in the input, and will use it to customize your sheet.
2. Hold click the small box on the lower-right corner of the highlighted cell
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.
3. Drag it down until you reach the range you need or double-click the small blue box on the lower-right corner of the highlighted cell
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:
FAQs
How to Fill Down The Same Date in Google Sheets?
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:
1. Input the date in the first cell
Input the date in one of the date formats allowed by Google Sheets.
2. Add the formula =first_cell on the second cell
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.
3. Double-click the small box on the lower-right corner of the highlighted second cell
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
The Formula is Giving Wrong Values and Errors! How to Make It Work?
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:
