Transcripts for the above video clip:

COPY AND PASTE

The ability to COPY and PASTE in Excel, is one of its main and strong 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.