Lets say I have one cell A0, which I want to keep constant in a calculation. For example, I want to calculate a value like this:
=(B0+4)/(A0)
How do I make it so that if I drag that cell to make a calculation across cells in many rows, only the B0 value changes, while A0 always references that cell, instead of going to A1, A2, etc.?
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
=(B0+4)/($A$0)
$ means keep same (press a few times F4 after typing A4 to flip through combos quick!)
You put it as =(B0+4)/($A$0)
You can also go across WorkSheets with Sheet1!$a$0
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.
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 a
tables from a single formula.