We usually need to apply the same formulas while dealing with many worksheets. When we copy a cell that does not have a formula but has value and pastes it someplace, it is usually pasted as value. However, in order to use the formula in the pasted cells, we must copy the formula from another worksheet and alter the value to match the data set in the current sheet. In this tutorial, we will explain to you how to copy formula to another sheet in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Quick Ways to Copy Formula to Another Sheet in Excel
We’ll go through four fast techniques to copy the formula to another sheet in the sections below. To be more specific, we will do the task using the fundamental copy-paste approach, Clipboard group, and Excel’s built-in Find and Replace option. In the below image, we have provided a sample data set to copy a formula to another sheet.
1. Apply the Keyboard Shortcuts to Copy Formula to Another sheet in Excel
The simplest method is to copy the formula using the keyboard shortcut. Follow the instructions below to do so.
- At first, press Ctrl + C to copy a selected cell.
- Go to another sheet, and press Ctrl + V to paste.
- As you can see, the value is modified on another sheet while the formula remained the same.
- Finally, use the AutoFill tool to fill the next cells.
2. Use the Formula Bar to Copy Formula to Another sheet in Excel
Another way for copying formulas is to use the Formula Bar. Follow the instructions below to copy the formula.
- Firstly, copy the formula from the first sheet.
- Press the Esc
- Then, paste the formula to another sheet.
- Finally, use the AutoFill tool to fill the blank cells.
Related Content: How to Copy Formula in Excel (6 Quick Methods)
- Excel VBA to Copy Formula with Relative Reference (A Detailed Analysis)
- How to Copy Formula in Excel without Dragging (10 Ways)
- Copy Formula to Entire Column in Excel (7 Ways)
- Copy Formula in Excel by Changing Only One Cell Reference
- How to Copy a Formula in Excel with Changing Cell References
3. Use the Clipboard Group to Copy Formula to Another sheet in Excel
When you’re working with a huge data set and want to replicate the formula for a specific section, using the Clipboard Group to copy and paste formulas comes in handy. To utilize the Clipboard Group, follow the instructions below.
- Select a cell and from the Clipboard Group, click on the Copy option.
- In another sheet, select the Paste option from the Clipboard Group.
- From the Paste option, select the Formulas(F) option.
- After getting the first result, AutoFill the cells with the same formula.
Related Content: How to Copy Exact Formula in Excel (13 Methods)
4. Perform the Find & Replace Option to Copy Formula to Another sheet in Excel
In addition to the previously mentioned parts, the Find and Replace option is a little-known but extremely valuable feature. Follow the steps outlined below.
- At first, select the cells.
- Then, press Ctrl + H to open the Find and Replace
- In the Find what box cell type equal to (=)
- In the Replace with box, type the ‘nothere’
- Then, click on Replace All
- Click OK.
- Then, click Close.
- Copy the cells as shown in the below screenshot.
- Now, paste it into another sheet.
- In the Find what box type ‘nothere’.
- In the replace with box type equal to (=)
- Then, click to Replace All.
- Finally, click on Ok.
- Finally, click on Close.
- Therefore, you will see that the formula is copied to another sheet.
Notes. After copying the formula in a new sheet, we may see that the first sheet will change. To get the sample data set in the previous formation we have to follow the rules below.
- Firstly, write ‘nothere’ in the Find what.
- Secondly, write the equal (=) sign in the Replace with.
- Simple, click on the Replace All.
- Then, click OK.
- Finally, click on the Close
- Finally, get the values in the previous format.
To summarize, I hope you now understand how to copy formulae from one Excel page to another. All of these techniques should be taught and used to your data. Look over the practice book and put what you’ve learned to use. Because of your vital support, we’re encouraged to continue offering lectures like this.
If you have any questions, please do not hesitate to contact us. Please let us know what you think in the comments section below.
The Exceldemy staff will respond to your questions as quickly as possible.
Stay with us and keep learning.