Sometimes you need to remove rows following a repeating pattern. For example, keeping two rows, deleting the next three, and repeating that sequence throughout a worksheet. While Excel doesn’t have a built-in command for this, it’s easy to accomplish using a helper column, sorting, or filtering.

In this tutorial, you’ll learn how to create a repeating keep/delete pattern using Excel’s AutoFill feature, then use sorting or filtering to quickly remove the unwanted rows. This approach works well for large datasets and can easily be adapted to different row patterns.

Inspired by a Real Question

This tutorial was created in response to a question posted in the Microsoft Tech Community. It’s a good example of how a few simple Excel features can solve a repetitive data-cleaning task without requiring formulas or VBA.

What You’ll Learn

  • Create a repeating pattern using a helper column.
  • Use AutoFill to extend the pattern across your data.
  • Sort or filter rows based on the helper column.
  • Delete repeating groups of rows efficiently.
  • Adapt the technique for any repeating keep/remove pattern.

Practice & Resources

Want to work through the examples yourself? This tutorial includes a practice workbook you can use while following along with the video and step-by-step instructions.

Click the button below to view the resources available for this tutorial.

View Workbook

Common Uses

This technique is useful whenever you need to remove repeating groups of data, including:

  • Cleaning exported reports
  • Removing spacer rows
  • Keeping every nth record
  • Preparing data for analysis
  • Cleaning machine-generated datasets
  • Formatting imported data before processing

Requirements & Compatibility

Requirements

This tutorial uses standard Excel features:

  • AutoFill
  • Sort
  • Filter

Compatibility

These features are available in Microsoft 365, Excel 2021, Excel 2019, and many earlier desktop versions of Excel.

Video Tutorial

Breaking Down the Technique

Unlike many Excel tutorials, this solution doesn’t require a formula.

Instead, it uses three simple features working together:

Helper Column

Create a new column that identifies each row as either:

  • Keep
  • Delete

AutoFill

Enter the first repeating pattern, for example:

Keep
Keep
Delete
Delete
Delete

Then drag the fill handle downward to repeat the pattern automatically.

Sort or Filter

Once the helper column is complete, you can either:

  • Filter to show only the rows marked Delete, then delete them.

or

  • Sort by the helper column so all Delete rows are grouped together before deleting them.

Step-by-Step Instructions

  1. Insert a new helper column next to your data.
  2. Enter your desired repeating pattern (for example: Keep, Keep, Delete, Delete, Delete).
  3. Highlight the completed pattern.
  4. Drag the fill handle downward to repeat the sequence throughout your dataset.
  5. Apply a Filter or Sort using the helper column.
  6. Display or group the rows marked Delete.
  7. Delete those rows.
  8. Remove the helper column if it is no longer needed.

Additional Tips

Helpful Tip

Always make a backup copy of your worksheet before deleting large numbers of rows.

Version Compatibility

Because this technique relies only on AutoFill, sorting, and filtering, it works in virtually every modern version of Microsoft Excel.

Enjoyed this tutorial? Subscribe to my YouTube channel for more step-by-step software tutorials, tips, and practical ways to work more efficiently.