Excel is essentially a calculation engine. Just like a calculator, it takes questions in the form of formulas, for example, =8+4, and returns answers.
Formulas are essential to manipulating data and extracting useful information from your Excel workbooks. Formulas make Excel roar to life as it were.
Although formulas are powerful tools they need to be used in an efficient manner for maximum benefit.
Suppose you have a dataset that requires the same formula in an entire column. Laboriously entering the formula in one cell at a time wastes time and effort.
This tutorial shows you 7 time-saving techniques for applying a formula at once to an entire column in Excel.
Method #1: Double-click the Fill Handle
The following is a dataset showing the quantities of various items bought and their prices.
We want to apply a formula in column D to calculate the total cost of each line of items bought.
We will enter a formula in cell D2 and double-click the fill handle to copy the formula down the column.
Here are the steps to do this:
- Select cell D2 and type in the following formula:
=B2*C2
- Click the Enter button on the formula bar.
This enters the formula without moving the cell selector.
Notice the little green square in the bottom right corner of cell D2:
That little green square is called the Fill Handle.
Note: If you do not see the fill handle enable it by doing the following then continue with step 3:
- Click the File tab to open the Backstage window.
- Select Options in the left sidebar.
- Select Advanced in the left sidebar of the Excel Options dialog box that appears. Then select Enable Fill handle and cell drag-and-drop and click OK.
- Hover the mouse pointer over the fill handle. It changes to a plus sign:
- Double-click the left mouse button.
The formula is copied down the column and total prices for the various items are returned.
The cell references in the formula are relative and are therefore adjusted accordingly as the formula is copied down the column. You can ascertain this by clicking Show Formulas in the Formula Auditing group on the Formulas tab.
Note: If the cell references in the formula were absolute, meaning they had two dollar signs, for example, =$B$2*$C$2, the formula would be copied down the column as is.
One problem with the technique of double-clicking the fill handle is that if there are blank rows in the dataset, the formula is not copied all the way down the column but only up to the first blank cell in the column as shown below:
If your dataset has blank rows you can first delete them before double-clicking the fill handle or use the following method of dragging down the fill handle.
Also read: Formulas Not Copying Down in Excel – How to Fix!
Method #2: Drag Down the Fill Handle
The following is a dataset showing the quantities of various items bought and their prices. Row 4 of the dataset is blank.
We want to apply a formula in column D to calculate the total cost of each line of items bought.
We will enter a formula in cell D2 and drag down the fill handle to copy the formula down the column.
Below are the steps to do this:
- Select cell D2 and type in the following formula:
=B2*C2
- Press the Enter button on the formula bar to enter the formula.
- Hover the mouse pointer over the fill handle. It changes to a plus sign. Press and hold down the left mouse button and drag the fill handle down to cell D6, then release the button.
Notice the zero (0) in cell D4. You can delete it.
One drawback of this technique is that it can become cumbersome if you are working with a large dataset.
Also read: Excel Showing Formula Instead Of Result
Method #3: Use Copy and Paste
The following is a dataset showing the quantities of various items bought and their prices.
We want to apply a formula in column D to calculate the total cost of each line of items bought.
We will enter a formula in cell D2, copy it, and paste it into the rest of the cells in the column.
We use the following steps:
- Select cell D2 and type in the following formula:
=B2*C2
- Click the Enter button on the formula bar to enter the formula.
- Press Ctrl + C to copy the formula in cell D2. On a Mac use Cmd + C.
Notice the marching ants border around cell D2. This indicates that the formula in the cell is copied to the clipboard and is available for pasting.
- Select range D3:D6.
- Press Ctrl + V to paste the formula in the selected range.
The formula is copied into the range and the total price of each line of the items bought is returned.
Notice that cell D2 still has the marching ants border. Press the Escape key to remove it and clear the clipboard.
Note: If the cell containing the formula has formatting that you do not want to be copied down the column, you can use the Paste Special feature as follows:
- Select and copy the cell containing the formula.
- Select the range into which you want the formula to be copied.
- Press Ctrl + Alt + V to open the Paste Special dialog box. Select the Formulas option and click OK.
Also read: How to Square a Number in Excel
Method #4: Use a Dynamic Array Formula
The following is a dataset showing the quantities of various items bought and their prices.
We want to apply a formula in column D to calculate the total cost of each line of items bought. We will enter a dynamic array formula in cell D2. We will press Enter to automatically propagate the formula in the column.
Note: Dynamic arrays are only available in Office 365 and Office 2022 or later.
We use the following steps:
- Select cell D2 and type in the following formula:
=B2:B6*C2:C6
- Press Enter or click the Enter button on the formula bar.
The formula is propagated to the rest of the cells and the total price of each line of the items bought is returned.
Important information about dynamic array formulas
Notice the blue border around range D2:D6 in our example dataset.
When we entered the dynamic array formula, the results spilled into the range. Excel visually defines a spill range with a solid blue border.
All cells in the spill range are disabled except the cell containing the original formula. This means that none of the values in the spill range except cell D2 can be edited, deleted, or moved in any way.
The spill range must be blank for dynamic arrays to work. Any non-empty cell in the path of the spill range will cause a #SPILL! error.
For example, the following dataset has a value in cell D4.
If we attempt to enter a dynamic array formula in cell D2 we get a #SPILL! error:
Delete the value in cell D4 and the formula propagates without an error:
Method #5: Use the Fill Down Command on the Home Tab
The following is a dataset showing the quantities of various items bought and their prices.
We want to apply a formula in column D to calculate the total cost of each line of items bought. We will enter a formula in cell D2 and use the Fill Down command on the Home tab to copy the formula down the column.
We use the following steps:
- Select cell D2 and type in the following formula:
=B2*C2
- Click the Enter button on the formula bar and select range D2:D6.
- Select the Down option on the Fill drop-down list in the Editing group on the Home tab.
The formula is copied down the column and the total price of each line of items bought is returned.
Method #6: Use a Keyboard Shortcut
The following is a dataset showing the quantities of various items bought and their prices.
We want to apply a formula in column D to calculate the total cost of each line of items bought. We will enter a formula in cell D2 and use the keyboard shortcut Ctrl + D to copy the formula down the column.
We proceed as follows:
- Select cell D2 and type in the following formula
=B2*C2
- Click the Enter button on the formula bar and select range D2:D6.
- Press Ctrl + D.
The formula is copied down the column and the total price of each line of items bought is returned.
Method #7: Use Excel VBA
The following is a dataset showing the quantities of various items bought and their prices.
We want to apply a formula in column D to calculate the total cost of each line of items bought. We will create and use a VBA procedure to copy the formula down the column.
We use the following steps:
- Press Alt + F11 to open the Visual Basic Editor.
- Select Module on the Insert menu.
- Type the following procedure in the module.
Sub EnterFormulaEntireColumn()
Range("D2").Formula = "=B2*C2"
Range("D2").AutoFill Range("D2:D6")
End Sub- Save the procedure and save the workbook as a Macro-Enabled Workbook.
- Place the anywhere in the procedure and press F5 to run the code.
- Press Alt + F11 to switch back to the active worksheet.
The procedure has worked and copied the formula down the column.
Note that cell references are hard-coded in the procedure and we will need to change the cell references if we want to apply the procedure to another dataset.
In this tutorial, we have looked at 7 different techniques for applying a formula to an entire column in Excel. You can use the techniques that work best for you.
Other Excel articles you may also like:
