How To Apply Formula To Entire Column In Excel

Excel boasts over 450 formulas to help users analyze and organize massive datasets. Most of those formulas are easy to use, but a few require advanced knowledge. Once you apply a formula or create one using different functions, it is really easy to copy it across the remaining cells of the corresponding column or row.

In fact, formulas are focused on automating manual tasks and saving plenty of time for Excel users. Nobody wants to manually apply the same formula to the entire column. It is a lengthy and time-consuming process.

Excel does not suggest manually applying or inserting the same formula into hundreds or thousands of cells. It offers several quick ways to do the job within a few seconds.

This is a quick guide about how to apply formula to entire column in Excel. We are about to discuss several built-in functions and features in Excel, so make sure you read the article until the end.

Use the following links to jump to a particular section of your interest,

Using the Fill Handle
Using the Fill Tool option
Using the Keyboard Shortcut
Using the Array function

Download the Example Excel Sheet

Consider downloading the following Excel Sheet, which contains the spreadsheets with the example dataset we used to demonstrate applying a formula to an entire column.

Click Here To Download!

Make sure you follow me along and practice the functions and tools we are about to discuss.

It is okay if you have your spreadsheet ready with the required dataset. You can skip downloading the above file.

How to Apply a Formula to an Entire Column in Excel

Every time we successfully insert a formula and get the desired results, the very next thing is to apply the same formula to the remaining cells of the respective column.

In this section, we will take a quick look at various methods to insert formulas into hundreds and thousands of cells of a column or row.

Let us start with the easiest method.

METHOD #1 – By Double-Clicking on the Autofill Icon

Using the Autofill feature allows you to apply the same formula to the entire column with a single click.

Consider the following example where we have employee details, including their full name and hiring date.

Our task is to find the month from the given hire date.

We will be using the TEXT function to derive the desired output. Our formula will be as follows for the first date,

=TEXT(B2,”mmmm”)

Note that our first date is in the cell “B2”.

Now, let’s begin to apply the same formula to the rest of the cell.

Select the cell where we wish to insert the formula. 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 quotations; otherwise, this formula won’t work)
Complete the bracket using “)“
Press “Enter“
Next, select the cell “C2” again
You will observe the green dot, which is also popular as the auto-fill handle icon. Refer to the following image,
Hover over that green dot, and it will change to a “+” icon
Double-click on the plus sign to populate the same formula to the entire column

As you can see in the above GIF, Excel will instantly apply the formula to the rest of the cells. Note that the dynamic parameters, including cell references, are updated automatically while the same formula is applied to the remaining cells of the column.

METHOD #2 – Dragging the Auto-fill Handle

This method is similar to the first method except that instead of double-clicking the Fill Handle icon, you will drag it to the end of the table.

It also allows users to copy a formula to an entire column within a few seconds. This method is the most preferred one for populating the formula in the whole column.

We will consider the same example dataset. Our task is to get the month from the given date.

Let us use the TEXT function to get the desired results.

Here are the steps,

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 “,” to move to the next argument of the TEXT function
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 that includes a table with less number of rows. When you have a massive dataset with thousands of rows, use the following method.

METHOD #3 – Using the Fill Tool

Excel has a special feature known as “Fill Down” that lets you apply a formula to the entire column.

It aims at replicating the same formula or calculation across a column, saving you a lot of time and effort.

Consider the following dataset,

Our task is to extract the month from the given hire date.

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 “,” to move to the next argument
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“
Select the cell with the formula, as well as the cells below it where you wish to insert the given formula
Next, hover over the main menu and click on the “Home” tab
In the “Editing” group, click on the “Fill” icon as shown below
You need to choose the “Down” option from the list

After following the above steps, Excel will fill the selected cells with the content from the top cells, as shown below.

This feature can help you quickly and efficiently apply the same formula across an entire column. It is beneficial when dealing with large datasets, as it allows you to perform repetitive tasks with much ease.

METHOD #4 – Using the Keyboard Shortcut

One more method that doesn’t require any expert knowledge and allows users to copy a formula to an entire column within a few seconds.

Now, let us consider a different and simpler example: we have first and last names in different columns.

Our task is to combine the first and last names to create a full name.

Let’s begin,

Click on the desired cell where we wish to insert the first formula
Type “=CONCATENATE”
Press the “Tab” on your keyboard
Provide the cell reference as “A1“
Press “,” to move to the next argument
For the space between the first and last name, we need to put the space as “ “
(Make sure to use double quotation marks; otherwise, the formula won’t return the results as expected)
Press “,“
Provide the cell reference as “B1“
Complete the bracket using “)“
Press the “Enter” key
Select the entire column as shown below
(You can click on the letter representing the column header at the top)
Press “Ctrl + D” on your keyboard

Note that the formula is also applied to the current table range. In the future, if you add new entries to the column, the formula will be automatically applied to the respective cell from the given row.

For example, our table ends at row 10, but if you click on the cell “C11,” you will notice that the formula is applied. As soon as you insert the details in cells A11 and B11, the corresponding results will be displayed in cell C11.

In other words, it means any first and last name in the row beyond ten will be automatically converted to the full name in column C.

METHOD #5 – Using the Array Formula

The final method in this article is to apply the formula to an entire column.

Array formulas in Excel allow users to perform calculations on a range of cells, making it easy to update the given table according to expectations and obtain the desired outputs.

Let’s understand this with an example,

Our task is to obtain the month from the given hire dates.

We will use the TEXT function, which is also an array formula in Excel.

Let’s begin,

Select the cell “C2”
Type “=TEXT”
Select the first option from the popup or press “Tab” on your keyboard
In place of the “number” argument, select the cell range
(Here, the data range or array is “B2:B11”)
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)
End the TEXT formula using “)“
Press “Enter“

Here’s how our final formula looks,

=TEXT(B2:B11, “mmmm”)

If you wish to apply the formula to a single cell, then press “Ctrl + Shift +Enter” on your keyboard.

Note that the Excel will place curly brackets “{ }” around the formula to indicate that it is an array formula.

Finally, avoid entering any values in the column where you use the array function. If you edit any cell of the column, the formula will stop working and return a “#SPILL” error.

Refer to the following image.

FAQs

Q. How do I apply a formula to an entire row in Excel?

We have discussed various methods to apply the same formula across the entire column.

However, there is only one method for applying a formula to an entire row. You just need to enter the desired formula, activate the fill handle, and drag it to the end of the row.

Let’s understand the process with an example,

Our task is to find the total monthly sales generated by all the reps at the end of the table.

Here are the steps,

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 observe a “+” icon
Click on it and drag the formula to the end of the table, as shown in the following GIF

Q. How can I copy a formula to an entire column without changing the cell reference?

If you wish to insert the same formula without changing the cell references, you can use the absolute references.

It is pretty simple: whatever your formula is, insert dollar symbols into the cell references.

Let us consider the following example where we need to assign the manager to each employee in the given table.

As you can see in the above image, the manager’s name is mentioned at the top of the table. Our task is to use the name from the given cell and insert it across the table.

Let’s begin,

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. Refer to the following image,|
Press “Enter“
Next, select the cell “C4” again
Go to the lower-rightmost corner of the cell “C5” over the Fill Handle icon
You will observe that a “+” icon
Double-click on it
(You can also click once and drag the Fill Handle icon to the end of the table)