Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Making Cell References Super-absolute

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

How do I make a cell reference so permanent that even inserting rows or
columns above, below, or within the ranges of the formula does not
affect the reference?

In other words, "=AVERAGE($E$7:$E$11)" is good and well, until I insert
a row of cells in row 7, and then Excel changes my formula to
"=AVERAGE($E$8:$E$12)". I don't want Excel to change my formula.

I'll also be happy to consider other workarounds-- but the people who
will be using this template are technology-illiterate.


View Answers     

Similar Excel Tutorials

Absolute and Relative Cell References in Excel
In this tutorial I am going to cover the difference between Absolute and Relative Cell References in Excel and show ...
Top Excel Keyboard Shortcuts to Increase Productivity
I'll show you the top keyboard shortcuts for Excel that are sure to increase your productivity. These shortcuts are ...
Make Column Headings Numbers instead of Letters - Make R1C1 Style References in Excel 2007
In Microsoft Excel you reference columns as letters by default - A1, B3, C5, etc. But you can also reference the co ...
Prevent Spaces from Being Entered in Text in Excel
How to prevent a user from entering any spaces within text in Excel.  This allows you to keep data clean when a use ...

Helpful Excel Macros

Change Formulas to Absolute or Relative References
- This macro will change the formulas in cells to absolute or relative. It allows you to change the rows or the columns o
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Pop-Up Message Box When a Range of Cells Reaches a Certain Average
- This macro will display a message box when the numbers within a range of cells reaches an average of 5. The current mac
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a

Similar Topics

Hello, I am having trouble copying something with formula. I have a 2 sheets in the same work book. One of them is the weighted average. My formula works wells just when i try to drag it down it does not come out they way i need it too.

This is the formula in C1
= IF('Weighted Average'!M1 = "",A1+B1,IF( 'Weighted Average'!M1>0.001,'Weighted Average'!M1))

On the weighted average page where i'm pulling the value of M1. It has to skips two cell and the next value would be M4.

So cells C1 to C3 would have these formulas in them.

= IF('Weighted Average'!M1 = "",A1+B1,IF( 'Weighted Average'!M1>0.001,'Weighted Average'!M1))
= IF('Weighted Average'!M4 = "",A2+B2,IF( 'Weighted Average'!M4>0.001,'Weighted Average'!M4))
= IF('Weighted Average'!M7 = "",A3+B3,IF( 'Weighted Average'!M7>0.001,'Weighted Average'!M7))

However, here is the problem when i select cells C1 to C3 and drag it down to C400 the sequence is messed up.

Here is how C1 to C6 will look if i drag it down.

= IF('Weighted Average'!M1 = "",A1+B1,IF( 'Weighted Average'!M1>0.001,'Weighted Average'!M1))
= IF('Weighted Average'!M4 = "",A2+B2,IF( 'Weighted Average'!M4>0.001,'Weighted Average'!M4))
= IF('Weighted Average'!M7 = "",A3+B3,IF( 'Weighted Average'!M7>0.001,'Weighted Average'!M7))
= IF('Weighted Average'!M4 = "",A4+B4,IF( 'Weighted Average'!M4>0.001,'Weighted Average'!M4))
= IF('Weighted Average'!M7 = "",A5+B5,IF( 'Weighted Average'!M7>0.001,'Weighted Average'!M7))
= IF('Weighted Average'!M8 = "",A6+B6,IF( 'Weighted Average'!M8>0.001,'Weighted Average'!M8))

As you can see the A and B cells copy just fine in a sequence. But the M cells go out of wack. Is there any way to do this without manually changing each M?
I just want the value to add 3 to every M. As in, M1, M4, M7, M10, M13....

Here is an attachment of what I am trying to do

Is it possible to use "find and replace" command to amend the formula?

Ex. we have the formulas like this:


and we wanted to change them into


There should be some trick to keep the cell references intact while changing the formula only.

