excel question

[bignateyk]

If I have a formula like: C1 = (A1 + B1)

And then I drag down to auto-complete, excel does: C2 = (A2 + B2),C3 = (A3 + B3), etc... by default.

If I want it to keep A1 as a constant so it does: C2 = (A1 + B2), C3 = (A1 + B3)...

How would I tell excel not to automatically increment the A part of the formula when I drag down?

 

[dullard]

C1 = (A$1 + B1). The A can change but the 1 won't.

You put a dollar sign before what you want to be constant. If you want the A to be constant when copying horizontally, then you'd do this:

C1 = ($A1 + B1). The 1 can change but the A won't.

Or, if you want both constant you'd do this:

C1 = ($A$1 + B1). No matter where you copy it, it'll still be A1.

Alternatively, give cell A1 a name that you can remember (such as bignateyk), and do this:

C1 = (bignateyk + B1). No matter where you copy it, it'll still be A1 since A1 = bignateyk. The real advantage here is that you know what it is while you might not know what A1 is.

 

[mb]

Wrong forum.

And, replace A1 with $A$1

 

[KeithTalent]

F4 when on A1 in the formula and wrong forum.

KT

 

[Anubis]

$A$1

highlight A1 in the formula hit F4

 

[bignateyk]

awesome, thanks.

 

[AMDZen]

Wow learn something new every day

 

[dullard]

Originally posted by: AMDZen
Wow learn something new every day

Yep. I've never heard the F4 shortcut. Although, moving from the keyboard, to the mouse, to click on the variable, and back to the keyboard may be more work that just typing the dollar sign to begin with. But, if I have to edit a pre-existing equation, I might go with the F4 trick.

Thanks for teaching me something.

 

[Tweak155]

Originally posted by: dullard
C1 = (A$1 + B1). The A can change but the 1 won't.

You put a dollar sign before what you want to be constant. If you want the A to be constant when copying horizontally, then you'd do this:

C1 = ($A1 + B1). The 1 can change but the A won't.

Or, if you want both constant you'd do this:

C1 = ($A$1 + B1). No matter where you copy it, it'll still be A1.

Alternatively, give cell A1 a name that you can remember (such as bignateyk), and do this:

C1 = (bignateyk + B1). No matter where you copy it, it'll still be A1 since A1 = bignateyk. The real advantage here is that you know what it is while you might not know what A1 is.

I always used the constant way but isn't always beneficial i found out... this is a nice trick that I'm surprised I didn't know.

 

[JS80]

Originally posted by: dullard

Originally posted by: AMDZen
Wow learn something new every day

Yep. I've never heard the F4 shortcut. Although, moving from the keyboard, to the mouse, to click on the variable, and back to the keyboard may be more work that just typing the dollar sign to begin with. But, if I have to edit a pre-existing equation, I might go with the F4 trick.

Thanks for teaching me something.

you don't need to even have the item selected, you just have have the cursor touching the cell name and it will work.

ctrl + ; gives today's date
alt + ; selects only visible cells
alt + = sums the above cells
ctrl + scroll up/down with mouse wheel zooms and zooms out
ctrl + 1 format cells

i can go on this is what's on the top of my head

 

[dullard]

Originally posted by: JS80
ctrl + ; gives today's date
alt + ; selects only visible cells
alt + = sums the above cells
ctrl + scroll up/down with mouse wheel zooms and zooms out
ctrl + 1 format cells

i can go on this is what's on the top of my head

I've seen a couple of those, nothing is too useful for me there (but I can see them being great for other people).

But I saw the ctrl + 1 and though about trying the other keys. Crtl + 6 seems quite useful. It hides/shows all the graphs. I'm always moving graphs around to see the data under them. This will help quite a bit.