201

Lets say I have one cell A1, which I want to keep constant in a calculation. For example, I want to calculate a value like this:

=(B1+4)/(A1)

How do I make it so that if I drag that cell to make a calculation across cells in many rows, only the B1 value changes, while A1 always references that cell, instead of going to A2, A3, etc.?

Alex Brown
  • 41,819
  • 10
  • 94
  • 108
dude
  • 2,013
  • 2
  • 13
  • 4
  • In case you want lot of simple formulas check matrix formulas with ranges - you cannot change anything in that matrix without changing main formula or whole range. For example if I create simple formula {=B1:B15} indexes to B range will change only in case you cut/paste B1-B15 cells at once, if you move only some fields, base formula will remain =B1:B15 – Jan Mar 06 '20 at 12:33

8 Answers8

361

Use this form:

=(B0+4)/$A$0

The $ tells excel not to adjust that address while pasting the formula into new cells.

Since you are dragging across rows, you really only need to freeze the row part:

=(B0+4)/A$0

Keyboard Shortcuts

Commenters helpfully pointed out that you can toggle relative addressing for a formula in the currently selected cells with these keyboard shortcuts:

  • Windows: f4
  • Mac: CommandT
Alex Brown
  • 41,819
  • 10
  • 94
  • 108
  • 24
    It should be noted that the dollar sign keeps the adjacent character constant when dragging. `$B1` (B will be kept constant and the 1 will count up) will be different to `$B$1` (both B and 1 will remain constant) – Jonno_FTW Jan 28 '10 at 17:40
  • 15
    added note: instead of manually typing the dollar signs you can toggle them by selecting the cell and pressing F4. – guitarthrower Jan 29 '10 at 00:00
  • 1
    Also I noticed that you don't need to freeze that row also. I had a top row label that I needed as a constant. – Pranjal Apr 20 '16 at 16:02
  • This is my formula `=IF(I4<>"",CONCATENATE("Compilation error ",I4),"")& CHAR(20)& IF(J4<>"",CONCATENATE(J$2," ",J4),"")` and it is printing crap instead of what i wanted. it is printing `` – Vipin Verma Sep 01 '17 at 05:17
  • 1
    @vipin8169 that sounds like a whole new question, Vipin, and the comments section of this answer is not the best place to ask for help on new questions. – Alex Brown Oct 01 '17 at 03:27
29

There are two kinds of cell reference, and it's really valuable to understand them well.

One is relative reference, which is what you get when you just type the cell: A5. This reference will be adjusted when you paste or fill the formula into other cells.

The other is absolute reference, and you get this by adding dollar signs to the cell reference: $A$5. This cell reference will not change when pasted or filled.

A cool but rarely used feature is that row and column within a single cell reference may be independent: $A5 and A$5. This comes in handy for producing things like multiplication tables from a single formula.

Carl Manaster
  • 39,912
  • 17
  • 102
  • 155
10

To make your formula more readable, you could assign a Name to cell A0, and then use that name in the formula.

The easiest way to define a Name is to highlight the cell or range, then click on the Name box in the formula bar.

Then, if you named A0 "Rate" you can use that name like this:

=(B0+4)/(Rate)

See, much easier to read.

If you want to find Rate, click F5 and it appears in the GoTo list.

DOK
  • 32,337
  • 7
  • 60
  • 92
7

You put it as =(B0+4)/($A$0)

You can also go across WorkSheets with Sheet1!$a$0

Raj More
  • 47,048
  • 33
  • 131
  • 198
6
=(B0+4)/($A$0)

$ means keep same (press a few times F4 after typing A4 to flip through combos quick!)

davidosomething
  • 3,379
  • 1
  • 26
  • 33
  • And someone finally talked about the keyboard shortcut! I actually use LibreOffice Calc but the shortcut is pretty similar, `Shift+F4`. Thanks! – Severo Raz Sep 04 '13 at 03:53
3

Placing a $ in front of the row value to keep constant worked well for me. e.g.

=b2+a$1
Matt
  • 45,022
  • 8
  • 78
  • 119
Matt
  • 512
  • 4
  • 7
1

Yeah. Just put the $ sign in front of your desired constant cell.

Like $A6 if you wish to just change the number 6 serially and keep a constant, or $A$6 if you do not want anything from that reference to change at all.

Example: Cell A5 contains my exchange rate. In B1 you put say ( = C1 * $A$1). when you fill B1 through B....... the value in A5 remains constant and the value in C1 increases serially.

I am by far not be good at teacher, but I hope this helps!!!! Wink wink

Albert Patrick Sankoh
  • 21
  • 1
0

For future visitors - use this for range: ($A$1:$A$10) 

Example
=COUNTIF($G$6:$G$9;J6)>0
Baked Inhalf
  • 3,375
  • 1
  • 31
  • 45