I tried replacing "average" with "iferror(average", but Excel does not like that notation. On the other hand, it is possible to replace "average" to "ifferror", but it leaves a lot of work to be done manually.


Hi - I have a problem that I thought would be solved by using absolute references, but it's not. I have a spreadsheet that looks like this:
__A _______B_______C
1 Alberts Alberts =IF(NOT(A1=B1),"different","")
2 Alberts Alberts =IF(NOT(A2=B2),"different","")
3 Brooks Alberts =IF(NOT(A3=B3),"different","")
4 Brooks Brooks =IF(NOT(A4=B4),"different","")
As you would expect, C3 displays "different", all other cells in column C are blank. So far so good. However, what I want to do, now that I've identified the "different" row, is to move A3 down one row, so things look like this:
__A _______B_______C
1 Alberts Alberts =IF(NOT(A1=B1),"different","")
2 Alberts Alberts =IF(NOT(A2=B2),"different","")
3 ______ Alberts =IF(NOT(A3=B3),"different","")
4 Brooks Brooks =IF(NOT(A4=B4),"different","")
5 Brooks etc...........
Please note that I'm doing this because there is other information in each row, not because my OCD is out of control. Also, in my real formula I'm handling cells that are blank. Anyway, my problem is that I don't get the above. Rather, Excel adjust the values in my formula so I get:
__A _______B_______C
1 Alberts Alberts =IF(NOT(A1=B1),"different","")
2 Alberts Alberts =IF(NOT(A2=B2),"different","")
3 ______ Alberts =IF(NOT(A4=B3),"different","")
4 Brooks Brooks =IF(NOT(A5=B4),"different","")
5 Brooks etc...........
This is NOT what I want. I want the formula in C3 to continue to reference A3, not A4. As I understand it, absolute references are only for when you want to copy the cell that they are used in, which I'm not doing. Anyway, I tried saying $A$3, but it didn't help.
How do I stop this behavior?

I have created one row of 737 columns. Basically, i had to convert a well formatted data entry sheet into one row so that it can be uploaded to a work database.

After creating my one row of 737 columns, I now have discovered that I did not make my references absolute. Thus, when I copy row one, the origin of my reference changes to the next row when pasting on the next line.
my first row, where cell F9 is the proper reference: =IF(ISBLANK('ESA Timesheet'!F9), "", 'ESA Timesheet'!F9)

my second (pasted row), where the reference still needs to be F9, or more accurately $F$9: =IF(ISBLANK('ESA Timesheet'!F10), "", 'ESA Timesheet'!F10)

Is there a way i can avoid putting in four $s in each of my 737 cells? all the references are in place, i just need to stop the automatic adjustment to the next row, as a relative reference would allow.

I have some complicated macros which help create quotes for our sales department. I am using a "configurator" workbook which contains a few template sheets and over 50 ranges which point to various variables/templates. In the process of creating the quotes I end up opening/creating a new workbook and copying the template ranges/sheets to the new workbook (through the macro). This new "Quotes" workbook will contain a coversheet with many variables containing named ranges which subsequent sheets will need to reference with "=NAMED_RANGE" or something like that.

