copy formula to the whole column

hello all,

I have a formula as follows.

=SUMPRODUCT(--(A78=Yield!$A$13:$A$35000),--(S78>Yield!$G$13:$G$35000),--(S78<=Yield!H$13:$H$35000),(Yield!$D$13:$D$35000))


I am trying to copy this formula to the whole column. The way I am doing this is by copying the formula in the first cell using ctrl + c and selecting the whole column and using ctrl+V. This method copies the value in the first cell to the whole 65000 rows. And when i click on any cell it looks like the formula has been copied down correctly but the values are not computed properly. whereas instead of copy and paste if I drag the formula to other cells it seems to be working fine. But the problem with this method is i have so many formulas that i need to copy in so many columns for different workbooks that this process is taking too long. What am i doing wrong with copy and paste option. Please advise.

Try, pressing CTRL-C to copy the cell contents, then with the next cell down selected press CTRL-SHIFT-down arrow to select the entire column below that cell and right-click and choose paste special and a dialog box will appear. Choose Formats and okay. The problem with doing this though is that your workbook will take more time to calculate, it is usually better to make a list and have the formula part of the list that you can insert rows into or add entries to the bottom.

Can you please eloborate on how to make a list and have the formula in it? I am pretty new to excel.

What happens if you press the F9 key to force a calculation. Excel will sometimes turn off the auto calculation to preserve resources.

If you put your formulas into a couple of rows, you can then go to Data->Lists->Create list. You then choose the cells you want included and it asks if your list has header rows (always a good idea). As you add to the list, you the formulas and if you used any the data validation drop down boxes, all copy down the list. If it helps you can even create a form from the Data menu to help data entry.