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

Copper Contributor

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

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 example

5 Replies

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

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”

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

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

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?

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

If i understand you correctly, then what you need to look into is the absolute/relative references
https://trumpexcel.com/absolute-relative-mixed-cell-references/
This is the way to determine how formulas adapts to be 'dragged'

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

Try this:

=IF(AND(E\$3>=\$C4,E\$3<=\$D4,"1",0")

This will do what you ask for.

But perhaps better to use:

=--AND(E\$3>=\$C4,E\$3<=\$D4)

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

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