Right now excel is annoying me because when it copies references of named ranges from one workbook to another, the cell tries to refer back to the "configuration" workbook (although it doesn't say so). I want to be able to use something like "=ThisWorkbook!NAMED_RANGE" which would solve all my problems but it looks like MS isn't going to make it that easy for me (I can't find any info on a special reference like ThisWorkbook for formulas).

The only workaround I could think of would be to create a macro to scan through all the cells with references to named ranges in the new "Quote" workbook and insert the new workbook name in front of them (i.e. =quote1.xls!NAMED_RANGE). This would take forever and then I would have problems if the user renamed the workbook.

Anyone have any suggestions? Please I'm dying here.

Hi all,

I'm going to give this another shot and try to state it a different way.

This is what I have so far:


What this is stating is I want to average the absolute value of cells B16:L16 only if the cell contains a number. The good thing is that this works; however, you see in the bold part that it is only referring to TAB 1 in the Jul06AMEFVER.xls workbook.

My problem is that I want to take the average of all absolute values of cells B16:L16 on TAB 1 through TAB 31. I tried this:


But the answer is "0". I checked when the data isn't filled in there is an "X" so that is why the ISNUMBER function is there. I would like the formula to do an ongoing average while the data is being filled in. If the referenced cell (aka B16:L16) as an "X" or blank space don't count it in the average.

I hope you all understand what I'm trying to do. Been working on this for two days now and running out of ideas. Thoughts. Thanks so much.


Hello, I've looked all over the net trying to find a solution, but here I am. Your help is appreciated.

I am in need a formula (for "'Sheet1'") to find the smallest number in a range of cells from ("'Sheet2'"). But, I also need that formula reference to the cell range on "'Sheet2'" to be absolute so that I can insert colums in "'Sheet2'"without the formula automatically updating which column it references.

The problem is that I need to be able to auto fill the formula on "'Sheet1'".

I've been researching, guessing, and testing for 4 hours now and here
Below is the closest thing to my desired formula that I've gotten to work.


But, when I auto fill, it comes out like this:

=SMALL(INDIRECT("'Sheet2'!$J4:$N4"&ROWS('Sheet2'!$J6:$N6)),1).....and so on

I need it to come out in a way that the column reference is absolute, but the row changes (preferably based on the row that the formula is in)

Please, help me.

I'm trying to get a formula working that is basically for a rating chart. The chart is somewhat simple, but the formula is a bit complex (for me anyway)

So the details are the rating is on three axis with three levels, and here is what I need to do.

At the moment the formula below works, but it isn't 100% accurate because I actually need to take an average of F5+G5 then take that value and add it to H5 then divide by two to get the proper average of the two charts. (if that makes sense.


Here is the formula that I thought would have worked, but Excel begs to differ


I played with the brackets quite a bit thinking I had something in the wrong order, but nothing seems to work. I always get FALSE in the field and an error as well when I enter the formula.

Any help would be much appreciated!


Good day,

I have a worksheet that has a large number of columns (based on the day of the week) and a large number of rows (based on the status of certain application checks).

In every other column I have the following formula: IF(ISNUMBER(SEARCH(TEXT(O1, "dddd"), 'Client Backup Set Up'!$C3)), "TRUE", "")

I want to fill the rows below it, in the same column, with the same formula but only changing the second cell reference (if that's the correct terminology).

Let me explain with an example.

If I drag the formula down the row, I get the following results.

Please Login or Register  to view this content.

However, what I want to see is this ...

Please Login or Register  to view this content.

As you can see, the first reference to a cell doesn't change. It stays as O1 in every row. The second reference changes by one in each new row (C3, C4, C5 etc.).

Is it possible to restrict certain cell references so that they don't change by adding a character to the formula or by any other means?


I have been making a mistake by not including $ in my formula for absolute reference. Now, i could like to amend this mistake. Is there a quick way of doing it?

Basically, i have 24 columns and 400+ rows for 4 worksheets each to amend. Hence, i am looking for a short cut rather than just to press "F4" button for each reference.

Thanks for the help in advance.

Here is the formula I am trying to write. looking for an average of a range of 3 cells that contain averages of other cells where the answer is DIV O, here is my formula.

=IF(J10:J12>0),SUM(J10:J12)/COUNTIF(J10:J12)>"0",0), I know there has to be a way to make this work.

J10 -J12 give and average of individual ranges using this formula "=SUM(D2:D32)/COUNTIF(D2:D32,">0")"

J10 has a number, J11 and J12 have "DIV 0" because no data has occurred to complete J11 and J12

trying to return 0 instead of DIV "0"

Hi, just a quick question really, I have a feeling it can probably be resolved quite quickly but I just don't know how.

Basically, I have a formula containing absolute cell references, but whenever I insert a new row into the table that the reference is looking at, the cells bound by the absolute reference change.

I thought the point of using absolute cell references is that they don't change?

Anyway, is there some way around this, to avoid the cell reference changing when a row is inserted?

Thanks in advance for any help.

Hi. I'm getting a Run-time 1004 error when trying to insert 3 blank lines (it pops up at the first "Insert" line). Excel is complaining about shifting non-blank cells off the page, but doing "CTRL-END" highlights cell D198, which is the extent of my data, so I don't understand the problem. The step before this was copying a pivot table, pasting as values, then pasting as formats. Any help on fixing this would be greatly appreciated.

Thanks, John


Sub Trouble()
    'Plug in the formula to figure the average monthly change
    With Sheets("Paste")
        Range("A1").EntireRow.Insert Shift:=xlDown
        Range("A1").EntireRow.Insert Shift:=xlDown
        Range("A1").EntireRow.Insert Shift:=xlDown
        Columns("A:A").Insert Shift:=xlToRight
        Range("A1").Value = "Date Columns Divisor"
        Range("A5").Value = "Average Monthly Change"
        Range("B1").Name = "Months"
        Range("B1").FormulaR1C1 = "=Counta(5:5)-5"
    Range("IV3").End(xlToLeft).Offset(0, 1).FormulaR1C1 = "=(-D6 + Offset(D6, 0, $B$1)) / Months"
    End With
End Sub

Hi everyone!

I need a formula that is able to calculate the average of every 5th row (sort of in 5 row blocks), so long as they are aligned with another row. It seems strange, but I need it to fit the specification of some MATLAB output. In the event that there's a blank, it should just ignore it and not change the counting in the blocks. However, if a new ID is detected in A, the counting needs to start over.

For example, I have Columns A and B, but need the average in C

1 ID #
2 1_01a x
3 x
4 x
5 x
6 x [average]
7 x
8 x
9 x
11 x [average]
12 x
13 x
14 x
15 x [average]
16 1_02_b
17 x
18 x
19 x
20 x
21 x [average]

I've tried some different things with average ifs, but I can't get excel to count the rows. Any help from anyone would be super, super, appreciated. Thanks!

Good day all,

I am working on an excel table in my worksheet contains formulas and locked cells. lets say table range is from column "a" to "f" and there is formula on cell in columns "c" and "f". I just locked cells in column "c" and "f" to prevent people changing the formula accidentally. but when the sheet is protected i am not able to insert or delete rows as the rows has locked cells in columns "c" and "f".

I tried the below macro to unlock the sheet, insert row and lock the sheet again:

Please Login or Register  to view this content.

So by running the macro it asks the password and do the job but the issue is it needs password and i do not want people to know my password. I don't want them able to unlock the workbook and make any undesired changes. I want them to be allowed insert or delete rows and input data in unlocked cell. I want the sheet to be locked all the time and only when people want to insert or delete a row it be unlocked and be locked again after inserting or deleting whit no need to insert password in this case only.

Best regards.


I have a formula with circular reference. When a I put any valur in any of
these reference cells the formaula gets overwritten by the value. Is there
any way that I can put values in any of these cells without overwriting the
formuls to try various permutations. Below is the example:-

Cell A B C
500 10 2%
Formula +C1*A1 B1/A1

Cell B1 & C1 are having formula with cicular reference. What I am trying to
get is that if I change the B1 value to 50 from 10, then C1 should
automatically change to 10% & vice versa. OR If C1 is changed to 10% B1
should change to 50 so that I can try as many combinationas possible.

The problem here is that once i put "50" in B1 or "10%" in C1 the formula
gets overwritten with the absolute value.

Would be thank ful if anybody can suggest any solution for this?


I hope you can help with my problem.
I have a range of cells I want to average, the contents of those sells are :


Those formulas give me a result of either, 1, 0, or an empty cell.

The formula for the Average calc. I am using is :


The result of this is :


Any sugestions ?????????
Tannks !!!!

I hope to make this a clear as possible. I have a basic excel table with dates down column A, and invoice total dollar amouts down columns B and C. I am trying to get a running average "Total daily spent" from these spreadsheets without creating a column D just to sum Columns B and C together. I am looking for a formula that will add each row in columns B with C then average it by the number of rows used. To try and clarify this, I need B2+C2 and B3+C3 then the average of the sum. But the rows are for 30 or 31 days and i want a "running" average which means i want it to compute "the boxes with values" so everyday as I add information, the formula auto recalculates it. Can anyone help? Thanks

I'm looking to have a simple formula reference another cell to find the range to use in the formula.
For example, say I have a column of numbers in column A.
Then I have a formula such as Average(A1:A20) in cell D1
And then maybe Average(A5:A10) in cell D2
And then maybe Average(A10:A15) in cell D3
And then maybe Average(A15:A20) in cell D4
So what I am doing is just changing the range of cells (rows) that I am using in the Average formula.
But instead of typing in different ranges in each formula, I would like to have a formula in D1 refer to another cell to determine the range to use.
For example, I want the starting and ending cell A1 & A20 to change according to what I have in say cell B1 and C1. So the Average formula would refer to say B1 to get the starting cell of the range and say C1 to get the ending cell of the range. Then I would list in B1 and C1 the starting and ending range cells or the row number desired.
Then I would repeat the same formula in D2, and that formula would refer to cells B2 and C2 to get its range to use. This would then continue for the other formulas.
So I am basically looking to tell the Average formula what starting and ending row to use. So far I haven't been able to figure out how to make a formula do that, and know what row/cell to use.
I realize I could name each range, (but then each formula in column D would still be different) but is there a way to have one formula so that I can just change the value in the B and C columns to tell the formula what rows to use in the Average formula? Or if I have to name the range is there a way to have the named range refer to another cell to find out its starting and ending row. Thanks

Hello All! Hope someone can help me with this. I'm trying to write a formula that uses todays date to determine a column reference and a VLOOKUP to determine a row reference (both in the same sheet) to have a certain cell displayed in another sheet.

I'll try to talk it through.

If todays date falls into the range of 'Schedule'!V5:W5 (column v is a start date and column w is an end date) then make the column reference 'Schedule'!B. If todays date falls between 'Schedule'!V6:W6 then make the column reference 'Schedule'!C. There are 17 date ranges so the possible column references are B through R.

I believe I have the VLOOKUP portion of the formula correct. This portion will return the row reference for the desired cell in 'Schedule'!. This part of the formula looks at a cell in column 'Data'!S and, based upon the text that is in that cell, returns the appropriate row reference.

For example, if todays date falls into the date range 'Schedule'!V7:W7 and the contents of cell 'Data'!$S8 is "CCC" then the cell the formula resides in will reference 'Schedule'!$D$7.

=INDIRECT("'Schedule'!TODAY(>={$V$5:$W$5,B;$V$6:$W$6,C;$V$7:$W$7,D;$V$8:$W$8,E;$V$9:$W$9,F;$V$10:$W$ 10,G;$V$11:$W$11,H;$V$12:$W$12,I;$V$13:$W$13,J;$V$14:$W$14,K;$V$15:$W$15,L;$V$16:$W$16,L;$V$17:$W$17 ,M;$V$18:$W$18,N;$V$19:$W$19,O;$V$20:$W$20,P;$V$21:$W$21,Q;$V$22:$W$22,R},2,FALSE))"&VLOOKUP($S8,{"A AA",5;"BBB",6;"CCC",7;"DDD",8;"EEE",9;"FFF",10;"GGG",11;"HHH",12;"III",13;"JJJ",14;"KKK",15;"LLL",16 ;"MMM",17;"NNN",18;"OOO",19;"PPP",20;"QQQ",21;"RRR",22;"SSS",23;"TTT",24;"UUU",25;"VVV",26;"WWW",27; "XXX",28;"YYY",29;"ZZZ",30;"111",31;"222",32;"333",33;"444",34;"555",35;"666",36},2,FALSE))

I tried to enter this but the computer said there were too many characters and suggested I use CONCATENATE. What am I missing? Is there a simpler way to to this?

All -

I have a sheet where data refreshes and everytime it does, the number of rows (records changes). I'm trying to write a simple formula that will allow me to divide two numbers and get a result and not get a #REF! error everytime the number of rows changes. I have a lot of rows (thousands) so I would like to be able to drag the formula to auto populate the column.

First I tried a simple reference of a1/b1 but I can't figure out how to change a bunch of references to absolute en masse without touching each cell.

Then I tried =INDIRECT("A1")/INDIRECT("A2") but you can't drag that so every cell would have to be typed individually.

I also tried =OFFSET(A1,0,0)/OFFSET(A1,0,1) but those return #REF! errors as well when the columns change without the absolute reference on cell A1 ($A$1) therefore I can't drag that formula either.

Any ideas would be greatly appreciated.

This may be a little hard to explain, but hopefully someone can follow my ramblings..

I am trying to get a formula to work - average a sequence of numbers only if the number of numbers is greater than or equal to 9. I have this working (across sheets if that is of consequence), but am having troubles with the else part of my if statement.

What I want, is that if the number of numbers is less than 9, then it outputs a zero (in Q2). Then, based on this result I have another cell outputting either a Yes (if the average is 1), a maybe (if the average is >= 0.85) else a no (in S2). The trouble is that if there is less than 9 numbers, the output of zero is not recognised as a number less than 0.85 and hence the output becomes "Yes".

The cell Q2 has been told to be of numerical type (with 2 decimal places).

Is there a way to force a cell to become an integer?

At the moment my formulae a
Q2 - =IF(Sheet2!C3>=Sheet1!$U$7, AVERAGE(Sheet1!D33), "0")
S2 - =IF(Q3>=$U$3,"Yes",IF(Q3>=$U$4,"Maybe","No"))
where the absolute references refer to the minimum constraints for a Yes / Maybe output and (in Q2) sheet2!C3 is the count(A) to work out whether the number of numbers is >= 9.

Make sense? I hope so!

I have a question and I dont know if this and be done but I was wondering is there is a away to make a formula increase itself per colunm.

=L12&" - "&I12&" "&H12&" "&J12&" - "&K12 <---- this is the formula that I am using

Say this is in A12 and I want to copy this to A13.

Is there away to make the the number 12 (=L12&" - "&I12&" "&H12&" "&J12&" - "&K12) change to a number 13 and increment by 1 for each and ever line I enter this in. 14,15,16 and so on.

So =L12&" - "&I12&" "&H12&" "&J12&" - "&K12 would change to this =L13&" - "&I13&" "&H13&" "&J13&" - "&K13 and so on.

What I am doing is pulling words from 4 different columns and making one column. and I am trying to figure out the best way to do this so I am not having to type this formula for thousands of columns.

Any ideas?

I want the AVERAGE function to be able to include cells which also contain the "<" character.
At the same time, I want cells containing the "<" character to use 50% of the reference value for purposes of the average calculation. I have attached an example of the output I'm seeking.

I would like for the solution to be a formula or array I can put into the calcuation cell without the need for dummy cells or off-page conversions or calculations. I can do that, using a single formula is what I'm struggling with.

I have three columns A= Plan, B = M, C = 204 which represents worksheet, column, row of an absolute reference to a different worksheet. I want to create a formula that I can copy down many rows that will link to the specified values and return the cell contents from various places in other worksheets (same workbook). I can get as far as a string that looks right but does not evaluate as a formula.

Plan M 204

Strihg equivalent is =CONCATENATE("=","ROUND(","'",F4,"'!",H4,G4,",0",")")
Calculate string is =ROUND('Plan'!M204,0)

How do I turn the string in the line above into a formula as if I had typed in literal characters, not calculated characters?