Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Dragging Formulas

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I am worked with the formula, when I drag the formula to expand to the next row it automatically increases the undefined values: A4, D3, and D589.

=(VLOOKUP(A4,'Raw Data'!A:D,4,FALSE)/SUM('Raw Data'!D3:D589)*100)

I am working with roughly 4000 lines of data. In each line I need the first cell value ( A4) to increase as I drag the formula down, which is does automatically.

But in the next part of the larger formula /SUM('Raw Data'!D3:D589)*100) I need D3:D589 to remain the same through out the entire column.

Is their any way to lock the second part of the formula so it does not increase as I drag the formula down?

View Answers     

Similar Excel Tutorials

Show All Formulas in a Worksheet in Excel
Display all formulas instead of their output values. This allows you to quickly troubleshoot issues in a worksheet ...
Display all Formulas at Once in Excel
How to view all of the formulas at once in Excel so that you can troubleshoot the spreadsheet quickly and easily. S ...
Stop Formula Calculation in Excel - Increase Worksheet Performance
I'll show you how to stop formulas and functions from performing their calculations and updating in Excel. This ma ...
Highlight Cells which Contain Formulas
This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highl ...

Helpful Excel Macros

Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means

Similar Topics

I have a question on formulas and dragging formulas to automatically populate cells with the same formula.

I'm using Excel 2003 on a Windows XP system.

I have a formula in one worksheet(tab) that references a cell in another worksheet. I would like to drag the formula down in the first worksheet so that it automatically populates the cells below or next to it with the same formula. Traditionally, Excel will just increase all the references cells by one cell. So, for instance if your formula is "=100+A1", when you drag it down the formula will become "=100+A2". What happens if you don't want it to always increase by "1"? What if you want it to skip cells or rows, so that your formulas become "=100+A1", "=100+A4", "100+A7"? In this example, the formulas increase by 3 each time.

And an even more advanced question is, what if you wan the formulas to swich sheets? For example, "=100+'Shee1"!A1", "=100+'Sheet2'!A2", and so on?

Is there a way to do this?


I am hoping this will be an easy one for you Excel experts. I have a simple formula that works and does everything that it is supposed to and I want to drag it down and have the VLOOKUP reference increase as I do. I have about 100 rows and and 11 instances of this formula in each row and within each formula are 3 numbers I am currently changing manually. Is there a way to have the example below increase as I copy and drag the rows down? Any assistance would be greatly appreciated.

I want the Copier1, Imaging1, Computer1 reference in the VLOOKUP to increase as I drag the rows down.





If I have a formula in cell A1 = B$2.

Now I want to select cell A1 and drag it down. So that cell A2 = C$2, and A3 = D$2. (I.e. the column increases by 1.)

If I use the drag feature it just repeats the B$2 when i want the B to increase by one with each cell I go down.

Any Ideas?

Hi once again

I actually have been facing this issue for quite some time and hav'nt taken the time
to get my question out there on this. So here it is, Hope there's a solution...

Is it possible, or how do you, drag a particular formula and have the increments
focused on ONE data you want accumulated (Increased)? I know this sounds
confusing, but it is hard to explain, so here an example of what I'm asking:

I have the need to drag down a formula 1000 rows, but I only want the
"Sheet#" (worksheets) incremented as I drag, but I want the cell "B2" to remain
constant... The formula looks like this: =Sheet1!B2 ... I want to drag this
formula down so it resembles this:


on and on....

When I drag I get increments in the cell B2 only:



is there a way to tell the drag to increase the sheets only without incrementing
the cell B2?

This has boggled me for sometime now, I hope it's possible

Thank You For Any Help On This...

I wasn't quite sure how to word this question for a google search, so hopefully someone here can help me.

Is there a way to drag sideways to fill cells with a formula, but instead of the cell reference increasing sideways, it increases vertically?

For example, if I have =B1+A1 in a cell, can I drag it across to fill the adjacent cell, but have the formula increase to =C1+A2, instead of C1+B1??

I am trying to create an excel sheet with a formula for dates on it. I have it all set up OK if you enter a date directly into the cell. I am looking to be able to drag cells into the cell the formula is using, then be able to drag out of that cell again, without the formula changing. Is this possible?

So, for example, the formula is,
"=DAYS360(G4,E7)" As it is now, if I were to drag a date into cell E7, it would work fine. But if I drag that cell, to F7, then the formula automatically becomes "=DAYS360(G4,F7)". Is there a way to lock the formula in as, "=DAYS360(G4,E7)"

Thanks for the help!
You can download the file for more information at,

I have a formula that needs to be entered 400 times and was looking for an easier way to do it than having to type it in every time. I was hoping there was a formula that I could just drag down.

In cell AA1 i have the formula =SUM(C12:C14)
In cell AA2 I have the formula =SUM(C27:C29)
In cell AA3 I have the formula =SUM(C42:C44)

When I try to drag the formulas down, cell AA4 populates with =SUM(C15:C17), but I need IT to increase from the previous cell by 15 rows. It should say =SUM(C57:C59)... etc. for 400 rows.

