Forum Discussion
Solved
Different results when copying with Ctrl-C/Ctrl-V and with fill handle
I often use Excel tables and therefore also table formulas.
And I found something strange… When copying a table formula referring to a column in a table, the result is depending on the way it is copied.
Below a picture of what you can find.
Is there an explanation for this?
Saskia
The only explanation I've seen from Microsoft is
Using structured references with Excel tables - Microsoft Support
Pasting and filling with Ctrl+r locks the reference.
Dragging the cell with the fill handle will drag the reference as well.
If you want to lock the reference so you can drag with the fill-handle, change the original formula to this:
=SOM(Tabel2[[Price ex]:[Price ex]])You can watch this video for more detail.
When you use the fill handle to fill to the right, Excel will dynamically adjust the column reference, from Price ex to VAT% to VAT€ to Price incl.
To avoid this, use
=SOM(Tabel1[[Price ex]:[Price ex]])
That will "fix" the column reference when you fill to the right.
- I know this Hans.
But my question was:
why is the reference adjusted when I use the fill handle and NOT ADJUSTED when I use Ctrl+c and Ctrl+v?????
Normally there is no difference between those 2 ways of copying (as far as I know)The only explanation I've seen from Microsoft is
Using structured references with Excel tables - Microsoft Support
