Fill Formula Down an Entire Column in Excel (Easiest Way in 2024)
In this article, we will learn how to fill a whole column with formula in Excel by double-clicking the auto-fill handle, dragging the auto-fill handle, using the fill-down option, and using a hotkey. Simply follow the steps below.
Method 1: Double-Click Auto-Fill Handle to Auto-Fill Entire Column in Excel
Follow the steps below to auto-fill an entire column in Excel with formula by double-clicking the auto-fill handle.
1. Enter the Formula
Imagine we have a dataset with sales data, where Column D is where we want to calculate Total Sales. We will use the formula =B2*C2 (Unit Price x Units Sold) to calculate the sales for each product. Select the first cell where you want to apply the formula. For this example, we will enter the formula in cell D2.
2. Locate the Auto-Fill Handle
After entering the formula, you will see a small square at the bottom-right corner of cell D2. This is the auto-fill handle.
3. Double-Click the Auto-Fill Handle
Double-click this handle. Excel will automatically fill the formula down the entire column as long as there is data in the adjacent column (Column C in this case).
Method 2: Drag Auto-Fill Handle to Auto-Fill Entire Column in Excel
Follow the steps below to auto-fill an entire column in Excel with formula by dragging the auto-fill handle.
1. Enter the Formula
Select the first cell where you want to apply the formula. Again, we will enter the formula in cell D2.
2. Locate the Auto-Fill Handle
Locate the small square at the bottom-right corner of cell D2.
3. Drag the Handle Down
Click and hold the auto-fill handle, then drag it down to cover the cells where you want to apply the formula (e.g., from D2 to D6).
Method 3: Use Fill-Down Option to Auto-Fill Entire Column in Excel
Follow the steps below to auto-fill an entire column in Excel with formula by using the fill-down option.
1. Enter the Formula
Select the first cell where you want to apply the formula. For this example, we will still enter the formula in cell D2.
2. Select the Range
Click on cell D2, then drag down to select all cells in Column D where you want to apply the formula (e.g., from D2 to D6).
3. Fill Down
Go to the Home tab on the Ribbon and click on Fill from the Editing group. From the drop-down menu, select Down. The formula will be filled down the selected range.
Method 4: Use Hotkey to Auto-Fill Entire Column in Excel
Follow the steps below to auto-fill an entire column in Excel with formula by using a keyboard shortcut.
1. Enter the Formula
Select the first cell where you want to apply the formula. For this example, we will still enter the formula in cell D2.
2. Select the Range
Click on cell D2, then press Ctrl + Shift + ↓ to select all the cells in Column D until the end of your data in the adjacent column (e.g., from D2 to D6).
3. Apply the Keyboard Shortcut
Press Ctrl + D. This will fill the formula from the top cell (D2) to the bottom of the selected range.
We hope that you now have a better understanding of how to fill formula down an entire column in Excel. If you enjoyed this article, you might also like our article on excel shortcut to fill color and how to alternate fill colors in excel.
