Excel Automatic Formula

New Contributor


Question : Is anyone here know how to automatically copy a formula in excel from one cells to different cells ? Non-Adjacent cells.

I want automatically copy the formula from the Master.

I tried goggling it, but all answer I found is apply automatic in entire column. I don't want that because I have spacing in the table. Please help me.

See attached sample table.



3 Replies



There is an easy way. You could use the IF formula combined with "ISNUMBER", see formula below.



As you see, the blank columns has also got the forumla. 


Forumla:  =IF(AND(ISNUMBER(B2), ISNUMBER(C2)),B2*C2," ") 

Use the forumla in one cell, CRTL Shift Down arrow to insert it in the row. 

I want to remove the process of using the CTRL shift down.
1. I want the remaining cells downward to automatically copy the formulas on the 1st cell. Every time I change the formula or the conditioning like lookup etc. I want it to apply automatically to the rest.
Now : the formula is 1+1
Then after sometime I will change it to 1-1 or with vlookup, hlookup, etc.
Once I change the formula in Row D3, I want the remaining cells downward to update automatically without using the CTRL Shift Down Arrow. I want to skip that part. If there's a way to do that ?


Well, you just have to create a real table. The column with the answer is a calculated column. You just have to insert the formula in the first cell, and it will automatically copy the the rest. If you change one of the formula's, all of them will change.