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.
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:
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!