Excel copy paste without changing formula
на канале: AuditExcel Advanced Excel and Financial Model Training and Consulting
00:00 Copy/ Paste cells without the formulas changing due to $ signs
00:11 Problem with copy/ paste and $ signs- it keeps looking at the absolute cells
00:54 Trick to replicate a section without it referring to other sections cells e.g. loan 1's interest rate instead of loan 2
02:05 Keep the integrity of calculations within a block of Excel cells
The cut and paste tool in Excel is a very useful tool to create exact copies of sections. But first let me explain the problem. Here we've got a product A section -- you'll see we've set it up. We've got some nice calculations, etc and my temptation will be to take this, copy it and just to paste it here. As shown in other video clips, the problem is, depending on how you did the formula, you'll see the dollar signs mean that we think we wanted the formulas to look within themselves but because of the dollar sign it is looking at product A. I'm just deleting this. We can make use of cut and paste to avoid this. First what you need to do is just create a new sheet -- a new spreadsheet. I'm just going to open a new spreadsheet -- so I've got this blank spreadsheet. Now this is key -- what I am going to do is I am going to take this section and instead of copying it, I'm actually going to cut it. I'm going to cut it and put it anywhere in the blank spreadsheet. So now we've got it sitting there. Come back to the original spreadsheet -- I've removed it from Product A. The key here is I'm going to now close down this spreadsheet but very important I'm not going to save it -- no saving. Close it down. Then immediately just open it up again -- you'll see because we didn't save it, Product A is still sitting there and go back to the new sheet -- again I will cut it, come back to the original spreadsheet, put it in the right place and I paste it. And now what you'll see is that whereas previously this formula looked up here because it was a copy and paste. Using cut and paste we have now kept the integrity of that whole block and in a fairly simple way we have safely made an exact copy of this section.
