Google Sheets is the best spreadsheet program to organize and analyze datasets using formulas. You can use various methods, such as dragging the Fill Handle or saying yes to the Auto Fill suggestions to apply a formula to an entire column.
Formulas let users perform various calculations quickly.
However, manually entering the same formula for hundreds or thousands of cells is not ideal. It may take hours for anyone.
There are various ways to apply formula to an entire column in Google Sheets.
In this quick guide, let us discuss the easiest methods to apply a formula to an entire column. We are about to discuss several built-in functions and features in Google Sheets, so read the article until the end.
Click on the following links to jump to that particular article section.
- Using Auto Fill suggestion
- Using the keyboard shortcut
- Dragging the Fill Handle
- Double-clicking on the Fill Handle
- Using the ARRAYFORMULA function
We’ll discuss the five methods mentioned above in detail in the upcoming sections of this article. Let’s begin!
Copy the Example Google Sheets
Consider getting the following Google Sheet file, which contains the spreadsheets with the example dataset we used to demonstrate applying a formula to an entire column.
Make sure to follow along to practice the methods we are about to discuss.
It is okay if you have your own Google Sheet ready with the required dataset. You can skip getting the above file.
Now, without any further ado, let’s begin.
How to Apply a Formula to an Entire Column in Google Sheets
Often, you may feel the need to apply the same methodology or formula to hundreds or thousands of cells.
There are various methods to do so. We will discuss all of them.
Let us start with the quickest method first.
METHOD #1 – Using the Auto Fill Suggestion to apply formula to an entire column in Google Sheets
In my opinion, this might be the quickest method for everyone. Google Sheets displays this feature once only after you write the formula within a cell and hit Enter.
Task: Extract the hiring month from the employee hiring date. Refer to the following image.
We will use the TEXT function in Google Sheets to extract the month from a date.
Here are the steps to apply a formula to an entire column in Google Sheets:
- Click on the very first cell of the column. In our case, it is the cell “C2“
- Type “=text”
- Select the first option from the popup or press “Tab” key
- For the “number” argument, let us provide the cell reference as “B2
- Press “,” to move to the next argument
- For the “format” argument, let us type “mmmm“
(Make sure to use double quotation marks; otherwise, this formula won’t work) - Complete the bracket using “)“
- Press “Enter“
- Now, you will see a popup that says “AUTO FILL“, as shown in the following image
- Click on the Check icon to use the AUTO FILL feature
The popup appears instantly after you press the “Enter” key.
Please note that if you choose not to use the AUTO FILL by pressing the Cross icon, then the popup won’t be displayed after that.
METHOD #2 – Using the keyboard shortcut to apply formula to an entire column
This method also doesn’t require any expert knowledge and allows users to copy a formula to an entire column within a few seconds.
Task: Merge first and last name to create a full name. We have two columns for first and last names, respectively.
Let’s begin,
- Click on the very first cell of the column
- Type “=concatenate”
- Press the “Tab” on your keyboard
- Provide the cell reference as “A1“
- Press “,“
- For the space between the first and last name, we need to type ” “
(Make sure to use double quotation marks and put the space between them) - Press “,“
- Provide the cell reference as “B1“
- Complete the bracket using “)“
- Press “Enter” key
- Select the entire column as shown below
- Press “Ctrl +D” on your keyboard
Note that the formula is also applied to current table rows and future rows. For example, our table ends at row 10, but if you click on the cell “C11”, you will notice that the formula is applied.
It means any first and last name in the row beyond 10 will be automatically converted to the full name in column C.
METHOD #3 – Dragging the Fill Handle to apply a formula to an entire column
This method requires users to drag the formula to the end of the table.
It is one of the most used methods to apply a formula to an entire column in spreadsheet software like Google Sheets and Microsoft Excel.
The Fill Handle appears at the lower-rightmost corner when you click on any cell. Refer to the following image.
Now, let us understand how we can use the Fill Handle to apply a formula.
Task: Get the month from the Employee Hiring Date in the following table.
Let’s begin,
- Click on the cell “C2“
- Type “=text”
- Select the first option from the popup or press “Tab” key
- Replace the “number” argument with the cell reference “B2“
- Press “,“
- Replace the “format” argument with the “mmmm“
(Make sure to use double quotation marks, or else the formula won’t work at the end) - Complete the bracket using “)“
- Press “Enter“
- Now, click on the cell “C2” again
- You will notice a Fill Handle at the lower-rightmost corner of the cell
- Hover over the Fill Handle until you see the “+” icon
- Click and drag that “+” icon to the end of the table
Dragging the Fill Handle is ideal when you have a table which contains several tens and hundreds of rows.
So, when you have a massive dataset with thousands of rows, use the following method.
METHOD #4 – Double-clicking on the Fill Handle to apply the formula to an entire column
This method uses the similar steps as discussed in the previous method. But, instead of dragging the formula, you need to double-click on the Fill Handle.
It is helpful while dealing with massive datasets that include thousands of rows.
We will refer to a similar task to practice this method.
Task: Get the month from the Employee Hiring Date in the following table.
Let’s begin,
- Select the cell “C2“
- Type “=text”
- Select the first option from the popup
- For the “number” argument, provide the cell reference as “B2“
- Press “,” to move to the next argument
- For the “format” argument, type “mmmm”
(Ensure that you are using double quotation marks to avoid errors in the future) - Complete the brackets using “)“
- Next, click on the cell “C2“
- Hover over the Fill Handle of the cell “C2” until you see the “+” icon
- Double-click on the “+” icon
Google Sheets will instantly apply the formula to the entire table.
This method is straightforward and common among popular spreadsheet programs such as Google Sheets and Microsoft Excel.
METHOD #5 – Using the ARRAYFORMULA function in Google Sheets to apply the formula to an entire column
This is the final method in this article to apply the formula to an entire column. It includes using the built-in function named the ARRAYFORMULA.
It is an easy-to-use method if you understand the ARRAYFORMULA function.
So, before we proceed further, let’s quickly discuss the function.
Explained: The ARRAYFORMULA in Google Sheets
As the name suggests, it is an array formula with simple general syntax, as shown below.
=ARRAYFORMULA(array_formula)Here, the “array_formula” argument must be replaced with the cell range or an expression that uses the cell range.
So, from the syntax, we can say that the function deals with a range. In other words, it means all you need to do is simply define the array’s size.
It applies a predefined formula to a range selected by the user. Simply put, the function turns a single formula into an array.
Let’s use this function to insert a formula into the entire column.
Task: Extract the month from the employee hiring date, as shown in the following image.
Here are the steps:
- Select the very first cell of the column. In the case of the above example, it is the cell “C2“
- Type “=arrayformula”
- Select the first option from the popup or press “Tab” on your keyboard
- Now, for the “array_formula” argument, let us use the Text function by typing “text”
(As the Text is a function in Google Sheets, you will notice a new popup as shown in the above image) - Select the first option from the popup
- Replace the “number” argument of the Text function. Let us provide the cell range as “B2:B11“
- Press “,“
- Now, for the “format” argument of the Text function, let us type “mmmm”
(Using double quotation marks is compulsory, or else the formula will return an error at the end) - Complete the parentheses for the Text function using “)“
- Complete the parentheses for the ARRAYFORMULA function using “)“
- Press “Enter“
Here’s how our final formula looks,
=ARRAYFORMULA(TEXT(B2:B11,"mmmm"))As discussed earlier, the ARRAYFORMULA applies to the range defined by the users.
Avoid entering any values in the column where you use the ARRAYFORMULA function.
If you edit any cell of the column, the formula will stop working, and all the results will disappear. Refer to the following image.
It is the common thing with array formulas. You are not allowed to edit any cell within the defined range.
FAQs
Q. What are the methods to apply a formula to an entire row in Google Sheets?
In the case of rows, users only have one option compared to applying a formula to an entire column.
All you can do is enter the formula and drag it to the end of the row.
Let’s consider an example where we wish to find the sum at the end of the table.
Here are the steps to apply a formula to an entire row:
- Click on the cell “C12“
- Type “=sum”
- Select the first option from the popup or press “Tab” key
- Provide the range as “C2:C11“
- Complete the bracket using “)“
- Press “Enter“
- Next, click back on the cell “C12“
- Go to the lower-rightmost corner of the cell over the Fill Handle
- You will notice a “+” icon appears
- Click on that “+” icon and drag the formula to the end of the table, as shown in the following GIF
It is the quickest method to apply a formula to an entire row. The other techniques we discussed in applying a formula to a whole column in the above section are not applicable.
Q. How can I copy a formula to an entire column without changing the cell reference?
In this case, you need to use the absolute references.
It is pretty simple: whatever your formula is, replace all the cell references by putting the dollar symbol before the column and row part of the cell reference.
Consider the following example where we need to assign the manager to each employee in the given table.
Here are the steps:
- Click on the cell “C4“
- Type “=”
- Put the cell reference as “B1“
- Now, put the dollar ($) symbol before the column and row part of the cell reference
- Press “Enter“
- Select the cell “C5“
- Go to the lower-rightmost corner of the cell “C5” over the the Fill Handle
- You will notice a “+” icon appears
- Double-click on that plus icon to apply the formula to the entire column
Here’s how the formula using the absolute references look like,
=$B$1
Note that you can use the shortcut key F4 to insert the dollar ($) symbol into the cell reference.
Conclusion
That’s all about applying a formula to an entire column or row.
It is pretty straightforward and can be done within a few seconds.
Most of the time, the Google Sheets will pop up an AUTO FILL dialog box as soon as you type the formula and press the Enter key.
Note that in the case of the ARRAYFORMULA function method, you may take some time based on your knowledge of the function.
Practice each method discussed above and figure out the one that feels quick and easy to you.
Let us know in the comment section if you are stuck somewhere or have any particular issue applying a formula to an entire column or row.