Formulas are the life and blood of Excel spreadsheets. And in most cases, you don’t need the formula in just one cell or a couple of cells.
In most cases, you would need to apply the formula to an entire column (or a large range of cells in a column).
And Excel gives you multiple different ways to do this with a few clicks (or a keyboard shortcut).
Working with Excel spreadsheets often involves applying formulas to analyze and calculate data. While you can manually copy and paste formulas to individual cells, it becomes tedious when dealing with large datasets spanning hundreds or thousands of rows.
Fortunately, Excel provides multiple easy methods to apply a formula to an entire column in one go. In this comprehensive guide, I will walk you through 10 simple ways to copy formulas down an entire column in Excel.
1. Double Click the Fill Handle
The fill handle is the easiest way to apply a formula to an entire column. Here are the steps:
- Enter the formula in the first cell (e.g. =A2*B2 in C2).
- Move mouse cursor over the small green square (fill handle) at bottom-right of the cell. The cursor changes to a black cross.
- Double click the fill handle. This copies the formula down the column until the last filled cell in adjacent columns.
This method has a limitation that it stops at the last filled cell in columns referred in the formula. To apply till the end, try dragging the fill handle as explained next.
2. Drag the Fill Handle Down
You can manually drag the fill handle down to copy formulas till the very last row, Here is how to do it
- Enter formula and hover over fill handle as before.
- Click and hold down left mouse button and drag down across the column.
- Release mouse button when selection reaches the end.
This tricks Excel into continuing beyond blank cells. The limitation is you have to manually select entire column.
3. Use the Fill Down Option in Ribbon
Excel also provides a Fill Down button to quickly apply formula to a pre-selected column. Here are the steps:
- Select the entire column where you want to apply formula.
- Enter formula in the first cell.
- Go to Home tab > Editing group > Find Fill option > Click on Fill Down.
This fills the formula in all selected cells in one click. You must manually select the column first.
4. Use Fill Down Keyboard Shortcut
You can also use the keyboard shortcut Control + D to quickly fill formula down a pre-selected column.
Simply select the column, input formula in first cell, and press Ctrl + D together to apply formula in all cells.
5. Copy and Paste Formula
A simple alternative is to copy and paste the formula. Here are the steps:
- Enter formula in first cell and hit Enter.
- Copy this cell (CTRL + C).
- Select entire column cells.
- Paste (CTRL + V) to fill formula in selection.
This method lets you paste just formulas without formatting. When pasting, right click and select Formulas under Paste Special option.
6. Add Fill Down to Quick Access Toolbar
For frequent use, add the Fill Down button to Quick Access Toolbar.
Click the dropdown next to Fill Down > Select Add to Quick Access Toolbar. The icon will now always be visible.
7. Use Formula Autofill Option in Tables
Convert your data into an Excel Table (CTRL + T) and turn on Formula Autofill option to automatically fill formulas down a column.
With table formatted data, Excel will populate the column with formula entered in any cell. This works like magic!
8. Fill Formula via AutoCorrect Options
In Excel tables, you can also find AutoCorrect Options next to cells after entering formula. Click the AutoCorrect Options icon and select Fill to apply formula across the column.
This feature automatically identifies columns in tables and provides assisted filling.
9. Utilize Array Formulas
Modern dynamic arrays in latest Excel versions allow spilling array formula results. Enter formula as ={formula} and press CTRL + SHIFT + ENTER to fill and spill array formula down column.
Limitation is formula should calculate same rows as column and not reference other sheets or workbook data.
10. Run a VBA Macro to Fill Column
You can use a VBA macro to select column, find first formula cell, and fill down automatically. This automates the entire process without any manual intervention.
Being programmatic, VBA gives more control and customization ability compared to in-built Excel options.
Do share any other creative ways you use to copy formulas down columns in Excel. Automating repetitive tasks is crucial to working faster and boosting productivity. Excel’s powerful features like fill handle, array formulas and VBA macros make it easy to repeat formula application across columns.
By Double-Clicking on the AutoFill Handle
One of the easiest ways to apply a formula to an entire column is by using this simple mouse double-click trick.
Suppose you have the dataset as shown below, where want to calculate the commission for each sales rep in Column C (where the commission would be 15% of the sale value in column B).
The formula for this would be:
Below is the way to apply this formula to the entire column C:
- In cell A2, enter the formula: =B2*15%
- With the cell selected, you will see a small green square at the bottom-right part of the selection.
- Place the cursor over the small green square. You will notice that the cursor changes to a plus sign (this is called the autofill handle)
- Double click the left mouse key
The above steps would automatically fill the entire column till the cell where you have the data in the adjacent column. In our example, the formula would be applied till cell C15For this to work, there shouldn’t be data in the adjacent column and there should not be any blank cells in it. If, for example, there is a blank cell in column B (say cell B6), then this auto-fill double click would only apply the formula till cell C5
When you use the autofill handle to apply the formula to the entire column, it’s equivalent to copy-pasting the formula manually. This means that the cell reference in the formula would change accordingly.
For example, if it’s an absolute reference, it would remain as is while the formula is applied to the column, add if it’s a relative reference, then it would change as the formula is applied to the cells below.
Using the Fill Down Option (it’s in the ribbon)
Another way to apply a formula to the entire column is by using the fill down option in the ribbon.
For this method to work, you first need to select the cells in the column where you want to have the formula.
Below are the steps to use the fill down method:
- In cell A2, enter the formula: =B2*15%
- Select all the cells in which you want to apply the formula (including cell C2)
- Click the Home tab
- In the editing group, click on the Fill icon
- Click on ‘Fill down’
The above steps would take the formula from cell C2 and fill it in all the selected cells
Apply a Formula to an Entire Column in Excel
How do I apply a formula to a column in Excel?
Another quick and well-known method of applying a formula to the entire column (or selected cells in the entire column) is to simply copy the cell that has the formula and paste it over those cells in the column where you need that formula. Below are the steps to do this:
How to fill a column with a select formula in Excel?
Another quick and easy way to fill the whole column with a select formula is the copy-and-paste method. 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. Now, press Ctrl + C to copy the cell.
How to fill a cell with a formula in Excel?
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. Alternatively, press Ctrl + Shift + Down Arrow to highlight all the cells of the column. Now, press Ctrl + D to fill the cells with the formula in the top cell of the column.
How to fill a column in Excel?
Excel also has an in-built button to help the application of a formula across an entire column. We call it the Fill Down Button and it’s located in the ribbon. Step 1) Write up your formula for the first cell. Step 2) Select the range of cells to which you want the formula applied. Step 3) Go to the Home Tab > Editing Group > Fill.
