In this recent post:
Friday Challenge Answers – Cumulative Events Over Time
I created a formula that referenced a table. As I created my video I noticed a problem. My Excel formula wouldn’t copy correctly to additional columns.
But it worked when I created it and then as I was making my video it wasn’t working. I was going crazy.
Then I figured it out! It wasn’t working when I used the Fill Handle to copy/paste the formula across columns.
But if I used CRTL+C copy and CTRL+V to paste across my range, it worked fine. What is the deal?
The issue is that the Fill Handle treats table references as a Relative Reference. Copy/Paste or CTRL+C & CTRL+V treats table references as an Absolute Reference or at least it doesn’t modify the formula when you copy and paste it across columns.
Here is my formula in Column E:
=COUNTIFS(Table1[Date],$D3,Table1[Incident],E$2)
If I use the Fill Handle and copy it to Column F, here is how the formula looks:
=COUNTIFS(Table1[Incident],$D3,Table1[Date],F$2)
Excel has shifted the formula table references by one column. It does the same in column G, but that ends up working as Excel shifts it back to the first column after it hits the end of the table columns. Here are a few ways to fix this if it is a problem for your formulas.
How to stop Excel from treating table formula references as relative and act more like absolute references:
1) Fix Version 1 – Use Copy/Paste Instead of the Fill Handler
If you just use Copy and Paste without using the Fill Handle, here is what column F looks like:
=COUNTIFS(Table1[Date],$D3,Table1[Incident],F$2)
Excel shifts the regular cell references as expected and it keeps the table references as though they were absolute references. Copy/paste is one way to go. There is another way to also fix this possibly unwanted outcome.
2) Fix Version 2 – Create Absolute Table References in Your Formula.
You can actually change your formula that references tables to act as an Absolute Reference instead of the Relative References that we were seeing in our sample formula.
You will need to change your original formula from this:
=COUNTIFS(Table1[Date],$D3,Table1[Incident],E$2)
To This:
=COUNTIFS(Table1[[Date]:[Date]],$D3,Table1[[Incident]:[Incident]],E$2)
To make your formula references to a table absolute, you will need to put an additional [ ] wrapper around a double column range reference
i.e. From this: Table1[Date] to this Table1[[Date]:[Date]]
If you do this, you can THEN use the Fill Handle to copy and paste the formula and get the formula that we wanted. Here is what the formula looks like when I copy and paste it using the fill handle from an adjacent column.
=COUNTIFS(Table1[[Date]:[Date]],$D3,Table1[[Incident]:[Incident]],F$2)
Problem solved. Now I know and knowing is half the battle 🙂
Here is a short video demonstrating the problems that I encountered and showing you how to fix it.
I don’t use Excel Tables enough in my work, but I am starting to do it more and more. Tables are powerful in Excel, but when you use them in conjunction with formulas, you will want to remember which copy/paste technique that that will work best. I think I will NOT use the handle in most cases. This didn’t work as I expected using the fill handle. What do you think? Let me know in the comments below if you have run into this before.
Steve=True
