How to paste the formula to the entire spreadsheet with blanks

[Sr1pr1ya]

Hi!

I have an excel sheet with 1048567 rows of data entries. I need help in pasting the formula to the entire sheet. I have entered my formula for the first row. Now I want to paste the same formula for every 6th row from the first row, in-between rows are left blank. If i manually paste the formulas for every 6th row, I would go mad. So, Please suggest me a solution to this. I attached the sample image of the spreadsheet and it explains what exactly i want.
Excel.png
Any help is very much appreciated!!

Thanks!

[Special-K]

Post a SMALL sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

[Sr1pr1ya]

I have attached the sample file for the easy reference.

[davsth]

select 6 rows as a block and then copy to the end, Shift control down arrow, may save you time getting there!

you may need to keep holding shift and go up a few rows as the range you select needs to be a multiple of 6 or it will error

[davsth]

its every 4th row!


select f1:p4

copy

press f5

enter f5:p100487 press ok

now paste

that should do it!

[Sr1pr1ya]

Hi

I tried your suggestion. F5 function is quite easier and saves time. But I am getting values in the blanks too. How to sort this out?

[kvsrinivasamurthy]

Davsth suggestion works.
Other way

select f1:p4

copy

Go to Namebox ->>

enter f5:p100487

press enter key

now paste using Home tab.

[davsth]

You are getting values if you select f1:p1 it will copy every line if you select f1:p4 it should work

[Sr1pr1ya]

Hey davsth! I made an error last time. I used Ctl-V to paste that why i got the results without blanks, but now i used Paste formula option from home tab. Now I got the result. Thank you very much.