How to Drag a Formula Down in Excel (Fastest Way)

One big reason why I and many other Excel users love Microsoft Excel is the “Drag and drop” feature of Excel.

You type a formula for one cell, drag the fill handle, and Excel automatically updates and populates the same for literally tens of thousands of rows (or even more)

Isn’t that just so cool? 😎

To your surprise, there’s not only one way how you can do it in Excel but many. And in this tutorial, I am going to show you different ways how you can drag a formula in Excel.

Plus, many tricks too that will help different unique circumstances where you need special tricks to use the drag feature of Excel. Get the practice workbook for this guide here and tag along with me.

Table of Contents

Drag a Formula by using the AutoFill Handle

The most basic and easiest method to drag a formula in Excel is to use the Auto-Fill Handle. And there are two possible ways how you can do it.

Let me stipulate some numbers in Excel as an example 📌

Take it as your grocery shopping bill where we have a list of items purchased, their prices, and the quantity purchased. Let’s now quickly do the bill.

For the first cell, here’s the formula you type:

Price of each item * Quantity purchased. Simple.

Now, to find it for all the remaining items on the list, you can use the Auto-Fill handle in two ways👇

Double-clicking it

To drag a formula across the whole column by double-clicking:

Step 1) Hover your cursor around the right bottom of the cell that contains the formula to see a small black plus sign ➕

Step 2) Once you see it, double-click on it.

There you go! Excel automatically copies the formula (with updated relative cell references) for all the cells in the column.

However, there’s a catch. Just if your data looked like this instead.

If you try to drag the formula by double-clicking the plus icon for such data, this is what you get.

Excel will drag the formula down up to the cell where there is any data in the adjacent columns.

Excel will drag the formula down up to the last cell where there is any data in the adjacent columns. As soon as there comes a gap in between, the autofill stops there.

So, you’d have to double-click the plus icon multiple times for such a dataset. Or you can help this issue by dragging the formula down.

See the below section.

Dragging it

You can also auto-fill formulas in Excel by dragging them down manually.

Let’s go with the same dataset as above that had a gap in it.

Step 1) Hover your cursor around the lower right corner of the cell that contains the formula to see the small black plus sign (the Fill handle)

Step 2) Once you see it, hold and drag the fill handle down up till the cell until where you want the formula dragged.

In this method, we manually drag the formula so that even if there are any empty rows/columns in between, they will be covered, too.

Also, a method not only works for dragging the formula to the bottom but also towards right/left and top.

For example, if the above data looked like this instead:

I could still drag the formula toward the right to have the results auto-filled.

This however cannot be done by double-clicking the fill handle 🤔

Auto-fill the formula but not the formatting

At times, you might only want to auto-fill the cells with the formula but not with the formatting.

The auto-fill handle of Excel allows you to do so. Look here.

Step 1) Once you have dragged the fill handle (or double-click it) to have the formula auto-filled, release the fill handle.

You’ll see the Auto Fill Options icon here. Click on the small drop-down menu icon next to it.

Step 2) From the menu of options that comes then, select the option to Fill Without Formatting.

Excel will instantly remove the formatting from the cells where the formula was dragged 🧹

Drag a Formula by using the Fill Down Option

To use the Fill Down option in Excel for dragging a formula:

Step 1) Select the source cell (D2 that contains the formula) and the target cells (up to Cell D7).

Step 2) Go to the Home tab > Editing Group > Fill Options > Down.

Excel will drag the formula from the source cell up to the target cell at the bottom

All you need to do is select the source and the target cells and select the right filling option depending on the direction in which your target cells lie.

For example, in the following situation:

I will use the option to Fill Left since my target cells lie toward the left of the source cell.

Excel will drag the formula to the left as below.

The Fill Feature of Excel can be used in any direction (left, right, top, or bottom), and this part makes it my favorite 🤩

Drag a Formula by using the Keyboard shortcut

Excel is super friendly in terms of keyboard shortcuts. You can always drag and drop formulas in Excel without having to reach out for the mouse.

Drag the formula down

To drag the formula down:

Step 1) Select the source cell (D2) and the target cells (up to Cell D7).

Step 2) Press Ctrl + D key.

And you get this.

Excel will drag the formula from the source cell up to the last selected target cell 💡

Drag the formula toward the right

To drag a formula towards the right:

Step 1) Select the source cell (B5) and the target cells (up to Cell F5).

Step 2) Press Ctrl + R key.

Here’s what happens.

Excel will drag the formula from the source cell up to the last selected target cell towards the right.

Use an Array Formula

This is not really formula dragging but gives the same results as you’d expect from dragging a formula in Excel.

Let’s calculate the General Sales tax to be applied on each of these items @ 15% 📜

One way could be that I write the formula B2 * 15% in Cell C2 and then drag it down to the remaining column.

Step 1) In another outlook, we can instead write an array formula as below.

Step 2) Press the Enter key.

Array formulas allow you to perform calculations on arrays of data rather than a single cell. So, instead of writing the formula for a single cell and then dragging it down, play smart by covering it all with a single array formula.

Pro tip

Array formulas work for the dynamic Microsoft Office Suite 365. So, if you’re subscribed to Microsoft 365, perfect.

If not, you can use make the formula work by pressing the combination keys of Ctrl + Enter instead of only Enter.

Troubleshooting – Why is the formula not being dragged

Trying to drag down a formula in Excel but you don’t see the fill handle upon hovering around the right-bottom of a source cell?

That’s probably because you have disabled the fill handle and cell drag and drop. Here’s how you can enable it:

Step 1) Click on the File tab > Options.

Step 2) In the Excel Options dialog box, go to the Advanced tab from the pane on the left.

Step 3) Go to the Editing options section.

Step 4) Check the “Enable fill handle and cell drag-and-drop” option.

Your fill handle will hopefully be back by now 😊

Other reasons why you might not be able to drag and drop formulas in Excel might be:

• Your worksheet is protected, and dragging formulas is restricted. Unprotect the worksheet to be able to drag formulas.
• You might be using an older version of Excel (or an older format file). Try saving the file in the latest Excel format (.xlsx) to see if it works.

Conclusion

These are some ways to drag and drop a formula in Excel. All of these methods come with their practical examples and pros and cons so you can decide which method best suits you.

Honestly, the best method will be different for different tasks as the nature and needs of your task will decide what’s best for you.

More guidance on how to make the best use of Excel Fill Handle and Auto Fill Feature comes in my Excel tutorials below:

• Excel Guide: Clicking and Dragging the Fill Handle (2024)
• How to Fix “Formula Omits Adjacent Cells” Excel Error (2024)
• How to Use Flash Fill and Autofill in Excel Step-By-Step

Do check them out to continue learning from the best.