What Is Google Sheets Fill Down?
Google Sheets fill down is a feature that enables us to automatically populate data, which could be values, complex patterns, or formulas, in a target cell range. The feature is also referred to as the fill handle. It appears as a small Blue circle at the bottom-right corner of the chosen source cell or range, holding the data we aim to fill down in the target cells.
Users can utilize the fill down option in Google Sheets to update data in massive and complex datasets quickly and with better accuracy.
For example, we have a list of item categories in column A, with the discount rate for the first category specified in cell B2.
The task is to update the same discount rate as in cell B2, in the remaining target cells B3:B5.
Then, we can perform Google Sheets fill down same value operation in the specified target cells, as explained below.
We can select cell B2 to view the fill handle option, similar to Excel fill handle, at the bottom-right corner of the cell, where we need to place the cursor. Next, while pressing the cursor, drag the cursor down till cell B5 to complete the Google Sheets fill down same value action.
Next, we can select the entire dataset range and apply the required border setting using the Borders option in the ribbon to achieve the output as shown above.
The above example shows one of the Google Sheets fill down entire column range methods. We shall see the remaining techniques in the ensuing sections.
Table of contents
Key Takeaways
- The Google Sheets fill down or handle feature allows one to automatically update data, such as values, complex patterns, or formulas, in the target range.
- We can use the feature using the small Blue circle, appearing at the bottom-right corner of the selected cell or range. While we can grab and drag the fill handle, we can double-click the fill handle when there is data in the adjacent columns to fill down data in the required range.
- Users can utilize the fill down option in Google Sheets to populate data in large and complex datasets with precision and in one go.
Google Sheets Fill Down Shortcuts
The shortcuts to use the Google Sheets fill down command are the following:
- Ctrl + D on Windows
- Cmd + D on Mac OS
- Ctrl + Enter on all platforms
The first two Google Sheets fill down command shortcuts will fill down the chosen cell or set of cells. On the flip side, the third shortcut will fill the chosen value or formula in all the selected cells in one go.
How To Use Fill Down In Google Sheets?
We can utilize Google Sheets fill down entire column range to populate static or sequential data or formulas, as explained below:
Static Data Fill Down
In some scenarios, we might have to populate the same data in the cell or range below a specific cell. The following fill down steps will help achieve the desired output.
- Choose the cell with the data we aim to populate in the target cells. Next, press the cursor on the fill handle icon at the bottom right corner of the chosen source cell.
- Now, drag down the cursor to the last target cell to fill the required cell range right below the source cell.
Sequential Data Fill Down
In some cases, we may have to enter a sequence of numeric values (for example, 1, 2, 3, and so on) in a set of cells in a column. Then, we can perform Google Sheets fill down numbers in a sequential order using the following steps.
- First, enter the first two or three numbers of the sequence in the first two or three cells of the target range.
- Choose the populated cells and drag the fill handle downwards till the last target cell to update the subsequent numbers in the sequence in the remaining required cells.
Formulas Fill Down
- First, enter the formula in the topmost cell of the range we aim to fill down.
- Choose the cell holding the formula to view the fill handle icon.
- Place the cursor on the fill down icon. Next, click the cursor and drag it downwards till the last target cell to fill down the formula in the required range. The Google Sheets cell references, which are the same as Excel cell references, in the formulas applied in the respective target cells will change according to the relative or absolute references, which are the same as Excel relative references and absolute reference, cited in the source formula.
Please note that if the dataset contains data in the adjacent column on either side of the target column, then we can choose the topmost cell of the target range. Next, we can double-click on the fill handle icon to fill down the remaining cells in the target range. It applies to all the three methods.
Examples
Refer to the following Google Sheets fill down examples to understand the feature effectively.
Example #1 – Fill Blank Cells With Above Cell Value
The source dataset lists products in column A. Column B shows the product categories.
The requirement is to update the product categories in the blank cells based on the previous cell. In other words, the category in cells B3:B4, B6:B8, and B10:B12 should be A, B, and C, respectively.
We shall see two methods to secure the required output, with the first method output displayed in column C and the second method output displayed in column D.
Method #1 – Using Copy And Paste Options
Step 1: Choose cell C3, enter the following formula, and press Enter.
=C2
Step 2: Select cell C3 and press Ctrl + C to copy the cell value.
Step 3: Choose cell B4. Next, press Ctrl and select the ranges B6:B8 and B10:B12. After that, use Ctrl + V to paste the copied formula into the chosen cells.
Method #2 – Using IF Function
Step 1: Select cell D2, enter the following formula, and press Enter.
=B2
Step 2: Choose cell D3, enter the following IF()-based formula, and press Enter.
=IF(LEN(B3),B3,D2)
The IF() and LEN() work with the same logic as Excel IF function and LEN function.
Step 3: Place the cursor on the fill handle icon. Next, while pressing the cursor, drag the cursor till the last cell in the target range.
Thus, we see the required data filled down in the required cell range, similar to Google Sheets fill down numbers.
Example #2 – Filling Down Formulas
Cell A2 holds a fixed monthly income value. Next, the data range C1:E7 shows the monthly expenses from Jan-Jun.
We must determine the monthly savings based on the fixed monthly income and expenses and display the output in column E cells E2:E7.
Step 1: Select cell E2, enter the following formula, and press Enter.
=$A$2-D2
Step 2: Double-click the fill handle icon to populate the remaining target cells E3:E7.
We use absolute reference when referencing the fixed monthly income value in the formula, as it is a constant value. On the other hand, we use relative reference when referencing the expense value in the formula, as it varies in every row.
Example #3 – Autofill
The following dataset shows the sales figures from 2021-23 at various branches of a firm.
We must evaluate the branch-wise total sales figures and showcase the output in column E cells E2:E6.
Step 1: Select cell E2 and enter the following SUM(), which works like the Excel SUM function.
=SUM(B2:D2)
Once we press Enter, we can see the AUTO FILL message box showing the suggested autofill, with the remaining target cells showing the required output.
Finally, click the Tick mark in the message box to complete the Autofill action and view the output as depicted below.
Example #4 – Fill Down Combining Words and Letters
The source dataset holds a set of employees’ first and last names and grades in columns A, B, and C.
We should combine the first and last names, with a space character in between, and the grade for each employee and showcase the output in column D cells D2:D6.
Step 1: Select cell D2, enter the following CONCATENATE(), which is similar to CONCATENATE in Excel, and press Enter.
=CONCATENATE(A2,” “,B2,”_”,C2)
Step 2: Select cell D2 and double-click the fill handle icon to fill down the formula to combine the words and letters in each target cell.
Example #5 – Fill Down Months (Or Months And Years)
The source dataset lists a set of employee names in column A who are the monthly top performers at a firm from January to December in 2023.
We must update the month and year in column B for each employee in the format specified in the column B heading.
Step 1: Choose cell B2, enter the required month and year with a Hyphen in between, and press Enter.
Next, select cell B3, enter the next month and year with a Hyphen in between, and press Enter.
Step 2: Select cells B2:B3 to view the fill handle icon.
Finally, double-click the fill handle icon to fill down the remaining target cells.
The output shows that while the months follow the chronological order, the year remains the same in all the target cells. The reason is that though we enter the month and year in cell B2:B3, they are date values. So, the fill down option updates the remaining target cells with dates incremented in the month order.
Important Things To Note
- Ensure the cell references are correct in the formula applied in the topmost cell of the target range before using the Google Sheets fill down feature to apply the formula in the remaining target cells.
- Ensure the value, data pattern, and formula syntax to fill down in Google Sheets is accurate to avoid errors.
- The keyboard shortcuts to fill down in Google Sheets are Ctrl + D and Cmd + D on Windows and Mac OS respectively. They are the same as the Excel keyboard shortcuts.
- The Tools – Autocomplete – Enable autocomplete option in the ribbon should be checked to ensure we can use the Autofill option to fill down in Google Sheets.
Frequently Asked Questions (FAQs)
We can fill down the same date in Google Sheets using the following method, explained with an illustration.
The source dataset holds a list of item codes and their date-wise inventory levels.
Assume the data is for the date 1-July-2024, which we must update in column A cells A2:A6. Then, the steps are as follows:
Step 1: Select cell A2, enter the date value as mentioned above, and press Enter.
Step 2: Select cell A3, enter the following expression, and press Enter.
=A2
Next, select cell A3 to view the fill handle option or icon. Finally, double-click the fill handle to fill down the same date value in the remaining target cells.
Google Sheets is not auto-filling down, perhaps because the Tools – Autocomplete – Enable autocomplete option is not checked in the ribbon.
You can fill down through Autofill in Google Sheets without dragging using the keyboard shortcut Ctrl + D in Windows and Cmd + D in Mac OS.
Download Template
This article must be helpful to understand the Google Sheets Fill Down, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Google Sheets Fill Down. Here we learn how to use it to automatically populate data and its shortcuts with examples. You can learn more from the following articles. –
Leave a Reply