# Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

DIVIDE NUMBER IN COLUMN A BY A FIXED NUMBER AND THEN SUBTRACT THAT TOTAL BY 1. HOW DO I COPY AND PASTE THIS FORMULA SO THAT IT APPLIES TO ALL OF THE ROWS?
Solved by A. U. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 17/07/2018 - 11:41
Hello there, welcome to Got It Pro.
User 17/07/2018 - 11:41
THANK YOU
Excelchat Expert 17/07/2018 - 11:41
Hope you are having a good day.
User 17/07/2018 - 11:41
you as well. do you understand my question?
Excelchat Expert 17/07/2018 - 11:41
Could you please upload the file you are having problems with? Please use the attachments button on the right of the chat tab.
Excelchat Expert 17/07/2018 - 11:41
Yes.
Excelchat Expert 17/07/2018 - 11:42
You want to divide the numbers in a column by a fixed number and subtract by 1.
Excelchat Expert 17/07/2018 - 11:42
Is that it?
User 17/07/2018 - 11:43
yes
Excelchat Expert 17/07/2018 - 11:43
Please have a look at column C.
Excelchat Expert 17/07/2018 - 11:43
The formula is =A1/\$B\$1-1
Excelchat Expert 17/07/2018 - 11:43
After you perform the calculation for cell C1.
Excelchat Expert 17/07/2018 - 11:43
Drag the little square on the bottom right to the cells below.
User 17/07/2018 - 11:44
thanks, ill try that now
Excelchat Expert 17/07/2018 - 11:44
This would calculate for the result for the corresponding values.
Excelchat Expert 17/07/2018 - 11:44
Sure.
Excelchat Expert 17/07/2018 - 11:46
Please let me know if it worked.
Excelchat Expert 17/07/2018 - 11:52
Hi, still there?
Excelchat Expert 17/07/2018 - 11:52
The "\$" sign here is working as an absolute reference.
User 17/07/2018 - 11:53
it didnt work
Excelchat Expert 17/07/2018 - 11:53
Here \$B\$1 refers to 4 and when you copy the formula below the other cells change but it stays the same.
Excelchat Expert 17/07/2018 - 11:53
Here?
User 17/07/2018 - 11:53
the formula works, but i cant seem to copy and paste to all the rows
Excelchat Expert 17/07/2018 - 11:53
Did you drag the little square?
User 17/07/2018 - 11:54
yes
Excelchat Expert 17/07/2018 - 11:54
This one.
Excelchat Expert 17/07/2018 - 11:54
Excelchat Expert 17/07/2018 - 11:54
User 17/07/2018 - 11:55
it just shows the same answer from row 1 in all the rows
Excelchat Expert 17/07/2018 - 11:55
User 17/07/2018 - 11:55
yes
Excelchat Expert 17/07/2018 - 11:56
Okay, its because your calculations are set to manual.
User 17/07/2018 - 11:56
ahhh, how do i unset them from manual?
Excelchat Expert 17/07/2018 - 11:56
To change it, go to File > Options > Formulas
Excelchat Expert 17/07/2018 - 11:56
Set it to automatic.
User 17/07/2018 - 11:56
ill try that now
Excelchat Expert 17/07/2018 - 11:57
Then try to drag the first cell down to the cells below.
Excelchat Expert 17/07/2018 - 11:57
And see if that works.
User 17/07/2018 - 11:57
Presto!
Excelchat Expert 17/07/2018 - 11:58
Great!!
User 17/07/2018 - 11:58
it works!
User 17/07/2018 - 11:58
thank you!
Excelchat Expert 17/07/2018 - 11:58
Excellent
Excelchat Expert 17/07/2018 - 11:58
Welcome.
Excelchat Expert 17/07/2018 - 11:58
Thanks for using Got It Pro. Please come back again with your Excel related queries.
Excelchat Expert 17/07/2018 - 11:58
Have a good day and if your query is resolved kindly end the session.
User 17/07/2018 - 11:58
thank you
Excelchat Expert 17/07/2018 - 11:58
You're welcome.
Excelchat Expert 17/07/2018 - 11:58
Kindly end the session if your query is resolved.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.