Microsoft Excel Tutorial: Copy Excel Formulas without Changing Cell References.
Welcome back to the MrExcel netcast! In today's video, we have a great tip from Marcus in Germany on how to easily replicate formulas without having to manually edit them.
We often come across situations where we need to copy and paste formulas to another location, but we want the new formulas to point to the exact same cells as the original ones. However, if the original formulas do not have dollar signs, it can be a tedious task to go through and add them in.
But fear not, Marcus has a solution for us. Instead of copying and pasting, we can use the "Edit" and "Replace" function. First, we select all the cells with the formulas, then go to "Edit" and choose "Replace". In the "Find what" field, we enter the equal sign (=) and in the "Replace with" field, we can enter any word we want, such as "equal". Then, we click on "Replace All" and then "Close".
Next, we can simply copy and paste the formulas to the new location. Since the formulas are now just text, the copy and paste function will create an exact copy, including the original cell references. Once the formulas are in the new location, we can use the "Edit" and "Replace" function again, but this time we replace the word "equal" with the equal sign (=). This will convert the text back into formulas, but with the original cell references intact.
This trick is similar to a cut and paste, but it allows us to keep the original cell references without having to manually edit the formulas. Thank you to Marcus for sharing this helpful tip with us. Make sure to give this video a thumbs up and subscribe to our channel for more useful Excel tips and tricks. See you next time for another netcast from MrExcel.
Buy Bill Jelen's latest Excel book: [ Ссылка ]
You can help my channel by clicking Like or commenting below: [ Ссылка ]
Markus from Germany sends in this tip. Say that you have to make an exact copy of a range of formulas. It is like you need to do a CUT and PASTE, but also leave the original cells where they were. Normally, a Copy and Paste will rewrite the formulas. Using this trick from Markus, you can achieve the desired result. Episode 866 shows you how.
Table of Contents:
(00:00) Introduction
(00:20) Replicating formulas
(00:31) The need for dollar signs
(00:43) Marcus' trick
(00:58) Copy and Paste
(01:08) Replacing "equal" with =
(01:19) Identical copies
(01:35) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these common search terms:
changing word back to =
Copy and Paste
Copy and Paste text
Cut and Paste with original cells
dollar signs in formulas
Edit Replace function
identical copies of original format
Marcus' trick
netcast from MrExcel
replacing = with a word
replicating formulas
Join the MrExcel Message Board discussion about this video at [ Ссылка ]
