Formulas are important in Excel spreadsheets. Excel formulas come in handy when dealing with large data sets and calculations. Sometimes you may need to drag formulas to different consecutive cells or even an entire column without having to retype it. This can easily be achieved using Excel built-in features with a few clicks or a keyboard shortcut.
Below, we give you the different ways you can use to drag formulas in Excel when working with spreadsheets. Let’s have a look at some of these methods.
Drag formulas till the end in Excel using the fill handle
1. Start by selecting the cell you wish to fill to the end.
2. Drag the fill icon down or side ways to your preferred cells . The fill handle is usually at the bottom right corner of the selected cell. Dragging the fill handle will fill the cells with a copy of the original formula used.
Note:
Using the AutoFill handle will automatically calculate the formula for you. You can enable the feature by clicking on the Formulas tab > Calculation Options > Automatic.
Drag formulas till then by Double-clicking the autofill handle
This method is preferable when you are working with a large worksheet such that using the fill handle can be tiresome.
1. You can also drag an Excel formula to the end by double-clicking the autofill handle. All you need to do is first enter your formula to the first empty cell in a column or row.
2. Select the cell to display a small green square at the bottom-right corner.
3. Place your cursor here to display the autofill handle plus sign icon.
4. Double-click the left key of your mouse to automatically fill up the adjacent cells in the column or row.
Note:
When using this method, Excel will automatically copy down your formula as far as the data goes. In places where you have row headers or any other data in place, Excel continues to copy the original formula to the left of the fill handle.
Using the Fill command to drag formulas in Excel till the end
1. First, select the cell with the formula and the adjacent cells or cells below that you want to fill.
2. On the main menu ribbon, click on the Home
3. Go to the Editing group and click on the Fill
4. On the displayed drop-down list choose either Down, Right, Up, or
Using Shortcuts
You can also use the keyboard shortcut Ctrl + D to fill your formula down the column, or Ctrl + R to fill your formula to the right in a row.
Using Copy and Paste
1. Start by typing in your formula in the top empty cell.
2. Press Ctrl + C or click the Copy button in the Home tab of the main menu ribbon.
3. Select all the cells you wish to apply your formula to.
4. Press Ctrl + V on your keyboard or the Paste button on the Home
Similar Read: How to Remove Spaces before Numbers in Excel