Any suggestions?


I have a list of data and I am using a vlookup forlmula to auto populate 4 fields. However when I drag the formula down the sheet, it carries exactly the same data as for the first row. Even though it shows that the cell number has changed to the next row.

I have vlookup(G4,CALLDRIVER,13,FALSE) which works in row 4 - shows: 11:37am. When I drag the formula to row 5, the formula shows = vlookup(G5,CALLDRIVER,13, FALSE) But it shows the data from row 4 - shows 11:37.

Can anybody assist please?


I hope someone can help.

I Have a number of identical worksheets all with mulitple formulas contained within and i am looking to create a summary sheet i have used the $ sign to stop the rows increasing as i drag and the formula but i cant get the sheets to increase by an increment of 1 i.e.

formula in A1: ='Sheet1'$B$3 when i drag to the right i want the formula to show ='Sheet2'$B$3 constantly increasing the sheet only for every cell right i drag.

Is this even possible, i've been going round in circles for hours!!

Many thanks in advance


Hello friends,

i have a data s in two columns A and B the no of the rows that contains the data in that column are dynamic, everyday it will change. i have a formula in column c. whenever my row of column A increases or decreases i need to either drag the formula down or delete the formula in the cell.
i need a macro or a formula so that when there i s a data present in column A the formula in C is automatically copied from above or if the data is not present in A then it will delete the formulas in C.

Please Help. thanks.

Okay, here is what is happening.

In cell C2 i have the formula =IF(A2>"",1,"")
In cell C3 i have the formula =IF(A3>"",2,"")
In cell C4 i have the formula =IF(A4>"",3,"")

I wanted to drag these formulas all the way down to cell C250 or so, but when i drag i end up with:

C5 has the formula =IF(A5>"",1,"")
C6 has the formula =IF(A6>"",2,"")

As you see it doesnt copy the formula correctly, as they should not both say "1" and "2" at the end, but "4" and "5".

Why is the formula dragging doing this, or am i doing something wrong? I selected C2:C4 when i did the drag.


I am working on a large spreadsheet where data needs to be appended on a weekly basis. So, I converted the data to a table (CTRL+T) and now if I add values, it just appends to the table. So far, so good.

Now, in another sheet, I have referenced this data in a sumproduct formula like this:

