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

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:

Click to copy

Syntax Highlighter

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➕

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 iconfor 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:

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.

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).

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

For example, in the following situation:

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).

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).

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.

Click to copy

Syntax Highlighter