Copy formula in Rows and Excel without changing the reference manually.

Copper Contributor

Is there any easy way I can copy IF statement formula to rows and columns both. I have to change the reference for every row to drag it to corresponding columns. I am enclosing the screenshot as an exampleExcel.PNG

5 Replies

Hi @max342ss

Looking at the image I understand you are trying to reference a formula on PivotTable,

  1. Keep the cell on the Pivot Table
  2. PivotTable Analyze & Design Tab will be activated in the Excel Ribbon
  3. Select PivotTable Analyze > Option > Uncheck “Generate GetPivotData”

this blog might be helpful for your query

How to remove GETPIVOTDATA in Excel for Mac and Windows 


Regards, Faraz Shaikh | Microsoft MVP, MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget to mark it as Official/Best Answer to help the other members find it more


Hi Faraz,
No, the formula has been applied to a normal cell (The pivot table is only until Column D) It's an IF statement that needs to be copied in both rows and columns, we have to change the reference every time for the row to drag it to corresponding columns. Is there a way to do it easy it quick?
If i understand you correctly, then what you need to look into is the absolute/relative references
This is the way to determine how formulas adapts to be 'dragged'


Try this:



This will do what you ask for.


But perhaps better to use:



If numbers, not texts, I'd simplify to =(E$3>=$C4)*(E$3<=$D4)