/docs/community?hl=en
/docs/community?hl=en
12/6/10
Original Poster
vivekraj

How to copy formulas without changing anything in it?

I have serached through many posts but not found my answer (i expected i would) therefore i am asking it here.

I want to simply copy paste a range of cells (say from A1 till M10) almost all of them having formulas in them in such a way that the formulas get copied as it. Currently when i copy the range of cells the formulas get changed.

For example:

If cell A1 has ='sheet1'!A1 and cell C1 has =A1+B1 in them, and if i copy this row and paste it in A11 and C11 respectively, these formulas get changed into ='sheet1'!A11 and =A11+B11 respectively, whereas what i want the formulas to remain as it is even when copied to row 11.

The one way for this to work is use the $ sign in the appropriate places in the formulas, but putting this sign in all the formulas will be a cumbersome task.

Thanks for your help!
Community content may not be verified or up-to-date. Learn more.
Recommended Answer
Was this answer helpful?
How can we improve it?
All Replies (2)
ahab
12/6/10
ahab
Try this trick:

Select the range of cells. Then use Ctrl+C (copy; this should put the cells and their formulas in the paste buffer).
Now drag (hover over the edge of the selection until the mouse cursor changes into a hand; now you can drag) the selected cells to the new position.
Go back to the original top-left position of the copied cells and use Ctrl+V (paste).

Now you should have two sets of formulas both referencing the same cells.


Note Google Docs severely lacks a Ctrl+Drag option to copy cells with formulas without having to resort to the above trick...
rajvivek
12/7/10
rajvivek
Hi Ahab!

Your trick worked as expected. Meanwhile i also had found a way for it, but yours one is simpler and faster so i will stick to your trick. Thanks a lot!

Rightly said, Google Docs severely lacks a Ctrl+Drag option to copy cells with formulas.

I hope it will be implemented soon.

Thanks again for your help!  


 
This question is locked and replying has been disabled. Still have questions? Ask the Help Community.

Badges

Some community members might have badges that indicate their identity or level of participation in a community.

 
Google Employee — Google product team members and community managers
 
Community Specialist — Google partners who help ensure the quality of community content
 
Platinum Product Expert — Community members with advanced product knowledge who help other Google users and Product Experts
 
Gold Product Expert — Community members with in-depth product knowledge who help other Google users by answering questions
 
Silver Product Expert — Community members with intermediate product knowledge who help other Google users by answering questions
 
Product Expert Alumni — Former Product Experts who are no longer members of the program
Community content may not be verified or up-to-date. Learn more.

Levels

Member levels indicate a user's level of participation in a forum. The greater the participation, the higher the level. Everyone starts at level 1 and can rise to level 10. These activities can increase your level in a forum:

  • Post an answer.
  • Having your answer selected as the best answer.
  • Having your post rated as helpful.
  • Vote up a post.
  • Correctly mark a topic or post as abuse.

Having a post marked and removed as abuse will slow a user's advance in levels.

View profile in forum?

To view this member's profile, you need to leave the current Help page.

Report abuse in forum?

This comment originated in the Google Product Forum. To report abuse, you need to leave the current Help page.

Reply in forum?

This comment originated in the Google Product Forum. To reply, you need to leave the current Help page.