Question re Pasting Formulas in Excel Spreadsheets

[overedge]

Posting this here because the "Microsoft help" is no f*$%^ing help at all.

I have an Excel spreadsheet with a formula in one cell, and I want to paste that formula in the other cells in the column to do the same calculation, but to use the values in that row. E.g. if the formula includes a reference to R1C1 (row 1, column 1), I want to paste the formula and have it use R2C1 in row 2, R3C1 in row 3, and so on.

Is there a way to do this in Excel without going through and manually changing all the row/cell references?

Many thanks for any assistance....

 

[skatemomaz]

If you don't have the references locked i.e. $R1$C1 then Excel should automatically change the reference as you intend.
eta, if the references are locked in the first cell, press the F4 key and it will unlock (remove the $)

 

[Aceon6]

Yup. Copy it once and edit it to say R2$C$1, then copy the one with the $ into all the cells you need.

There's a really good tutorial here.

 

[skatemomaz]

Yup. Copy it once and edit it to say R2$C$1, then copy the one with the $ into all the cells you need.

There's a really good tutorial here.

if you edit to add the "$" it locks the reference and won't change with next row. I think @overedge wants the references to change in each corresponding cell.

 

[overedge]

@Aceon6 @skatemomaz I think I unlocked it, but should I be using "fill down" or "copy" or "paste" to put the formula in the other cells?

 

[skatemomaz]

either one works, it functions the same way.

 

[HeManSkaterDad]

If you want the column reference to remain constant, but the row to change, put a '$' only in front of the column reference. For example, =R1$C1. When you copy this down the rows, it will automatically be:

R2C1
R3C1
R4C1
etc.

 

[overedge]

Ha! Got it to work thanks to the hints here. The other thing I was doing wrong was calculating the formula like this:
=((r1c1)+(r1c2))
which in the former versions of Excel used to automatically change the column references when it was pasted. Now you have to use the SUM function to get it to do the same thing.

FSU is great - I would have been tearing my hair out for a couple of days otherwise. Thanks, everyone!

 

[nlloyd]

Hope it's okay to piggyback on this thread to ask a different Excel question.

Can anyone explain why one would use VLOOKUP rather than just a simple "Find" command in Excel? Does VLOOKUP enable the user to enter a batch of values (a list of student names, for example) and to retrieve a correlated list of related data (a list of grades that correlates to the list of student names)? Or would you have to take a one-by-one approach, looking up each student name and being referred to the grade?

Any help would be much appreciated! I have looked it up in Google, but can't find a quick answer to the batch question.

 

[ioana]

Vlookup could be used as a formula you drag down, so if you already have all the student names entered into columns and grades in columns to the right, it would be a quick way of looking up grades, absences, etc as a 'batch'. Not sure if that's what you meant, though.