HomeLatest ThreadsGreatest ThreadsForums & GroupsMy SubscriptionsMy Posts
DU Home » Latest Threads » Forums & Groups » Topics » Computers & Internet » Computer Help and Support (Group) » Excel question. How to lo...

Sun Apr 10, 2022, 10:54 AM

Excel question. How to lock a cell in a formula

I have done this many times in the past but don't remember how. ie =sum(b1:b500) what do i put in front of B1 to make it constant? Rather than going down a step when copying it down. ie =sum(b2:b501)

6 replies, 959 views

Reply to this thread

Back to top Alert abuse

Always highlight: 10 newest replies | Replies posted after I mark a forum
Replies to this discussion thread
Arrow 6 replies Author Time Post
Reply Excel question. How to lock a cell in a formula (Original post)
tiredtoo Apr 2022 OP
LineNew Reply $
Turbineguy Apr 2022 #1
sanatanadharma Apr 2022 #2
tiredtoo Apr 2022 #3
taxi Apr 2022 #4
twodogsbarking Apr 2022 #5
HubertHeaver Apr 2022 #6

Response to tiredtoo (Original post)

Sun Apr 10, 2022, 10:59 AM

1. $

I think.

Reply to this post

Back to top Alert abuse Link here Permalink


Response to Turbineguy (Reply #1)

Sun Apr 10, 2022, 11:12 AM

2. Yes, $A$1 to 'lock' cell A1

Reply to this post

Back to top Alert abuse Link here Permalink


Response to Turbineguy (Reply #1)

Sun Apr 10, 2022, 11:12 AM

3. thanks EOM

Reply to this post

Back to top Alert abuse Link here Permalink


Response to tiredtoo (Original post)

Sun Apr 10, 2022, 12:12 PM

4. Naming the cell works good too

Naming cells and ranges gets rid of the problems of having to fix formula when things get moved around.
Using your example: give the range of b2:b501 a name, call it betslost
Your formula would be =sum(betslost)
It won't matter if rows or columns are inserted or deleted, for example: you inserted six more columns. Now what was in B is now in column G. Wherever the formula =sum(betslost) appears still works.

Reply to this post

Back to top Alert abuse Link here Permalink


Response to tiredtoo (Original post)

Sun Apr 10, 2022, 12:32 PM

5. I think it was f4 but the f's are gone from today's keyboards.

Reply to this post

Back to top Alert abuse Link here Permalink


Response to twodogsbarking (Reply #5)

Mon Apr 11, 2022, 11:41 AM

6. Keyboards no longer give an f?

Reply to this post

Back to top Alert abuse Link here Permalink

Reply to this thread