On a 11^{th} hour of processing complex Excel worksheets, knowing certain time saving techniques could make a huge difference. In this post, I am explaining few useful techniques to reuse excel formula across worksheets and workbooks.
Copy Pasting Excel Formula across Worksheets (within Same Workbook)
The easiest possible scenario, you will have to copy the formula from one cell range into another within same excel workbook. For this copy (Ctrl +C) the source cells and do a paste special on your destination and choose the ‘formula’ option from the paste special option (shown below).
Copy Pasting Formula across Excel Workbooks
The steps for copying formula across excel workbooks are very much same like copying within same workbook. But if you do receive reference errors like shown below then make sure the cell range referenced in the formula are valid in your new workbook.
Copy Pasting Formula across Excel Workbooks when Formula consists of Cross Sheet/Workbook References
Let us take another tricky but a practical scenario. I want to copy the formula which references some cell range from other sheets of the workbook.
I have a table present in Sheet2 of Book1.xlsx like shown below.
On Sheet1of Book1.xlsx I am using formula to get the sum of Table A + Table B like:
Now, if you want to copy the formulas from this range into a different workbook e.g. Book2.xlsx you
will notice the formula is copied along with the workbook reference for e.g. SUM([Book1.xlsx]Sheet2!C4:C4)
So copying the exact formula across workbooks in this case will be slightly tricky. There are lot of confusing/outdated references exist across web for this. But I have researched a simple solution for this problem.
 First, paste the formula into your destination workbook
 Now you will have to remove the references of the original workbook source

For this, use the ‘Find and Replace’ functionality of excel and remove the formula source reference, in this case [Book1.xlsx] and replace it with nothing
 After ‘Replace All’ all your formula source references will be removed and you achieve the exact formula pasting into your destination workbook in line with your source. You only need to ensure the similar named sheets and required data exist at your destination workbook as well
Did you find this post useful? Feel free to share your feedback in the comments area.
You can find more interesting posts from us on the posts index section
If you are interested to be notified for the new posts from us, then you can make use of the subscribe option available on the right sidebar.
Help us to improve our search rating by recommending this post to Google using the button below
1 comment
Daniel says:
March 5, 2014 at 5:31 am (UTC 5)
Great post and primarily helpful! The copy and paste function is very handy.