The risks of using the COPY PASTE Excel feature without understanding the impact of absolute and relative referencing
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.
If I understand the query below correctly, the one idea is to type some words in a cell, and then, while holding the ALT key down, click ENTER. Within the cell you will get another line. Hope this helps. Original query Hi, … Continue reading
A recent email query asked how to share and unshare workbooks in Microsoft Excel with a specific focus on switching off the sharing. The method to share and ‘unshare’ the workbook is the same. As shown in the image below, … Continue reading
AuditExcel (aka Miricle Solutions) provided the Sasol Chlor Vinyls financial team with professional excel training whilst fully understanding the accounting landscape which is highly recommend.– Sasol Polymers