Recently I had some data from a website that was poorly formatted, but I needed to get it into a spreadsheet to work with. When I copied the data into the spreadsheet, I needed to remove all of the rows that contained irrelevant information, such as repeated header fields.
Other use cases might require you to delete any rows containing someone’s name, a location, or some other information to trim the excess data from your sheet.
How to Remove all Rows Containing Certain Data
- Select all of your data, including the data you wish to remove.
- Press Ctrl F to open the Find and Replace window.
- Type the text that is contained in the row you wish to delete. For example if you need to delete rows with someone’s name, type that name in.
- Click the Find All button.
- This will show a list of all cells containing the data you searched for below the search box.
- Click on one of the results that appear below the search box, then press Ctrl A.
- All results should be highlighted now. Also, if you notice on your spreadsheet, each cell containing what you searched for will be selected.
- Click the Close button on the Find and Replace window.
- Press Ctrl – to open the Delete window.
- Select the Entire Row option, and press the OK button.
All rows containing the data you wanted to remove should be gone now!
You can also remove an entire column of data that contains certain information in a similar manner. To remove the entire column, simply select the Entire Column option in the last step above.
As with any data modification, be sure to have a copy of your data saved elsewhere as a backup just in case you accidentally remove some important information.