How to paste data in alternate blank rows in Excel?
In some cases, for a copied row, you may need to paste it in multiple blank rows alternately in a new range in Excel as below screenshot shown. How can you do to achieve it? This article will help you.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
There is no direct method to solve this problem, but you can achieve it with the little trick in this article. Please do as follows.
1. Select a blank cell (here I select cell D2) in the new range you need to paste the copied data into, then enter formula =MOD(ROW(A1),2).
2. Keep selecting cell D2, drag the Fill Handle down to the column cells. See screenshot:
3. Then select cell D1, click Data > Filter to enable the Filter function.
4. Click the drop-down arrow of cell D1, then filter the column by number 1 and finally click the OK button. See screenshot:
5. Now the alternate blank rows are filtered out. Please copy the data, select the filtered range, and then press Ctrl + V keys simultaneously to paste the data.
6. Click Data > Filter to turn off the Filter function, and then delete the helper column as you need. See screenshot:
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.
To post as a guest, your comment is unpublished.· 4 months agoshows alternate rows but when i paste, it pastes normal way, doesnt skip cells.
To post as a guest, your comment is unpublished.· 9 months agoIt doesn't work
To post as a guest, your comment is unpublished.· 9 months agoThis doesn't work for me. I have Microsoft Office 2010, so whichever version of excel comes with that I am not quite sure but either way it doesn't work. When I paste it pastes to ALL rows. So, when I paste I only see half of what I pasted, when I turn the filter off I see everything I pasted =/