- To post as a guest, your comment is unpublished.· 7 months agoHi Thomas CHARLES,
AutoText will not change the formula. However, you can change the named range to reference cells in the formula before saving it as an AutoText entry.
FYI, Kutools for Excel’s Convert Name to Reference Range feature can help you easily convert all named ranges to corresponding ranges easily.
- To post as a guest, your comment is unpublished.· 7 months agoThank you, but I have several versions of the same document, and I wanted to paste the formula keeping the naming ranges (as they are consistent across the files). Thanks anyway.
How to copy formulas from one workbook to another without link?
For example, you need to copy formulas in selected range from current workbook to another workbook without link, how to solve it easily in Excel? Here we will share three methods with you.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
To prevent formula references from changing during copying, we can slightly modify formulas and then copy. You can do as follows:
1. Select the range in which you will copy the formulas, and select Range H1:H6 in our case, and then click Home > Find & Select > Replace. See below screen shot:
Note: You can also open the Find and Replace dialog box with pressing the Ctrl + H keys simultaneously.
2. In the opening Find and Replace dialog box, type = into Find what box, type a space into Replace with box, and then click the Replace All button.
Now a Microsoft Excel dialog box comes out and tells how many replacements it has done. Just click the OK button to close it. And close the Find and Replace dialog box.
3. Keep selecting the range, copy them and paste into the destination workbook.
4. Keep selecting the pasted formulas, and open the Find and Replace dialog box with clicking Home > Find & Select > Replace.
5. In the Find and Replace dialog box, type a space into the Find what box, type = into Replace with box, and then click the Replace All button.
6. Close popping up Microsoft Excel dialog box and Find and Replace dialog box. Now you will see all formulas in original workbook are copied exactly into the destination workbook. See below screen shots:
(1) This method requires opening both workbook with formulas you will copy from and destination workbook you will paste to.
(2) This methods will modify the formulas in original workbook. You can restore the formulas in original workbook with selecting them and repeating the Step 6 and Step 7 above.
Actually, we can quickly convert formula to text by Kutools for Excel's Convert Formula to Text feature with only one click. And then copy the text of formula to another workbook, and at last convert the text of formula to real formula by Kutools for Excel's Convert Text to Formula feature.
1. Select the formula cells you will copy, and click Kutools > Content > Convert Formula to Text. See screenshot below:
2. Now the selected formulas are converted to text. Copy them and then paste into the destination workbook.
3. Keep selecting the pasted text, and then click Kutools > Content > Convert Text to Formula to convert the pasted text to formula.
This method will introduce the Exact Copy utility of Kutools for Excel, which can help you exactly copy multiple formulas to a new workbook with ease.
1. Select the range in which you will copy the formulas. In our case, we select the Range H1:H6, and then click the Kutools > Exact Copy. See screen shot:
2. In the first opening Exact Formula Copy dialog box, click the OK button.
3. Now the second Exact Formula Copy dialog box opens, go to the destination workbook and select a blank cell, and click the OK button. See screenshot above.
(1) If you can’t switch to the destination workbook, please enter the destination address such as [Book1]Sheet1!$H$2 into the above dialog box. (Book1 is destination workbook name, Sheet1 is destination worksheet name, $H$2 is the destination cell);
(2) If you have installed Office Tab (Have a free trial), you can switch to the destination workbook easily with clicking the tab.
(3) This method requires opening both workbook with formulas you will copy from and destination workbook you will paste to.
Sometimes, you may just want to copy and save a complex formula in current workbook, and paste it to other workbooks in future. Kutools for Excel’s Auto Text utility enables you to copy a formula as auto text entry, and let you reuse it in other workbooks with only one clicking easily.
1. Click the cell in which you will copy the formula, and then select the formula in the formula bar. See below screen shot:
2. In the Navigation Pane, click in the far left of Navigation pane to switch to Auto Text pane, click to select the Formulas group, and then click the Add button at the top. See below screen shot:
3. In the opening New Auto Text dialog box, type a name for the auto text entry of formula, and click the Add button. See screenshot above.
4. Open or switch to the destination workbook, select a cell, and then click the auto text of formula, the formula will be pasted to the select cell at once.
The auto text of formula can be reused at any time in any workbook with only one clicking.
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 7 months agoHi, they auto text feature doesn't work if there's a named range : it keeps the reference to the old workbook :/
- To post as a guest, your comment is unpublished.· 1 years agoFor method one don't replace with a space as lots of formulas use spaces. Replace with a character that is not used in formulas. I find _ works quite well. Also check the number of replacements made in the first workbook and ensure it is the same in the second.
- To post as a guest, your comment is unpublished.· 2 years agoCtrl + ~ - shows formulas
Copy to notepad
Copy to target
- To post as a guest, your comment is unpublished.· 2 years agoThank you sooo much
- To post as a guest, your comment is unpublished.· 2 years agoJust go to Data -- Edit Links ---- and change source. Change the source to the current workbook where you want the formulas to point to. This works like magic.
- To post as a guest, your comment is unpublished.· 2 years agoKiss: "Keep It Simple & Stupid"
This saved me so much time!