Copy Paste Excel

The risks of using the COPY PASTE Excel feature without understanding the impact of absolute and relative referencing

• 02:24
• Skill Level: 2
• What's my skill level
  

  All of our videos are listed by user Excel Skill Level.

  To find out your personal skill level, download our quick assessment.

  Once you've completed the test, send it to us and we'll evaluate for you.

  

• 

Copy Paste Excel

The  COPY and PASTE in Excel, is one of its best features. However as a result of absolute and relative referencing this can be extremely dangerous and a number of errors result due to this.

For example what you have here is a typical spreadsheet for Product A which has been set up, and you are busy calculating the Revenues and the Margins.

And hat you’ll see here is that the Revenue is taking the Price Per Unit, multiplying by the Units Purchased.
Lets copy it across,
And the Cost of Sales its determined by taking the Revenue and multiplying it by the Margin. Now because o this ability to copy and paste, if you’ve built it once, now what you do is you
highlight that section,
you’ll copy it
and you’ll past it
and you’ll believe its correct.
U obviously copy paste and it should work, however what you have to be careful of, is if we for example

change that margin to 20%,
you may notice that nothing has change here, and that should immediately raise a concern
and what you’ll see is, if you go to the Cost of Sale number and you look where its looking,
because in this section here, you used absolute referencing, you said always look at B11, when you copy and paste it,
it followed your instructions, and as a result your cells are looking at the incorrect calcuculation
Another way to identify that would be to

click on the Margin and say what else is looking at that Margins, and you’ll be warned that there’s no other formula looking at this cell and be concerned about it.
This is one of the most common errors, it’s important to consider what you do when you copy and paste.
I recommend you use the auditing toolbar, and if you are ever going to do a copy and paste, just consider if you have any absolute referencing, and this TRACE Dependents feature is extremely useful because you can immediately tell what inputs are or aren’t being used.