Written by Allen Wyatt (last updated April 4, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
When working with data retrieved from another source—such as another user or from an external program—you may have a need to remove certain rows from the data. It is not unusual, for instance, to have the need to remove every third row or every fifth row. The following general steps allow you to delete every X rows without using a macro.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6123) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Deleting Every X Rows without a Macro.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
Need to insert rows in your worksheet? Excel provides a few techniques you can use to do this. Here are some ideas you ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-06-22 12:36:52
Neil
I see a pattern like 1,2,3,1,2,3 CAN be auto-populated if it is formatted as text before being entered.
2020-06-22 12:18:47
Neil
nevermind, autofill won't work that way. That's pretty dumb of excel not to be able to detect a pattern like that.
2020-06-22 12:12:52
Neil
Or, you could just use one helper column and put, in this case, 1,2,3,1,2,3 and autofill the rest of the way down and then filter for the number 3 and delete those rows.
2020-06-20 14:10:52
Frank Moore
Wow. This is another great tip. I frequently have this problem when I import a CSV file. Thank you very much.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments