8 Ways to Apply a Formula to an Entire Column in Microsoft Excel

Are you wondering how to apply a formula to an entire column in Excel? Read this Excel tutorial until the end to find out the tried and tested methods.

Efficiency, consistency, scalability, and automation are key considerations when working with spreadsheet data. Applying a formula across an entire column or until adjacent data is encountered is often necessary to streamline processes. However, doing so manually can be incredibly time-consuming, especially in Excel where a column can contain up to 1,048,576 cells.

Fortunately, Microsoft Excel offers a range of features, functionalities, and tools to tackle this task efficiently, saving time and effort. In this blog post, I’ll demonstrate multiple techniques within Excel for applying a formula to a whole column, empowering you to analyze your data with ease and precision.

📒 Read More: How Many Rows Can Excel Handle?

Using a Shortcut Key

Enter formula in the first cell
Enter formula in the first cell

On your Excel dataset, highlight the first cell below the column header and enter your formula.

Now, hit Enter to calculate the output of the entered formula.

Select cells in the column
Select cells in the column

Highlight the formula cell again and then use Shift and Down Arrow to highlight the part of the column until the cell where data exists in the adjacent column.

Selct all cells of a column
Select all cells of a column

Alternatively, press Ctrl + Shift + Down Arrow to highlight all the cells of the column.

Fill column with formula
Fill column with formula

Now, press Ctrl + D to fill the cells with the formula in the top cell of the column. This action will overwrite any content in the selected cells of the column.

Using Copy & Paste

Another quick and easy way to fill the whole column with a select formula is the copy-and-paste method.

Formula in first cell
Formula in first cell

Firstly, create and enter an Excel formula in the first cell of the column after the column header. Don’t forget to hit Enter to calculate the value of the cell.

Copying the formula cell
Copying the formula cell

Now, press Ctrl + C to copy the cell.

Highlight whole column
Highlight whole column

Use Shift + Down Arrow to select the partial column or Ctrl + Shift + Down Arrow to select the whole column until the 1,048,576th cell.

Paste and calculate formula
Paste and calculate formula

Press Ctrl + V to paste the formula. Excel shall automatically calculate the values of the cells where you’ve copied the formula.

Using the Fill Handle

Another fast method for applying a formula to an entire column in Excel is using the fill handle feature. However, for this technique to work, the adjacent columns to the left of the selected column must contain data up to the 1,048,576th cell. The fill handle tool automatically extends the formula in the selected column until it encounters data in the adjacent columns involved in the calculation.

A formula in the first cell
A formula in the first cell

On your Excel worksheet, navigate to the first column below the target column’s header. There, enter the column you want to paste in the entire column.

The fill handle
The fill handle

Now, highlight the first cell again. You should see a tiny solid square box in the bottom right corner of the selected cell. That’s the fill handle.

Double-clicking fill handle
Double-clicking fill handle

Double-click on it to copy and paste the formula down the column.

The fill handle has been available since the Excel 2010 desktop app. If you’re using an earlier edition, you can’t use this method.

Enable fill handle
Enable fill handle

If you’re on Excel 2010 or a better edition and unable to see the fill handle, press Alt + F + T on the keyboard to bring up the Excel Options dialog.

On Excel Options, click the Advanced category on the left-side panel. Then, checkmark the checkbox for Enable fill handle… below the Editing options section on the right-side panel. Don’t forget to click OK to save the changes you’ve made.

Using the Fill Down Tool

Fill Down is the Excel ribbon command. If you’re unable to use the Ctrl + D shortcut key to copy a formula to an entire column, you can use the Fill Down tool.

Selecting whole column along with formula cell
Selecting whole column along with formula cell

After entering the formula in a cell of the target column, select the cell along with the rest of the cells of the column using the Ctrl + Shift + Down Arrow keys.

Using the Fill Down toll
Using the Fill Down toll

Now, go to the Home tab and navigate to the Editing command block. Click on the Fill drop-down arrow and choose the Down option from the context menu.

Excel shall fill the whole column with the formula of the selected cell. It’ll also calculate the cells as it copies the formula.

Using an Array Formula

If you enter an array formula in a cell of a column, Excel will automatically calculate the cells below the formula. Excel will calculate the cells until it encounters a blank cell in the adjacent columns referred to in the selected formula.

Entering an array formula
Entering an array formula

In the above dataset, I’m calculating the amount of a 20% discount from MSRP. To do this calculation for all the cells in column D (MSRP column), I’ll use an array formula as shown below:

=D2:D10*20%

Ensure you adjust the references and values in the formula according to your own dataset.

Filling all the cells down the column with a formula
Filling all the cells down the column with a formula

Upon pressing the Enter (Shift + Enter on older Excel editions) key on the keyboard, Excel will calculate all the cells in the Discount column until the last cell where values exist in the adjacent cells.

Now, if your dataset contains referred data in the formula until the 1,048,576th cell, Excel shall calculate the values for all the cells using the same array formula.

A drawback of this method is if someone deletes the parent array formula, all the cells will lose their calculated values.

Using the Excel Table

Provided that the feature AutoCorrect Options for Fill formulas in table is active, Excel will overwrite all the cells in a column, except the header cell will a formula entered in any cell of the said column.

However, this method will only work if you’ve created the table before entering the formula in any column of the cell. Let’s look at how it works.

Example dataset
Example dataset

Suppose you want to calculate the cells in the Cost column by referring to the adjacent cells under the Nos. and Rate columns in the above dataset.

For the calculation, you’re going to use the following formula:

=B2*C2

Now, you’re planning to apply the same formula in all the cells of the Cost column and calculate the cells in one go.

Convert to table
Convert to table

First, you must convert the dataset to an Excel table. To do so, you’ll highlight any cell on the dataset and press Ctrl + T. Now, click OK on the Create Table dialog to create the table object.

Enter formula in any cell of table
Enter formula in any cell of table

Now, copy and paste the above formula into any cell of the Cost column and hit Enter.

Excel fills whole column with the formula
Excel fills whole column with the formula

Excel shall overwrite all the cells in this column with this formula and calculate the cells as well.

Enabling Fill Formulas in Table

AutoCorrect Options for Fill formulas in table have been available since Excel 2007. If you’re using compatible Excel software but are still unable to use this feature to apply a formula to a whole column in Excel, you must enable the feature.

AutoCorrect Options
AutoCorrect Options

Press Alt + F + T to bring up the Excel Options dialog.

On Excel Options, click the Proofing category on the left and click the AutoCorrect Options button.

AutoCorrect dialog
AutoCorrect dialog

Now, you should see the AutoCorrect dialog. There, checkmark the checkbox for Fill formulas in table… feature.

Click OK on the AutoCorrect and Excel Options dialogs to save the changes you’ve just made.

Now, perform the steps mentioned earlier to use AutoCorrect Options for Fill formulas in table.

📒 Read More: How to Spell Check in Microsoft Excel

Using the AutoCorrect Options in a Table

If you’re unable to enable Fill formulas in table in your Excel desktop app but the software edition supports the feature, you’ll see the AutoCorrect Options as a suggestion in your table if you enter a formula in any column.

Using this suggestion box, you can apply a formula to an entire column in Excel.

Dataset as a table
Dataset as a table

Let’s consider that you’ve already created your Excel table using the worksheet dataset.

Type formula in table column
Type formula in table column

Now, enter the desired formula in the target column of the table and hit Enter.

Overwrite all cells
Overwrite all cells

You shall see a small AutoCorrect Options suggestion box to the right of the cell just below the formula cell. Click on that and click on the Overwrite all cells… option in the overflow context menu.

Applying a formula to entire column
Applying a formula to an entire column

Excel shall copy and calculate the formula in all the cells of the target column.

Using Excel VBA

So far you’ve learned all the techniques to apply one formula to the whole column using various Excel command buttons and shortcuts.

Suppose, you want to automate this task as a part of a larger Excel automation, you can use Excel VBA scripting. Here’s a script that’ll do the following:

Selecting column for VBA Macro
Selecting column for VBA Macro
  • Show an input box so you can select the target column using the mouse.
  • The VBA script shall ignore the first cell of the selected column because it might contain a column header.
  • It’ll loop through the selected column to detect the formula you want to apply to the entire column from the 2nd cell to the 1048576th cell.
  • If the script finds any formula in the column, it’ll copy and paste the same in the entire column.
Applying a formula to entire column using VBA
Applying a formula to entire column using VBA
  • The VBA script shall also copy the number formatting of the first formula cell to the rest of the cells of the column.
The VBA script to copy formula
The VBA script to copy formula
Sub ApplyFormulaToColumn()
    Dim rng As Range
    Dim cell As Range
    Dim formula As String
    Dim numFormat As String
    
    ' Prompt the user to select a column
    On Error Resume Next
    Set rng = Application.InputBox("Select a column", Type:=8).EntireColumn
    On Error GoTo 0
    
    ' If no range is selected, exit the subroutine
    If rng Is Nothing Then Exit Sub
    
    ' Loop through each cell in the column, starting from the second cell
    For Each cell In rng.Cells
        If cell.Row > 1 Then
            ' If the cell has a formula, store it and exit the loop
            If cell.HasFormula Then
                formula = cell.formula
                numFormat = cell.NumberFormat
                Exit For
            End If
        End If
    Next cell
    
    ' If no formula was found, exit the subroutine
    If formula = "" Then Exit Sub
    
    ' Apply the formula and number format to the entire column, starting from the second cell
    With rng.Cells(2, 1).Resize(rng.Cells.Count - 1, 1)
        .formula = formula
        .NumberFormat = numFormat
    End With
End Sub

To find out the easy ways to create an Excel VBA macro using the above script, check out this article:

📒 Read More: How To Use The VBA Code You Find Online

Back up your workbook before using any Excel VBA script. You won’t be able to Excel Undo feature to undo the changes made by the VBA macro.

Conclusions

Now you know how to apply an Excel formula to the whole column. Try out the methods you like and comment below to let me know if the article helped or not. If you know another trick to apply a formula to an entire column, do mention that in your comment.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Subscribe

Subscribe for awesome Microsoft Excel videos 😃

Related Posts

Comments

3 Comments

  1. SK. Barret

    Hello,
    I might have missed it, but I haven’t seen selecting all cells (CTRL shift down arrow), typing the formula and then using CTRL ENTER to validate in the whole selection.
    Cheers

    Reply
    • John MacDougall

      Definitely a good method!

      Reply
  2. Mike

    Workbook File size may be a consideration when choosing a method.
    I created a simple small worksheet, similar to your example with 18 rows and 5 columns, with a formula in column E.
    With formula simply copied down to row 18 the workbook size was 14K
    With formula copied down to fill whole column the workbook size was 9,950K
    Using Array fill for whole column the workbook size was now 25,316K

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