=SUMPRODUCT((Basedata[[#All],[Account Manager]]=$B$3)*(Basedata[[#All],[ Billablity_Status ]]=$D3)*(Basedata[[#All],[Month]]=E$2))

This gives me the exact required count. Again, it is good. Lastly, I try to drag this formula for all 12 months and the trouble starts.
The formula jumps off referencing the next column (next named range). For example, [Account Manager] column is Column 'AA'. When I drag the formula to the right, to next cell, I want it to remain 'AA', but it turns out to be column 'AB'.

So, the formula becomes :

=SUMPRODUCT((Basedata[[#All],[Month]]=$B$3)*(Basedata[[#All],[ Non Billable Details ]]=$D3)*(Basedata[[#All],[ Category ]]=F$2))

If I were using a formula, I can lock the formula using "$". But that does not seem to work in named ranges. Can someone tell me how should I lock this range so that it does not jump to the next column?

Thanks for all the suggestions and help...

I am using the following formula


I need to drag the formula box down to cover a couple thousand cells, but when I do the (Sheet2!$A1:$B$6) part increases as well. Is there something I can put in this to make the B2 value increase (to B3, B4, etc..) but leave my A1:B6 the same as I extend the forumla to my other cells

When I drag formula down, it will copy the value of the cell, even through
the values of the formula get updated.
What I mean, if I were to drag the formula in 1A down to 3A, it would result
Comumn A Column B Column C
Row 1 3 (formula is 1B+1C) 1 2
Row 2 3 (formula is 2B+2C) 3 4
Row 3 3 (formula is 3B+3C) 5 6

So the formulas are correct, yet the valus are wrong. It will work if I
clear the contents and retype them, or if I right click on the first cell,
drag and click "Fill Formats". This just started yesterday, on two different
worksheets (no viruses). Anyone???

Hi, I was wondering if someone could help me with a little problem I'm having.

I'm still using Excel 2002 (works for what I need it to do).

I need to drag values, without the formula following those values. Essentially, I need to lock a formula to specific cells.

Ex: Cells A1-A4 have random numbers, A5 has the following formula: =sum(A1:A4), now I drag A1-A4 over to B1-B4, leaving A1-A4 blank. After the drag and drop, the formula in A5 follows those numbers to the new cells. However, I want the formula in A5 to stay locked to A1-A4.

How do I keep the formula in A5 from following the reference after a drag and drop? The "$" tag doesn't work for this, I've tried.


If I am creating a vlookup forumulae, and I want to return values from several columns, I have been manually changing the column it's returning the value from when I drag the formula across the columns. This is ok for a few columns but a pain for lots.

eg =VLOOKUP($D2,$AB:$AJ,1,FALSE), dragged across columns, and then manually edited in each column to read

The value doesn't increase when you drag it.

is there a short cut for doing this automatically?

I have some data in a spreadsheet that I'm trying to manipulate and I'm not sure if what I want to do is possible.

I have data in B2: D2 and I have data in E2:E4. So one set of data goes across the columns and the other goes down the rows. I want to subtract these two sets of data. I click on a new cell (B21) and put =B2-E2. That works fine. However when I drag the formula to populate the cells is where my problem starts.

I want to drag the formula across the columns to populate B21:P21. The problem is that when I drag across the columns B2 increments to C2, D2 etc...I would like it to instead increment to B3, B4 etc...

Is is possible to drag a formula in one direction (whether across the page or down the page) and have the cells used in the formula increment in the opposite direction?

I hope that was explained well enough for someone to figure out what I mean. I attached a file as well so you might be able to see what I'm talking about. Thanks

I have values in columns "A1:Z1", on shhet1 and on another sheet I want to refer to these column values in rows for e.g I want column A1 value in cell B1 and column B1 value in B2 and so on. I can't drag the formula down, I have to give column name seprately everytime I drag the formula? Is there a way we can achieve this by dragging the formula down?

I'm trying to have a cell show exactly the same data as a cell in another spreadsheet. I accomplished this by throwing in = and the clicking on the cell in question. Works great. I want to be able to drag the formula

='[StaffingReport.xlsm]Staff & PH Data'!$A$35

have it populate the next cell with the next cell from the other spreadsheet so that the formula would automatically read:

='[StaffingReport.xlsm]Staff & PH Data'!$B$35

Only when I drag, it's dragging the exact same formula instead of going up one column.

Does that make sense?

I have both vertical and horizontal data on a worksheet that is evaluating for two variables from another worksheet to SUMIF. My formula is calculating correctly when I drag down OR if I drag across (as I am freezing rows/columns with $ to indicate the constants). The problem is that it has 100+ rows and 100+ columns and I cannot figure out how to drag the formula across without entering the formula in each of the rows or each of the columns. Is there a faster way to drag a SUMIFS formula??
Help is much appreciated!!

I have this formula in Sheet2 cell A1: = 'Sheet1'!A$1

When I drag down 3 cells I get these formulas:


But I would like to see these formulas:


Basically, when I drag down and copy my data, I would like the numbers to remain constant but the letters to increase by 1. Say from A --> B --> C, etc.

How can I accomplish this?

I want A1 (for example) to always be multiplied with a list of #'s in column
B with the results in column C. Is that clear? A1 should be multiplied by B1
thu B20 with the sum showing up in C1 thru C20. Currently my settings are
allowing the first part of the formula (A1) to increase to one cell,
A2,A3,A4, and so on as you drag. Please Help????

I am in the process of creating a fully automated Excel file for an accounting report that we process monthly (with all the macro bells and whistles). But, I have run into a problem. I have two worksheets in my workbook. One contains the data that feeds in from our ledger, the other is the formal report that we print (and where I have my formulas set up). The formulas are quite simple (add & subtract) and are linked to the data from the corresponding month on the data ledger tab. That's the set up, here's the problem:

The formula tab is set to drag down one row at a time, while the data tab pulls in three rows for each month (which may not be removed). I need to make it so that when I drag my formula down one row, it captures the corresponding data three rows down. For example: month of July-09 is in row 1 on the formula tab, and row 3 on the data tab. My formula is set to grab the data from row 3 in the data tab. Now the month of August-09 gets dragged down to row 2 on formula tab, and row 6 is populated on the data tab. My formula will automatically grab the data from row 4, because that is the next row in the series. Make sense?

How can I make it so that when I drag my formula down by one row, the formula knows to grab the data three rows down from the next worksheet???

Thank you!!!

I have a spreadsheet that I did not make that pulls data from one I did make.

My spreadsheet has dates and data
The dates start in A1 and increase down the rows.

In the spreadsheet I didn't make, the dates start in A1 and increase across the rows

This makes pulling data from mine into this other person's spreadsheet a little time consuming because when I drag let's say the formula that pulls info from my sheet in

cell AC35(=mysheet!B599) over to cell AD36, I have to go into the formula and manually change it.

As is, when I drage the formula over to cell AD36, the formula would read (=mysheet!C599)

when I want it to read (=mysheet!B600)

Is there a way to selectively have cells I choose autofill the formula
the opposite it normally does?

Instead of increasing the Row by 1, it would increase the Column by 1?

Hi ,

I am facing a problem in Using Drag & Drop formula in Excel 2003 recently.

While I put a formula in one cell Say B1 w.r.t. the refrenec in cell A1, I get the results. But when I drag the formula in B1 to successive cells in column B w.r.t. the corresponding refrences in column A, the formula does not work & the value in cell B1 is simply copied in each cell.

I have been using this from the past several months but now it is not working. I have checked the Options:Edit:Drag & Drop(Yes). But nothing is working.

Since I have a lot of data , this functionality is of great help to me.

What Should I do, Pl. help