Mehrab Imtiaz
Inputting data in a spreadsheet one by one can be long and tedious. Thankfully, applications like Google Sheets provide its users with multiple ways to fill down an entire column automatically in just a couple of clicks.
In this article, we will look at some of these features and some user-defined methods that will make data and formula entry in Google Sheets efficient and fun.
Let’s get started.
4 Ways to Fill Down an Entire Column in Google Sheets
When we say the entire column, we often mean up to the last row of the table or dataset.
If that is the case, then there are multiple ways we can fill the entire column in Google Sheets automatically.
Let’s look at some of the most common and the most straightforward to implement.
Note that the autofill methods we are using do not involve dragging the fill handle.
1. Trigger the Autofill Suggestion of Google Sheets
Google Sheets has an autocomplete feature activated by default. The application actively looks for formulas or data entry patterns that it uses to suggest potential formulas to its users.
One such updated feature is the Smart Fill of Google Sheets, but that is a topic for another day.
Now, we will focus on the autofill suggestion that Google Sheets presents when entering a formula.
To trigger this, simply start a formula. Google Sheets will automatically catch on to the pattern and suggest an autofill:
The pop-up is the Suggested Autofill that you can apply by clicking on the tick mark. The result:
2. Double Click the Fill Handle to Automatically Fill the Entire Column
The alternative way to apply an autofill without dragging is to simply double-click on the fill handle.
Step 1: Select the cell that contains a formula or a value that you want to fill down the column with. You will notice a small blue square at the bottom-right of the active cell. This is the fill handle.
Step 2: Moving the cursor over the fill handle will turn it into a plus symbol (+). At this point, double-click to fill the value down the entire column in the Google Sheets worksheet.
While this is a fairly automatic and efficient method, the range of the autofill solely depends on the adjacent data available. To put it simply, if this autofill reaches an adjacent blank cell, it will stop, that is the data range limit.
Learn More: Google Sheets Autofill: Fill Cells Automatically (A Comprehensive Guide)
3. Using Keyboard Shortcuts to Fill Down Entire Column in Google Sheets
There are two specific keyboard shortcuts associated with filling down an entire column with a value or formula in Google Sheets:
- CTRL+D (Fill Down)
- CTRL+ENTER (Fill All)
For Mac users: Use ⌘ (Command Key) instead of CTRL.
Step 1: Select the range of cells you want to auto-fill. You can use SHIFT+Left Click on the last cell to select everything in between. The shortcuts depend on the selection.
Step 2: Use any shortcut to fill down the entire column.
Note that the CTRL+D shortcut only fills down the column, whereas CTRL+ENTER fills the entire selection.
Another important thing to note is that the shortcuts will fill all selected cells, even if they are not utilized.
4. Using ARRAYFORMULA to Generate Results in an Entire Column in Google Sheets
Another way to fill down an entire row in Google Sheets is by including the rata range in the reference of the formula.
And as you may have already guessed, this method only works with formulas.
Consider the example we have been using so far, where we are multiplying two adjacent values, Units Sold and Price, to get Revenue:
=A2*B2
So, let’s update the formula so that both multiplicand and multiplier are their respective column ranges:
=A2:A21*B2:B21
Now, if we press ENTER, we will only get the topmost value (as seen in the bubble above the active cell). But that doesn’t mean the other rows aren’t calculated; they are still there.
To bring out all the calculated values, we must apply the ARRAYFORMULA function around this formula.
Tip: Instead of manually typing in the ARRAYFORMULA function, place the text cursor at the end of the formula and use the keyboard shortcut: CTRL SHIFT+ENTER.
=ArrayFormula(A2:A21*B2:B21)
The formula needs to be applied once as it stores all the calculations. The ARRAYFORMULA function simply helps present the stored results.
Learn More: How to Autofill Formula in Google Sheets (3 Easy Ways)
Final Words
That concludes all the ways you can use to fill down an entire column in Google Sheets. Most methods use amazing built-in features of Google Sheets with the exception of the ARRAYFORMULA which is simply a function. Most veteran users know of the versatility of the function in a spreadsheet.
Feel free to leave any queries or advice you might have for us in the comments section below.
